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...


 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 last published in October 2003

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.