Re: NEED HELP!!!! Oracle equivalent for Sybase SQL...

kibwe_at_wpi.edu (KJ) wrote:

>Here's the code... I'm having problems with the testing of the subquery for nulls. Oracle needs the format 'expr operator subquery'
>and 'NULL != query' of course won't work. HELP!
 

> update TOTAL_COMP_DATABASE
> SET LOA_DATE =
> (SELECT MIN (J3.EFFDT) FROM PS_JOB J3
> WHERE J3.EMPLID = TOTAL_COMP_DATABASE.EMPLID AND J3.ACTION = 'LOA'AND
> J3.ACTION_REASON != 'PAR' AND J3.EFFDT >=
> TOTAL_COMP_DATABASE.RET_DATE)
> WHERE
> (SELECT MIN (J3.EFFDT) FROM PS_JOB J3
> WHERE J3.EMPLID = TOTAL_COMP_DATABASE.EMPLID AND J3.ACTION = 'LOA' AND
> J3.ACTION_REASON != 'PAR' AND J3.EFFDT >=
> TOTAL_COMP_DATABASE.RET_DATE) != NULL AND RET_DATE != NULL
 
> -kibwe_at_wpi.wpi.edu

update TOTAL_COMP_DATABASE

        SET LOA_DATE = 
         (SELECT MIN (J3.EFFDT) 
            FROM PS_JOB J3
           WHERE J3.EMPLID = TOTAL_COMP_DATABASE.EMPLID 
             AND J3.ACTION = 'LOA'
             AND J3.ACTION_REASON != 'PAR' 
             AND J3.EFFDT >= TOTAL_COMP_DATABASE.RET_DATE) 
WHERE
        (SELECT MIN (J3.EFFDT) 
           FROM PS_JOB J3
          WHERE J3.EMPLID = TOTAL_COMP_DATABASE.EMPLID 
            AND J3.ACTION = 'LOA' 
            AND J3.ACTION_REASON != 'PAR' 
            AND J3.EFFDT >=  TOTAL_COMP_DATABASE.RET_DATE) != NULL 

update the loa_date and set it equal to the minimum J3.effdt where .... if the J3.effdt is NOT NULL and ret_date IS NOT NULL.

A shorter way to say this will be:

update TOTAL_COMP_DATABASE

        SET LOA_DATE = 
         (SELECT nvl( MIN (J3.EFFDT), loa_date ) 
            FROM PS_JOB J3
           WHERE J3.EMPLID = TOTAL_COMP_DATABASE.EMPLID 
             AND J3.ACTION = 'LOA'
             AND J3.ACTION_REASON != 'PAR' 
             AND J3.EFFDT >= TOTAL_COMP_DATABASE.RET_DATE) 

Or you can change the != NULL is IS NOT NULL.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Dec 05 1995 - 00:00:00 CET