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.
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.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
even can do:
select * from <table> where (SELECT ID FROM SOME TABLE WHERE ID = 1) IS NULL;
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;
/
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
end if;
end;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
show your pl/sql code.. It shouldn't.
ASKER
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?
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.
If you trying to select the column by itself then you would get an error.
ASKER
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select id from <table> where ID = 1;
exception
when no_data_found
// logic if null returned
end;