another - how can i do this sql in oracle - question 1

[FALCONSEYE]

t-sql:

if exists( 
select quantity, id
from myTable
where id = 55
)

begin
  print 'yes'
end
else 
begin
   .. run another select statement to look another table
end

I have to the same thing in Oracle. Currently I have:

declare 
tempquantity number;
tempid number;

begin
select quantity, id
into tempquantity, tempid 
from myTable
where id = 55

if tempquantity > 0
then 
  /* found */
else
  .. run another select statement to look another table
end if;
end;

the problem is the first select statement sometimes returns 'no rows selected' and tempquantity > 0 fails.

I tried tempquantity number := -1; but had no luck either.
any help?

http://www.fuzzysiberians.com

 

[SantaMufasa]

Falcon,

The problem with doing a "SELECT...INTO..." in PL/SQL is that it requires that precisely ONE row returns from the query...not zero...not two or more. Therefore, you can use this code to ensure that one row returns:

declare 
    tempquantity number;
    tempid number;
    rowcnt number;
begin
    select count(*) into rowcnt from myTable
     where id = 55;
    if rowcnt <> 1 then
        ...<do what you want for this condition>
    else
        select quantity, id
          into tempquantity, tempid 
          from myTable
         where id = 55
        if tempquantity > 0 then 
            /* found */
        else
            .. run another select statement to look another table
        end if;
    end if;
end;

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[FALCONSEYE]

Thanks, I just did something very similar.


defined a tmpCount number;

and used

select count(1) into tmpCount 
from myTable
where id = 55

if tmpCount > 0
then
  -- it's found over in myTable
  DBMS_OUTPUT.put_line ('found it in myTable');
else
  -- run other select
  DBMS_OUTPUT.put_line ('look at other table');
end if;

http://www.fuzzysiberians.com