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 ?

I know NVL but how to use it here ? In general i use it on a field like NVL(pi1.c_indicateur,0) — Damien Christophe
– Damien Christophe, Commented May 15, 2015 at 10:15

halfer · Accepted Answer · 2019-06-29 10:08:42Z

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

edited Jun 29, 2019 at 10:08

halfer

20.2k20 gold badges111 silver badges208 bronze badges

answered May 15, 2015 at 11:09

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Damien Christophe Over a year ago

I had tought of that but my problem wasn't there, it was a bit under : WHERE d.id_simu = '1168' AND V.valeur IS NOT NULL GROUP BY id_simu, id_element Added the line : OR (pi1.c_indicateur IS NULL AND d.id_simu = '1168')) Thanks for your help.

ngrashia · Accepted Answer · 2015-05-15 10:15:58Z

0

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');

answered May 15, 2015 at 10:15

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

ORACLE SQL // IF ELSE?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related