Q

Oracle SQL doesn't work in SQL Server

This SQL works in Oracle, but not in SQL Server. How do I structure it in SQL Server? It determines the number of unique rows (distinct) based on multiple columns.
 select count(*) FROM (select distinct hdr_invoice_number, trn_sequence, lip_charge_transaction from STG1_INVOICE_FACT)

The syntax you have supplied does work with SQL Server when you alias the sub select. For example execute the following

T-SQL:

 create table #demo1 ( col1 int, col2 int, col3 int ) insert #demo1 values(1,2,3) insert #demo1 values(1,2,3) insert #demo1 values(1,2,4) insert #demo1 values(1,2,4) insert #demo1 values(1,2,4) insert #demo1 values(5,6,7) insert #demo1 values(6,7,8) select count(*) from( select distinct col1, col2, col3 from #demo1) table1
This produces a COUNT(*) of 4 which is the number of distinct rows when selecting unqiness based on the three columns. Similarly this code:
 select count(*) from( select distinct col1, col2 from #demo1) table1
produces a count(*) of 3 as uniqueness is being selected on only two columns.

 

For More Information

This was first published in October 2003

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close