Link to home
Start Free TrialLog in
Avatar of mmarksbury
mmarksbury

asked on

Check if Select Statement returns null

In T-SQL (MS-SQL), I can easily do a test to see if a select statement returns null.

Example:
IF (SELECT ID FROM SOME TABLE WHERE ID = 1)

I want to know the simplest way I can do this in PL/SQL.  I don't mind creating a variable and storing the value in the variable, but I would like for this to happen without the need to catch any exceptions.
Avatar of sathyagiri
sathyagiri
Flag of United States of America image

begin
select id from <table> where ID = 1;
exception
when no_data_found
// logic if null returned
end;

Avatar of Raynard7
Raynard7

Depending on what you want to use it for you could do,

case when (SELECT ID FROM SOME TABLE WHERE ID = 1)  is null then x else y end

Which will give you x when it is null and y when it is not

or

nvl((SELECT ID FROM SOME TABLE WHERE ID = 1), "IS NULL")

which will give you the id where it is not null else will return "IS NULL"

What do you want it for?
ASKER CERTIFIED SOLUTION
Avatar of sathyagiri
sathyagiri
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
Avatar of mmarksbury

ASKER

I simply want to check if a select statement returns null.  If it does I want to do one thing.  If it doesn't I want to do another.  I can't get your example to work.
Avatar of Acton Wang
even can do:

      select * from <table> where (SELECT ID FROM SOME TABLE WHERE ID = 1) IS NULL;
Try my second post should work for you
you would use this :

declare
_count number;
begin
select count(*) into _count from (select * from <your table> where rownum=1);  
if _count = 0 then
DBMS_OUTPUT.PUT_LINE('no record');
else
DBMS_OUTPUT.PUT_LINE('some record');
end if;
end;
/
SOLUTION
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
Many of your suggestions work, but will throw a "NO DATA FOUND" exception if no data is present.
declare
cnt number;
begin
select count(1) into cnt from table where id = 1;
if (cnt > 0 ) then
//logic here
else // No records found
//logic
end if

will not throw no data exception.

See below example

create table test (sno number);

 declare
 cnt number;
 begin
 select count(1) into cnt from test;
 dbms_output.put_line(cnt);
 end;

SQL> /
0

PL/SQL procedure successfully completed.

>> but will throw a "NO DATA FOUND" exception if no data is present
     show your pl/sql code.. It shouldn't.
If you perform a "SELECT INTO" and the WHERE condition is such that nothing is matched, the statement will always throw a "NO DATA FOUND" exception.  I thought it to be rediculous as well, but it IS the case.

Any other ways of checking for the existence of values in a table that anyone can think of?
Are you trying to select count or are you tring to select a column from table?

If you trying to select the column by itself then you would get an error.

Please read the question.  This has nothing to do with selecting a count or a column.
Two cases here are

1) The query returns no rows
     In PL/SQL use WHEN NO_DATA_FOUND exception

2) The query returns a row, but the field is NULL
     Use nvl function to convert the null to a particular value
     or use in the where clause  field1 is null  or field1 is not null
To achieve same result as sql server (only ,,if'' - without ,,else''):

FOR x IN (SELECT ID FROM SOME TABLE WHERE ID = 1 AND ROWNUM = 1) LOOP -- Your statement with AND ROWNUM = 1 added
  ...
END LOOP;

It won't raise any exception if no rows found.
If You need also ,,else'' You probably need variable (i.e. ifExists) that's initially set to 0 and set it to 1 in loop.
SOLUTION
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