CODEWITHSUNDEEP
sqloracle-databasejoinleft-join
Oh-No
Oh-No

Reputation: 127

Using select case on a left join?

I have used a left join on two of my tables. Now I want to use case to identify the records from my left table who don't have a match in the right table. Such records exist and have a null value in the 'id_zeus' column of my join, however when I execute the case, it is as these fields don't exist. Where am I going wrong ? I get "Present" in all my column Disturbance. I am using Oracle SQL developer.

SELECT 
    CASE DP.ID_PRB
    WHEN  NULL
    THEN 'Absence'
    ELSE 'Present'    END as Disturbance,
FROM 
  FIRE.WSITE WI 
  LEFT JOIN 
    (SELECT DISTINCT 
      DPL.ID_PERT as ID_PRB 
     FROM FIRE.DEPPLAN DPL 
     GROUP BY DPL.ID_PERT
    ) DPL
    ON WI.ID_PERT = DP.ID_PERT

Upvotes: 1

Views: 1367

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

What is const? You don't seem to need it. The SELECT DISTINCT and GROUP BY are redundant, so use only one of them. And your alias on the subquery is incorrect.

But your problem is the comparison to NULL. It doesn't even match when doing a comparison as you are doing in CASE. You need to use IS NULL:

SELECT (CASE WHEN DP.ID_PRB IS NULL THEN 'Absence' ELSE 'Present'
        END) as Disturbance,
FROM FIRE.WSITE WI LEFT JOIN
     (SELECT DISTINCT DPL.ID_PERT as ID_PRB
      FROM FIRE.OSI_DEVIATION_PLANS DP
     ) DP
     ON WI.ID_PERT = DP.ID_PERT;

This query would commonly be written as:

SELECT (CASE WHEN NOT EXISTS (SELECT 1
                              FROM FIRE.OSI_DEVIATION_PLANS DP
                              WHERE WI.ID_PERT = DP.ID_PERT
                             )
             THEN 'Absence' ELSE 'Present'
        END) as Disturbance,
FROM FIRE.WSITE WI ;

This offers more opportunities for optimization.

Upvotes: 2

Related Questions