Re: SQL statement help - passing sysdate - 1

From: Saira Somani <saira_somani_at_yahoo.com>
Date: Fri, 11 Apr 2003 10:48:14 -0800
Message-ID: <F001.0057FB8F.20030411104814@fatcity.com>

Answered my own question!

Thank you all.

SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and
a.cust_num='2' and
trunc(a.order_date)=trunc(sysdate)-1

group by a.cust_num;
                    ^^^^^

CUST_NUM COUNT(B.ORD_ID)
---------- ---------------

2                      574

SQL> -----Original Message-----
Sent: April 11, 2003 9:31 AM
To: 'ORACLE-L_at_fatcity.com'

Could someone guide me as to what is wrong with the SQL statement below? I'm trying to get a total number of order lines for yesterday's date in a report.

I could run it at 11:59 PM that day and just use sysdate, but there is a possibility that a couple of orders coming in at that time would not be included in the report.

The SQL statement works when I explicitly state yesterday's date but in a PL/SQL script that will be run daily from cron and e-mailed to people, I want to be able to pass this value dynamically.

SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and a.cust_num='11' and trunc(a.order_date)='10-APR-03' group by a.cust_num;

CUST_NUM COUNT(B.ORD_ID)
---------- ---------------

11                     880

SQL> select sysdate - 1 from dual;

SYSDATE-1

---

10-APR-03 SQL> select a.cust_num, count(b.ord_id) from ord a, ord_l b where a.ord_id=b.ord_id and a.cust_num='11' and trunc(a.order_date)=(select sysdate - 1 from dual) group by a.cust_num;

no rows selected

Thanks in advance.
Saira

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Saira Somani
  INET: saira_somani_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Apr 11 2003 - 13:48:14 CDT