CODEWITHSUNDEEP
sqldb2existswith-statement
rogdawg
rogdawg

Reputation: 687

what is the best way to select the first query that returns a result in DB2

I want to implement this type of logic, using DB2:

Create Procedure sp_MyProcedure (
    IN Var1 Decimal(6, 0),
    IN Var2 Decimal(6, 0)
) Language SQL

Dynamic Result Sets 1

Begin

If Exists(Select * from MyTable where CustomerNbr = Var1) Then

    return (Select * from MyTable where CustomerNbr = Var1)

Else If Exists(Select * from MyTable where CustomerNbr = Var2) Then

    return (Select * from MyTable where CustomerNbr = Var2)

Else

    return (Select * from MyTable where CustomerNbr = 0)

End If

End

But can't figure out the syntax to implement this type of logic. Plus, I am running each query twice; once to check that it returns values and, if it does return values, once to create the returned set of data. So, I know there has to be a better way. I have been looking at the "With" statement to create temporary tables but, so far it has essentially the same issues of inefficiency and syntax limits.

(I apologize for the formatting of the code. I can't seem to get it to work right in this text editor) Can someone suggest the best way to accomplish this? Thanks, in advance, for your advice.

Upvotes: 0

Views: 140

Answers (3)

wvdz
wvdz

Reputation: 16651

Best I can think of is as such:

SELECT * FROM MyTable
WHERE custNo = Var1
OR (custNo != Var1
AND custNo = Var2)
OR (custNo != Var1
AND custNo != Var2
AND custNo = 0);

Upvotes: 1

WarrenT
WarrenT

Reputation: 4542

If one works you want to exclude the others. How about this?

with A as
(select *
   from mytable
   where customer = var1
), Ax as
(select count(*) as tally
   from a
), B as
(select * 
   from mytable, Ax
   where Ax.tally = 0
     and customer = var2 
), Bx as
(select count(*) as tally
   from B
), C as
(select *
   from mytable, Ax, Bx
   where Ax.tally=0
     and Bx.tally=0
     and Customer=0
)
select * from A
UNION
select * from B
UNION
select * from C

Upvotes: 0

Hogan
Hogan

Reputation: 70538

Here is an ugly select statement that will work:

With allrecords as
(
  Select 1 as qnum,* 
  from MyTable 
  where CustomerNbr = Var1
  union all 
  Select 2 as qnum, *
  from MyTable
  where CustomerNbr = Var2
  union all
  Select 3 as qnum, * 
  from MyTable 
  where CustomerNbr = 0
)
select *
from allrecords
where qnum = (select min(qnum) from allrecords)

Note, if you don't want qnum returned in the result then you have to give a select list for the final select statement that does not include qnum.

Upvotes: 2

Related Questions