OraFAQ Forum: SQL & PL/SQL » find all the null data value in column

Home » SQL & PL/SQL » SQL & PL/SQL » find all the null data value in column (oracle 11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

find all the null data value in column [message #617314]

Fri, 27 June 2014 05:03

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear All,

I have a table of few column where not all the column has got data some of them has got null, each row is unique , i want to find out for each unique row all the column which has null in them

table

CREATE TABLE TEST
(
  ID    NUMBER,
  COL1  VARCHAR2(100 BYTE),
  COL2  VARCHAR2(100 BYTE),
  COL3  VARCHAR2(100 BYTE),
  COL4  VARCHAR2(100 BYTE),
  COL5  VARCHAR2(100 BYTE)
)

data

SET DEFINE OFF;
Insert into TEST
   (ID, COL1, COL2, COL3, COL4, 
    COL5)
 Values
   (1, '4', NULL, '8', NULL, 
    '10');
Insert into TEST
   (ID, COL1, COL2, COL3, COL4, 
    COL5)
 Values
   (2, '3', '4', '8', '8', 
    NULL);
Insert into TEST
   (ID, COL1, COL2, COL3, COL4, 
    COL5)
 Values
   (3, '6', '8', NULL, NULL, 
    '5');
Insert into TEST
   (ID, COL1, COL2, COL3, COL4, 
    COL5)
 Values
   (4, '7', NULL, '2', '8', 
    '4');
COMMIT;

my data looks like below in table

ID	COL1	COL2	COL3	COL4	COL5	COL6
1	4	NULL	8	NULL	NULL	10
2	3	4	8	8	8	NULL
3	6	8	NULL	NULL	NULL	5
4	7	NULL	2	8	9	4

so i want to report as below , say for instance id 1

1 col2 , col4 ,col5 
3 col3 , col4 , col5

Hope i am clear with the example. please suggest me a clue

[EDITED by LF: applied [code] tags to preserve output formatting]

[Updated on: Fri, 27 June 2014 06:50] by Moderator

Report message to a moderator

Re: find all the null data value in column [message #617315 is a reply to message #617314]

Fri, 27 June 2014 05:59

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

decode(col1,null,'col1')||decode(col2,null,'col2')

Report message to a moderator

Re: find all the null data value in column [message #617317 is a reply to message #617315]

Fri, 27 June 2014 06:33

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

or you can use case, or nvl2.

Report message to a moderator

Re: find all the null data value in column [message #617320 is a reply to message #617315]

Fri, 27 June 2014 06:46

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Using DECODE as Pablolee suggested(added trim to remove the leading and trailing commas if any):

SQL> SELECT id,
  2         Trim(both ',' FROM ( Decode(col1, NULL, 'col1,')
  3                              || Decode(col2, NULL, 'col2,')
  4                              || Decode(col3, NULL, 'col3,')
  5                              || Decode(col4, NULL, 'col4,')
  6                              || Decode(col5, NULL, 'col5') )) str
  7  FROM   test;
        ID STR
---------- ------------------------
         1 col2,col4
         2 col5
         3 col3,col4
         4 col2

If you need all the values to be displayed in each column:

Using NVL2:

SQL> COLUMN col1 format a10;
SQL> COLUMN col2 format a10;
SQL> COLUMN col3 format a10;
SQL> COLUMN col4 format a10;
SQL> COLUMN col5 format a10;
SQL> SELECT id,
  2         Nvl2(col1, col1, 'col1') col1,
  3         Nvl2(col2, col2, 'col2') col2,
  4         Nvl2(col3, col3, 'col3') col3,
  5         Nvl2(col4, col4, 'col4') col4,
  6         Nvl2(col5, col5, 'col5') col5
  7  FROM   test;
        ID COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ---------- ----------
         1 4          col2       8          col4       10
         2 3          4          8          8          col5
         3 6          8          col3       col4       5
         4 7          col2       2          8          4

Using DECODE again :

SQL> SELECT id,
  2         Decode(col1, NULL, 'col1',
  3                      col1) col1,
  4         Decode(col2, NULL, 'col2',
  5                      col2) col2,
  6         Decode(col3, NULL, 'col3',
  7                      col3) col3,
  8         Decode(col4, NULL, 'col4',
  9                      col4) col4,
 10         Decode(col5, NULL, 'col5',
 11                      col5) col5
 12  FROM   test;
        ID COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ---------- ----------
         1 4          col2       8          col4       10
         2 3          4          8          8          col5
         3 6          8          col3       col4       5
         4 7          col2       2          8          4

Regards,
Lalit

Report message to a moderator

Re: find all the null data value in column [message #617327 is a reply to message #617320]

Fri, 27 June 2014 07:38

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Thanks,

every one, i tried decode before sending my doubt, i though there might be some function can can reduce the code.

Many thanks

Report message to a moderator

Previous Topic:	SQL query to use Reg_exp
Next Topic:	input argument in function use in select statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 12:39:08 CDT 2024