Link to home
Start Free TrialLog in
Avatar of mbroad02
mbroad02Flag for United States of America

asked on

How to SELECT null or blank columns in SQL

I am creating an extract in SQL of some Oracle database columns.  However, when some of the columns are blank, they do not SELECT.  I would like to know how to SELECT a given column and insure that it will SELECT it even if it is blank or null.
Thank you
Avatar of pollock_d
pollock_d

try something like...

SELECT NVL(column1,''),  NVL(column2,'')....etc

this will replace the NULL in the column with an empty string...\

it might work...havent tried it..
Avatar of mbroad02

ASKER

i will try this and let you know--thanks
hi, can you post your query? It might be filtered out in your WHERE condition. Try to add this

WHERE <your conditions> OR (ColumnWithNull IS NULL)
Avatar of Naveen Kumar
if you have columns with null values and you want to select them
them you have to do as shown below :

select col1, col2
from table1
where col3 is null; -- this will get all records from table1 where col3 is null.

select col1, col2
from table1
where col3 is not null; -- this will get all records from table1 where col3 is not null.

if you want to treat a null value as a different value in the queries, then we have to use
something like as shown below :

select col1, col2
from table1
where nvl(col1,9999) >= 100 ; -- i assume col1 is of number datatype

select col1, col2
from table1
where nvl(col2,'Other Dept') in ('Marketing','Other Dept') ; -- i assume col2 is of varchar2 datatype

We can even use decode, nvl2, coalesce.. to handle null values.

Thanks
Thank you for your many replies.  I am posting my query.  Notice that it is not a standard query--this one creates a fixed length text file.  
I am interested in pulling all columns in the query, whether null or not.  I do not know what columns may be null or not, so I need to check for that, and pull values if they are there, or space fill if it is null.
Please let me know how to accomplish this.

Thanks,
mark
spool  c:\extract.txt
 
SELECT RPAD(SUBSTR('D',1,1),1)
         ||     RPAD(SUBSTR(IDNT.SSN,1,9),9)
         ||     RPAD(SUBSTR('         ',1,9),9)
         ||     RPAD(SUBSTR(IDNT.LAST_NAME,1,30),30)
         ||     RPAD(SUBSTR(ADDR.ADDR_LINE1,1,30),30)
         ||     RPAD(SUBSTR(ADDR.ADDR_LINE2,1,30),30)
         ||     RPAD(SUBSTR(ADDR.CITY,1,20),20)
         ||     RPAD(SUBSTR(ADDR.ST_CD,1,2),2)
         ||     RPAD(SUBSTR(ADDR.ZIP_CD,1,9),9)
         ||     RPAD(SUBSTR('386004868',1,9),9)
         ||     RPAD(SUBSTR(' ',1,146),146)
         ||     RPAD(SUBSTR(CASE.DSCR,1,16),16)
         ||     RPAD(SUBSTR('C',1,1),1)
         ||     RPAD(SUBSTR('16  ',1,4),4)
         ||     RPAD(SUBSTR('48043',1,9),9)
         ||     RPAD(SUBSTR('0',1,1),1)
         ||     RPAD(SUBSTR('ARPTY.AMT_OUTSTANDING',1,10),10)
         ||     RPAD(SUBSTR('20080307',1,8),8)
         ||     RPAD(SUBSTR(' ',1,35),35)
 
 
 
FROM   "CRTVDBA"."ADDR" "ADDR", "CRTVDBA"."PTYADDR" "PTYADDR",
  "CRTVDBA"."IDNT" "IDNT", "CRTVDBA"."PTY" "PTY",
  "CRTVDBA"."ARPTY" "ARPTY", "CRTVDBA"."DKT" "DKT",
  "CRTVDBA"."CASE" "CASE"
 
 WHERE  ("ADDR"."ADDR_ID"="PTYADDR"."ADDR_ID" (+))
 AND ("IDNT"."IDNT_ID"="PTY"."IDNT_ID") AND
 ("PTYADDR"."CASE_ID"="PTY"."CASE_ID" (+)) AND
  ("PTY"."CASE_ID"="ARPTY"."CASE_ID" (+)) AND
  ("ARPTY"."CASE_ID"="DKT"."CASE_ID" (+)) AND
  ("DKT"."CASE_ID"="CASE"."CASE_ID" (+)) AND
  "DKT"."DKT_CD"='GICC' AND
  ("DKT"."DT">=TO_DATE ('01-09-2007 00:00:00',
  'DD-MM-YYYY HH24:MI:SS') AND
  "DKT"."DT"<TO_DATE ('02-03-2008 00:00:00',
  'DD-MM-YYYY HH24:MI:SS'));
 
	Spool off

Open in new window

Increased point value---really need a solution--thanks
ASKER CERTIFIED SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am testing and will let you know shortly--thank you!
This appears to have worked!! thanks very much.
mark
Thank you!!
Glad to help :-)
dude, I suggested the NVL(column1,''),  NVL(column2,'')....etc .. ages ago, geez, you coulda had it working sooner...!