Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on Microsoft SQL Server Installation

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.