ORACLE SQL // IF ELSE?

Question

I got a small problem in my SQL code :

LEFT JOIN
           (SELECT *
              FROM pilotage_usines_valeurs
             WHERE c_indicateur IS NOT NULL) v
              ON     v.id_usine = d.id_usine
                 AND v.annee = 2015
                 AND V.MOIS = D.MOIS
                 AND V.C_INDICATEUR = pi1.c_indicateur

Sometime pi1.c_indicateur is null. How can i test it and write the line if pi1.c_indicateur is not null don't write it if pi1.c_indicateur is null ?

Answer 1

If I understand your intentions correctly I suggest you add the NOT NULL condition to the ON clause:

LEFT JOIN (SELECT *
             FROM PILOTAGE_USINES_VALEURS
             WHERE C_INDICATEUR IS NOT NULL) v
  ON v.ID_USINE = d.ID_USINE
     AND v.ANNEE = 2015
     AND v.MOIS = D.MOIS
     AND v.C_INDICATEUR = pi1.C_INDICATEUR
     AND pi1.C_INDICATEUR IS NOT NULL

Answer 2

NVL is used to substitute null value by something else. Syntax is similar to

NVL (FIELD_TO_BE_TESTED_FOR_NULL, VALUE_IF_NULL);

Option 1: Replace pi1.c_indicateur when null with some value so that V.C_INDICATEUR will never be equal to that value.

AND V.C_INDICATEUR = NVL(pi1.c_indicateur,'IMPOSSIBLE VALUE FOR V.C_INDICATEUR ');

Option 2: Replace V.C_INDICATEUR with some value if null and replace pi1.c_indicateur with some other value, so that both will never match if NULL.

AND (V.C_INDICATEUR,'ABC') = NVL(pi1.c_indicateur,'DEF');