CODEWITHSUNDEEP
sqloracle-database
Aymen Kanzari
Aymen Kanzari

Reputation: 2023

sql oracle - filter query sql using case when

I have the below query

select
    ens.ID_ENS,
    ens.NOM_ENS,
    
   -- for the first SURVEILLANT
    disp.SALLE_EXAM,
    disp.NB_HEURES_ENS,

    -- for the second SURVEILLANT
    disp.SALLE_EXAM2,
    disp.NB_HEURES_ENS2

from ESP_ENSEIGNANT ens, ESP_MODULE_PANIER_CLASSE_SAISO disp
WHERE ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2) AND ens.NOM_ENS != 'A AFFECTER';

I want update the query for display SALLE_EXAM, NB_HEURES_ENS only for SURVEILLANT

select
    ens.ID_ENS,
    ens.NOM_ENS,

    CASE
        WHEN ens.ID_ENS = disp.SURVEILLANT THEN (disp.SALLE_EXAM AS "EXAM", disp.NB_HEURES_ENS AS "HOURDISP")
        WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN (disp.SALLE_EXAM2 AS "EXAM", disp.NB_HEURES_ENS2 AS "HOURDISP")
    END

from ESP_ENSEIGNANT ens, ESP_MODULE_PANIER_CLASSE_SAISO disp
WHERE ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2) AND ens.NOM_ENS != 'A AFFECTER';

Upvotes: 0

Views: 35

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You can rewrite the query with explicit JOIN syntax as

SELECT ens.ID_ENS,
       ens.NOM_ENS,      
       CASE
         WHEN ens.ID_ENS = disp.SURVEILLANT THEN
           disp.SALLE_EXAM 
         WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN
           disp.SALLE_EXAM2
       END AS "EXAM",
       CASE
         WHEN ens.ID_ENS = disp.SURVEILLANT THEN
          disp.NB_HEURES_ENS 
         WHEN ens.ID_ENS = disp.SURVEILLANT2 THEN
          disp.NB_HEURES_ENS2
       END AS "HOURDISP"
  FROM ESP_ENSEIGNANT ens
  JOIN ESP_MODULE_PANIER_CLASSE_SAISO disp
    ON ens.ID_ENS IN (disp.SURVEILLANT, disp.SURVEILLANT2)
   AND ens.NOM_ENS != 'A AFFECTER';

A side note : if disp.SURVEILLANT and disp.SURVEILLANT2 have equal values, then disp.SALLE_EXAM and disp.NB_HEURES_ENS will be picked respectively. Eg the first components have precedence.

Upvotes: 1

Related Questions