CODEWITHSUNDEEP
sqloracle-database
user5240377
user5240377

Reputation: 9

Oracle SQL show null value

I am trying to do the following query:

SELECT column1,column2,column3
FROM tablename
WHERE
column1 IN
('string1','string2','string3','string4')
AND column2='some_value';

The results show only: String1, string2,string3 because string4 does not have an equivalent some_value in column2.

How can I show all 4 values with the 4th showing as null like:

Column1|column2|column3
+----------------------+
|string1 value  value  |
|string2 value  value  |
|string3 value  value  |
|string4 null   null   |
+----------------------+

Upvotes: 0

Views: 141

Answers (3)

Diogo Maschio
Diogo Maschio

Reputation: 74

In this case the treatment is diferent, if value of column2 is only diferent from "some value" (parameter) return all lines. - In this case: - column2 with diferent value from parameter will be returned with empty value on column2 and column3. - column2 with null value will be returned

SELECT column1, CASE WHEN column2 = 'some value' THEN COLUMN2 ELSE NULL END column2, CASE WHEN column2 = 'some value' THEN column3 ELSE NULL END column3 FROM TABLENAME WHERE column1 IN ('string1','string2','string3','string4') AND (column2='value' OR column2 = COLUMN2 OR column2 IS null);

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Add OR 

SELECT column1,column2,column3
FROM tablename
WHERE
    column1 IN ('string1','string2','string3','string4')
AND (
        column2='some_value'
     OR column2 is null
    );

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT column1,column2,column3
FROM tablename
WHERE
column1 IN
('string1','string2','string3','string4')
AND column2='some_value'
OR (column2 is null or column3 is null);

You should saycolumn2 is null at the end. as it doesn't correspond to your where condition.

Upvotes: 1

Related Questions