Re: Need Help with PL/SQL
Date: 1996/09/27
Message-ID: <324C2FBC.4B2F_at_cmprime.cis.att.com>#1/1
Ah, the pain (and horror!) of non-blank padded comparisons. Here is my 2cents on what's happening:
The 'DAY' format returns the day-of-the-week, e.g. 'MONDAY', 'TUESDAY', etc., right-padded with blanks to a length of 9 characters. This means that your query compares 'SATURDAY ' to 'SATURDAY' and 'SUNDAY ' to 'SUNDAY'; hence, the subquery will not return any rows.
You can modify your subquery as follows:
1). .....WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')='SATURDAY ' OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')= 'SUNDAY ', or
2) Use LIKE 'SATURDAY%' OR LIKE 'SUNDAY%', or
3) Use the 'D' format which returns the day-of-the-week as a number
(1-7):
.....WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'D')='6'
OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'D')='7'
Note: For the last one, you may need to use TO_NUMBER instead of
TO_CHAR.
BTW, I think you could re-write the whole query without using a
subquery:
SELECT EO_SHIPPED.SHIP_DATE, EO_SHIPPED.ORDERNUM
FROM EO_SHIPPED
WHERE (EO_SHIPPED.SHIP_DATE BETWEEN '14-SEP-96' AND '15-SEP-96')
AND (TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY') IN ('SATURDAY ','SUNDAY '));,
or
SELECT EO_SHIPPED.SHIP_DATE, EO_SHIPPED.ORDERNUM
FROM EO_SHIPPED
WHERE (EO_SHIPPED.SHIP_DATE BETWEEN '14-SEP-96' AND '15-SEP-96')
AND (TO_CHAR(EO_SHIPPED.SHIP_DATE,'D') IN ('6','7'));
I hope this helps.
Michael Serbanescu
Sr. ORACLE DBA, TRECOM Business Systems, Edison, NJ
ms_at_cmprime.cis.att.com
atyagi_at_mc.xerox.com wrote:
>
> Hello Everybody,
>
> I have a table containing a field ship_date. I wish to
> select the shipment dates excluding weekends. I only want
> data for weekdays. Here is the query I have written.
>
> SELECT EO_SHIPPED.SHIP_DATE, EO_SHIPPED.ORDERNUM
> FROM EO_SHIPPED
> WHERE ((EO_SHIPPED.SHIP_DATE BETWEEN '14-SEP-96' AND '15-SEP-96')
> AND
> EO_SHIPPED.SHIP_DATE NOT IN
> (SELECT EO_SHIPPED.SHIP_DATE
> FROM EO_SHIPPED
> WHERE TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')='SATURDAY'
> OR TO_CHAR(EO_SHIPPED.SHIP_DATE,'DAY')= 'SUNDAY'
> ));
>
> I tried running subquery seprately but it seems that it is
> not liking my comparision with Sat. & Sun and doesn't return
> me anything.
>
> I will appreciate any help.
>
> Thanks all.
>
> You can mail me at atyagi_at_mc.xerox.com
> or
> post your comments in newsgroup.
>
> Amit Tyagi
Received on Fri Sep 27 1996 - 00:00:00 CEST