Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: sql error

REPOST: Re: sql error

From: Mike Liu <mike2322_at_hotmail.com>
Date: 25 Jan 2002 11:11:55 -0800
Message-ID: <6$--$%%%%--$-$$_%$@news.noc.cabal.int>


"Gary Turner" <gary.turner_at_sanmina-sci.com> wrote in message news:<iNE38.23656$h31.2260953_at_e420r-atl1.usenetserver.com>...
> does anyone see what is wrong with this sql statement?? i keep getting a
> missing expression error and i can't seem to find anything that seems wrong.
>
> UPDATE SFPO SET
> RELEASE_DT = CASE WHEN SUBSTR(RELEASE_DT_WRK,1,1) > '4'
> THEN DATE('19' CONCAT SUBSTR(RELEASE_DT_WRK,1,2)
> CONCAT '-' CONCAT SUBSTR(RELEASE_DT_WRK,4,2)
> CONCAT '-' CONCAT SUBSTR(RELEASE_DT_WRK,7,2)
> ELSE
> DATE('20' CONCAT SUBSTR(RELEASE_DT_WRK,1,2)
> CONCAT '-' CONCAT SUBSTR(RELEASE_DT_WRK,4,2)
> CONCAT '-' CONCAT SUBSTR(RELEASE_DT_WRK,7,2))
> END;
>
> we have this same sql running daily on DB2, but there shouldn't be any
> difference.

Replace 'DATE' with 'TO_DATE' with a format mask and replace CONCAT with '||' because 'DATE' is not a function in Oracle and CONCAT is not an operator. Use this instead,

UPDATE SFPO SET
  RELEASE_DT = CASE WHEN SUBSTR(RELEASE_DT_WRK,1,1) > '4'     THEN TO_DATE('19' || SUBSTR(RELEASE_DT_WRK,1,2)

        || '-' || SUBSTR(RELEASE_DT_WRK,4,2)
        || '-' || SUBSTR(RELEASE_DT_WRK,7,2), 'YYYY-MM-DD')
  ELSE
    TO_DATE('20' || SUBSTR(RELEASE_DT_WRK,1,2)    || '-' || SUBSTR(RELEASE_DT_WRK,4,2)
    || '-' || SUBSTR(RELEASE_DT_WRK,7,2), 'YYYY-MM-DD')   END; hth,

Mike

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 13:11:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US