Solved

SELECT STATEMENT IN IF CLAUSE?  (pl\sql)

Posted on 2006-11-28
8
6,509 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

696 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