Skip to Content
0
Former Member
Aug 22, 2011 at 10:06 PM

Using distinct in a command

12 Views

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?