Using distinct in a command

[Former Member]

I have a query that works fine in SQL DEveloper:

select distinct s.invoice_number as "Invoice Number",c1.company_cd as Counterparty, C2.COMPANY_cd as "Internal Company", s.payment_due_dt as "Payment Due",

s.invoice_amt as "Amount", s.paid_amt as "Paid Amt", r.credit_limit as "Credit Limit"

from stl_invoice_hdr S, ref_company C1, ref_company C2, ref_credit_limit_definition r, stl_invoice_detail d, stl_item_hdr e

where s.invoice_status_ind=1 and (s.invoice_amt>0 and s.paid_amt < s.invoice_amt) and S.counterpart_company_num=c1.company_num

and S.internal_company_num=C2.COMPANY_NUM and s.internal_company_num=2418 and s.invoice_dt >'30-Jun-2011' and c1.company_num=r.company_num(+)

and d.invoice_hdr_num=s.invoice_hdr_num and e.item_hdr_num=d.item_hdr_num and e.mot_num=586

order by c1.company_cd;

When I convert it for a Crystall XI report, it became:

select distinct s.invoice_number as "Invoice Number",c1.company_cd as Counterparty, C2.COMPANY_cd as "Internal Company", s.payment_due_dt as "Payment Due",

s.invoice_amt as "Amount", s.paid_amt as "Paid Amt", r.credit_limit as "Credit Limit"

from stl_invoice_hdr S, ref_company C1, ref_company C2, ref_credit_limit_definition r, stl_invoice_detail d, stl_item_hdr e

where s.invoice_status_ind=1 and (s.invoice_amt>0 and s.paid_amt < s.invoice_amt) and S.counterpart_company_num=c1.company_num

and S.internal_company_num=C2.COMPANY_NUM and s.internal_company_num={?InternalCompany} and s.invoice_dt >'20110630' and c1.company_num=r.company_num(+)

and d.invoice_hdr_num=s.invoice_hdr_num and e.item_hdr_num=d.item_hdr_num and e.mot_num=586

order by s.counterpart_company_num

I replaced the internal_company_num with a parameter and changed the date format to make it work. When I added the distinct it gets error: not a SELECTed expression. If I don't use the distinct,I get a cartesian set.

IS there a trick for using a disctinct in a command?

[Former Member · Accepted solution]

ignore this -- I see the problem. Sorry to bother you.

[Former Member · Accepted solution]

I am having trouble thinking today. The problem still exists. The command now reads:

select distinct s.invoice_number as "Invoice Number",c1.company_cd as Counterparty, C2.COMPANY_cd as "Internal Company", s.payment_due_dt as "Payment Due",

s.invoice_amt as "Amount", s.paid_amt as "Paid Amt", r.credit_limit as "Credit Limit"

from stl_invoice_hdr S, ref_company C1, ref_company C2, ref_credit_limit_definition r, stl_invoice_detail d, stl_item_hdr e

where s.invoice_status_ind=1 and (s.invoice_amt>0 and s.paid_amt < s.invoice_amt) and S.counterpart_company_num=c1.company_num

and S.internal_company_num=C2.COMPANY_NUM and s.internal_company_num={?InternalCompany} and s.invoice_dt >'20110630' and c1.company_num=r.company_num(+)

and d.invoice_hdr_num=s.invoice_hdr_num and e.item_hdr_num=d.item_hdr_num and e.mot_num=586

order by s.counterpart_company_num

and it is still getting the "not a SELECTed expression.

Any help appreciated.

[Former Member · Accepted solution]

Its because

order by s.counterpart_company_num

This is not part of the select statement so Crystal can not order it, add this to select and all will be OK.

SQL developer allows you to sort by non selected fields but Crystal will not.

Ian

[Former Member · Accepted solution]

That worked - than you. But I don't understand why. I had the order by clause in there when Crystal was giving me the Cartesian set. IT didn't care then about the field not being selected. It only failed when I added the distinct.

Much thanks!!!!