?
Solved

SELECT STATEMENT IN IF CLAUSE?  (pl\sql)

Posted on 2006-11-28
8
Medium Priority
?
6,513 Views
Last Modified: 2011-08-18
hello experts,

is it posible to have a select statement in an if clause. I am writing a function or stored procedure which will search for the account no given and return true\false depending on the result of select statement.

thanks
0
Comment
Question by:sunny012097
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18033581
Hi kwieckii,

No, u can have a select in an if. Below is a sample procedure on how i do it.

CREATE OR REPLACE PROCEDURE Sp_Test(av_acctno    VARCHAR2)
IS
   tmp  VARCHAR2(1):= 'N';
BEGIN
   SELECT 'Y' INTO tmp
   FROM abc
   WHERE account_id = av_acctno;
     
   IF tmp = 'Y' THEN
     ...
   ELSE
     ...
   END IF;

   COMMIT;
END;

Cheers
Nickson
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18033586
sunny,

Sorry sunny for addressing u with the wrong name.

Nickson
0
 
LVL 27

Expert Comment

by:sujith80
ID: 18034753
"is it posible to have a select statement in an if clause"
Very much possible.
Follow this example

create or replace function test_func(p_arg number)
return boolean
is
 l_no number := 0;
begin
 if p_arg = 1 then
  select count(*) into l_no
  from a1;
 else
  select count(*) into l_no
  from a2;
 end if;
if l_no > 0 then
 return true;
else
 return false;
end if ;

end;
/

declare
 l_res boolean;
begin
 l_res := test_func(2);
 if l_res = true then
  dbms_output.put_line('true');
 else
  dbms_output.put_line('false');
 end if;
end;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:GGuzdziol
ID: 18034837
You can do it also without using variables; let's say Your query is <query>. You want to do something only if it returns at least one row:

...
  for x in (select * from (<query> ) where rownum = 1) loop
    ...
  end loop;
...
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 18037125
you can even directly call the function in the if/elsif conditions :

begin
 if ( my_func(arguments here....) )  then
  dbms_output.put_line('TRUE');
 else
  dbms_output.put_line('FALSE');
 end if;
end;
/

make sure your function returns true or false for all possible type of returns from the function.

Thanks
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 18037796
NicksonKoh,
Sorry but your solution will never hit the 'else' clause because it will receive a NO_DATA_FOUND exception.

sunny,
Can I assume you want the boolean TRUE and FALSE returned?  If so, I don't believe you can do this.  Oracle has a BOOLEAN data type but I think it can only be used inside PL/SQL and can't be returned as a datatype to some calling program.

I've provided a function that returns a Y or N and also a single select statement that will do the same thing.  The drawback to the select statement is that you have to hit the table twice.

testcase:
----------------------
drop table tab1;
create table tab1 (
account number
)
/

insert into tab1 values(1);
commit;


--Do in a single select statement
-- cons against:  have to select it twice
undefine account

select 'Y' from tab1 where account=&&account
union
select 'N' from dual where not exists (select 1 from tab1 where account=&&account)
/

--function based
CREATE OR REPLACE FUNCTION Sp_Test(av_acctno    number) return char
IS
      tmp char(1);
BEGIN
   SELECT 'Y' INTO tmp
   FROM tab1
   WHERE account = av_acctno;
     
   return 'Y';

   exception
         when NO_DATA_FOUND then
               return 'N';

END;
/

show errors


select SP_TEST(1) from dual;
select SP_TEST(2) from dual;

0
 
LVL 32

Expert Comment

by:awking00
ID: 18037904
The following assumes that account_no is of varchar2 datatype. If it is something else, modify to accommodate the proper datatype.

create or replace function acct_no_exists(acct_no_in varchar2) return boolean is
v_exists  pls_integer;
begin
select count(*)
into v_exists
from yourtable
where account_no = acct_no_in;
if v_exists > 0 then
return 'TRUE'
else
return 'FALSE'
end if;
end;
/
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18042023
Hey man, this sux man... what a silly mistake by me :(
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question