Solved

SELECT STATEMENT IN IF CLAUSE?  (pl\sql)

Posted on 2006-11-28
8
6,491 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Performance issue with case statement in oracle 11G 7 80
SQL2016 to ORACLE11G linked-server 6 28
add more rows to hierarchy 3 26
Migration from sql server to oracle 5 27
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

821 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