Home » SQL & PL/SQL » SQL & PL/SQL » Check if a column is NULL
Check if a column is NULL [message #285912] Thu, 06 December 2007 01:28 Go to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Hello. I would like to know how I can fill all my cells for a column with one string if it´s empty and another one if it´s not empty. I dont have a clue how to do this but I tried this:
UPDATE colName SET 
IF rowName=!NULL THEN 
	rowName := 'filled';
END IF;

IF rowName=Null THEN
	rowName := 'empty';
END IF;

One of the errors this gives me is:
Quote:

SP2-0850: Command "END IF" is not available in iSQL*Plus

Any hints on what I should use to make something like this to work?

[MERGED by LF. Please, do not open a new topic for the same problem]

[Updated on: Thu, 06 December 2007 07:17] by Moderator

Report message to a moderator

Re: Insert one or another value [message #285914 is a reply to message #285912] Thu, 06 December 2007 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECODE or CASE

Regards
Michel
Re: Insert one or another value [message #285915 is a reply to message #285912] Thu, 06 December 2007 01:33 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Thanks a lot, will check it out Very Happy
Re: Insert one or another value [message #285965 is a reply to message #285914] Thu, 06 December 2007 03:44 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather use the NVL2 function:
UPDATE some_table SET colname = NVL2(rowname, 'filled', 'empty');
Check if a column is null [message #286050 is a reply to message #285912] Thu, 06 December 2007 06:32 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Hello. Anyone know how to get a boolean if a cell is empty or not in a specific comlumn?
Have tried(no booleans in oracle..):
IF (SELECT colname FROM tableName) IS NOT NULL 1=1 ELSE 1=2 END IF)

and
NOT EXISTS(SELECT colname FROM tableName)


But those dose not seems to give me what I want. Any hints on what to use?
Re: Check if a column is null [message #286059 is a reply to message #286050] Thu, 06 December 2007 06:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

no booleans in oracle..


BOOLEANS are there is PL/Sql not in SQL .
in SQL you can ues 1 or 0 . How does it make a difeerence if it is 1 or True ?

Show us how did you use Exists

Thumbs Up
Rajuvan
Re: Check if a column is null [message #286064 is a reply to message #286050] Thu, 06 December 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Precise where you tried to get your null or not null value.
What is a cell?

Regards
Michel
Re: Check if a column is null [message #286065 is a reply to message #286050] Thu, 06 December 2007 07:06 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Yea, feels like I´m missing something with EXISTS. This is how I use it:

table: tableName
Col1 |  Col2
1    |  a   
2    |   


SELECT Col1, Col2,  
CASE  
  WHEN (NOT EXISTS(SELECT Col2 FROM tableName))
  THEN 'Empty'
  ELSE 'Not Empty'
END AS "Col3"
FROM tableName;


This give me the result:
tableName:
Col1 |  Col2 | Col3
1    |  a    | Not Empty
2    |       | Not Empty


As you can see I would like one of them to be empty in Col3. Any idea whats wrong?
Re: Check if a column is null [message #286070 is a reply to message #286065] Thu, 06 December 2007 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select nvl2(col2,'Not empty','Empty') from table

Regards
Michel
Re: Check if a column is NULL [message #286076 is a reply to message #285912] Thu, 06 December 2007 07:17 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Works great! Thanks a lot Smile
Re: Check if a column is NULL [message #286084 is a reply to message #286076] Thu, 06 December 2007 07:25 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sure it does; I just wonder what did you do for the past 4 hours. This answer has been given to you in your previous topic about the same problem (which has now been merged with this one; see message #285965).

[Updated on: Thu, 06 December 2007 07:26]

Report message to a moderator

Re: Check if a column is NULL [message #286088 is a reply to message #285912] Thu, 06 December 2007 07:41 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Quote:

Sure it does; I just wonder what did you do for the past 4 hours. This answer has been given to you in your previous topic about the same problem (which has now been merged with this one; see message #285965).

Ah, Sorry. Could not find it. Will seartch next time. Will not happen again.

Just realised it dosent work very well for me after all. Need to use it into the "CASE". Maybe possible to compare values somehow?
Tried this:

SELECT col1,col2,
CASE  
  WHEN ('Empty' = (select nvl2(col2,'Not empty','Empty') from tableName)
  THEN 'Empty'
  ELSE 'Not Empty'
END AS "col3"
FROM tableName;

Quote:

ORA-01427: single-row subquery returns more than one row

The reason I need it in this form is that I got some "AND" into the "CASE".

[Updated on: Thu, 06 December 2007 07:41]

Report message to a moderator

Re: Check if a column is NULL [message #286092 is a reply to message #286088] Thu, 06 December 2007 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT col1,col2,
       nvl2(col2,'Not empty','Empty') AS "col3"
FROM tableName;

Just keep it simple.

Regards
Michel
Re: Check if a column is NULL [message #286113 is a reply to message #285912] Thu, 06 December 2007 08:38 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
But lets extend the problem then. Lets say I have this:

Col1 | Col2
a    |  
     | b
a    | b


And would like to check if both Col1 and Col2 got values or not. Then I cant see any way to use "nvl2" as long as it´s not possible to compare the values put into "nv12". Any tips on how I should atak this kind of problem?
Maybe you can see why I try to compare the the value into the "CASE" now.
Re: Check if a column is NULL [message #286114 is a reply to message #286113] Thu, 06 December 2007 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many ways to do it.
CASE is the simplest one to read and write.
Just try it.

Regards
Michel
Re: Check if a column is NULL [message #286117 is a reply to message #286113] Thu, 06 December 2007 08:48 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Your requirement for
Quote:

And would like to check if both Col1 and Col2 got values or not...snip
Is notall that clear as to the required results but if you want to indicate that 2 columns in a row are null:

with nlss  as (select 'a' x, 'b' y from dual
                      union all
                      select null, null from dual
                      union all
                      select null, 'm' from dual
                      union all
                      select 'f', null from dual)
select nvl2(x||y,'not empty','empty')
from nlss


It might be a better idea if you stop trying to 'dumb it down' and tell us what you actually need.
Re: Check if a column is NULL [message #286144 is a reply to message #285912] Thu, 06 December 2007 10:32 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Sorry if I have been unclear but normaly it´s best for everyone to cut down the problem. But when there is a special function like "nvl2" it can lead to missunderstanding.
Quote:

Is notall that clear as to the required results but if you want to indicate that 2 columns in a row are null:

A lot harder then I expected. But I have tried really hard to understand it but its still not very clear yet. But I have at least tried my best and this gets me closest to the result I want but still not perfect:

CREATE TABLE tableName(
col1 VARCHAR(10),
col2 VARCHAR(10));

INSERT INTO tableName VALUES(
'a','');

INSERT INTO tableName VALUES(
'','b');

INSERT INTO tableName VALUES(
'a','b');

WITH aName AS (SELECT 'a' col1, 'b' col2 FROM tableName
               UNION 
	       SELECT 'a', 'b' FROM tableName)
SELECT t.col1,t.col2,nvl2(n.col1||n.col2,'not empty','empty') AS "status"
FROM aName n,tableName t;

This will give out the table:
COL1 	COL2 	status
a 	  	not empty
  	b 	not empty
a 	b 	not empty

Would like it to look like this but show the two first rows in the "status" column to be empty.
Re: Check if a column is NULL [message #286151 is a reply to message #286144] Thu, 06 December 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use CASE it is easy... if only you try it.

Regards
Michel
Re: Check if a column is NULL [message #286159 is a reply to message #285912] Thu, 06 December 2007 11:09 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
You make it sound simple Wink
But then it´s the same problem as before. Is it even possible to compare values? If not I cant see how it should be possible to do this in a "CASE". The table look:

Col1 | Col2
a    |  
     | b
a    | b


Code who gives error when I try to compare 'Empty' = ....
SELECT col1,col2,
CASE  
  WHEN ('Empty' = (select nvl2(col1,'Not empty','Empty' from tableName)
  AND 'Empty' = (select nvl2(col2,'Not empty','Empty') from tableName))
  THEN 'Empty'
  ELSE 'Not Empty'
END AS "col3"
FROM tableName;

And wished result is:
COL1 	COL2 	status
a 	  	empty
  	b 	empty
a 	b 	not empty

Note: I have read throw how "CASE" works once again and it was nothing who could help me there.
Re: Check if a column is NULL [message #286160 is a reply to message #286159] Thu, 06 December 2007 11:14 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Why check for both columns to be empty? Are you not looking for one column OR the other to be empty?

And as Michel says, keep it simple...get rid of the subqueries
Re: Check if a column is NULL [message #286168 is a reply to message #286160] Thu, 06 December 2007 11:37 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
cmerry wrote on Thu, 06 December 2007 11:14

Why check for both columns to be empty? Are you not looking for one column OR the other to be empty?

You are correct, my misstake, should be OR and not AND, but it dose not get me closer to the solution.
Michel

SELECT col1,col2,
       nvl2(col2,'Not empty','Empty') AS "col3"
FROM tableName;


Just keep it simple.

Sorry but I cant see how this would look into a "CASE" and not be a subquerie.

EDIT: after lots more of tries to remove subqueire I finaly succesful manage to remove it. Thanks a lot for your help!

[Updated on: Thu, 06 December 2007 11:55]

Report message to a moderator

Re: Check if a column is NULL [message #286171 is a reply to message #286168] Thu, 06 December 2007 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why don't you post the final solution?

Regards
Michel
Re: Check if a column is NULL [message #286176 is a reply to message #285912] Thu, 06 December 2007 12:50 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Sorry, but it felt like everyone without me know how it was supposed to look like. Here is the final result:

SELECT t.col1,t.col2,
CASE  
  WHEN ('Empty' = nvl2(t.col1,'Not empty','Empty')
  OR ('Empty' = nvl2(t.col2,'Not empty','Empty')))
  THEN 'Empty'
  ELSE 'Not Empty'
END AS "col3"
FROM tableName t;


Thanks once again! Very Happy
Re: Check if a column is NULL [message #286179 is a reply to message #286176] Thu, 06 December 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep it simple, most of the time exactly the way you say it:
SELECT t.col1,t.col2,
CASE  
  WHEN t.col1 IS NULL OR t.col2 IS NULL THEN 'Empty'
  ELSE 'Not Empty'
END AS "col3"
FROM tableName t;

Regards
Michel

[Updated on: Thu, 06 December 2007 13:05]

Report message to a moderator

Re: Check if a column is NULL [message #286180 is a reply to message #285912] Thu, 06 December 2007 13:15 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Nice, I belive I take that one instead. Short and clear solution. Thanks m8 Wink
Re: Check if a column is NULL [message #286512 is a reply to message #285912] Fri, 07 December 2007 12:59 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just another example of a way to do what you want

select t.col1,t.col2,nvl2(t.col1,'Empty',nvl2(t.col2,'Empty','Not Empty')) col3
FROM tableName t;
Re: Check if a column is NULL [message #286542 is a reply to message #285912] Sat, 08 December 2007 00:48 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

A small correction Bill B ,

select t.col1,t.col2,nvl2(t.col1,'Not Empty',nvl2(t.col2,'Not Empty','Empty')) col3
FROM tableName t;


Thumbs Up
Rajuvan
Previous Topic: Problem with Oracle Directories
Next Topic: data insert problem
Goto Forum:
  


Current Time: Wed Dec 07 02:45:48 CST 2016

Total time taken to generate the page: 0.09107 seconds