Ask the Expert

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)

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: