Skip to Content
author's profile photo Former Member
Former Member

SQL not processing date clause in where part of the statement

I have a query:

select c1.company_cd as "Counterparty", c2.company_cd as "Internal Company", s.payment_due_dt as "PaymentDue", s.invoice_number as "Invoice Number", s.invoice_dt as "Invoice Date", s.invoice_amt as "Invoice Amount", s.paid_amt as "Paid Amt"

from stl_invoice_hdr s, ref_company c1, ref_company c2

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.payment_due_dt > '30-Jun-2011'

order by s.counterpart_company_num

This works fine in SQL Developer but when I run it in Crystal Reports XI, it brings back results for invoices in 2009 and 2010. I switched invoice_dt to payment_due_dt as you see above but, as I expected it made no difference. IS this an Oracle 10g versus CR thing or is it just a dumb mistake I am making. Please help.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Aug 20, 2011 at 12:20 AM

    Hi,

    The query looks fine. Are you using this query as a command object in CR?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Your question about being in a command got me to thinking. I cannont use the semi-colon in a command, maybe command doesn't like my date format. So I played around with the date format. AFter battling Oracle and its pickiness with date fomats in SQL I got the following to work:

      select c1.company_cd as "Counterparty", c2.company_cd as "Internal Company", s.payment_due_dt as "PaymentDue", s.invoice_number as "Invoice Number", s.invoice_dt as "Invoice Date", s.invoice_amt as "Invoice 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

      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.payment_due_dt > '20110630' and c1.company_num=r.company_num(+)

      order by s.counterpart_company_num

      Thanks for pushing me in the right direction!

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.