Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> REPOST: Re: sql error
"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
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