Solved

SELECT STATEMENT IN IF CLAUSE?  (pl\sql)

Posted on 2006-11-28
8
6,499 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
Independent Software Vendors: 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 250 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 92
Fill Null values 5 54
Fill Date time Field 12 45
how to tune the query 17 81
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

734 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