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

Question

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.

score 2 · Accepted Answer · answered Nov 15 '13 at 21:48

2

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.

answered Nov 15 '13 at 21:48

Hogan

69,564
10
76
117

I up-voted this because it is such a clever approach. I took the other response as the answer but, thank you for showing me this approach. – rogdawg Nov 15 '13 at 23:59
@rogdawg - The other answer gives different results. Which did you want -- test them both and see. – Hogan Nov 16 '13 at 00:23
Thanks for your follow-up, @Hogan. You are correct. I spent the morning testing these in my actual testing environment, and your answer is returning the correct response. I really like the technique of providing a "hard-coded" marker (qnum) for each result set, then choosing the minimum value, to get the first response. I would imagine that technique, and variations on it, will come in handy in the future. Thanks again. – rogdawg Nov 16 '13 at 16:10
Yes this as you call it "marker" technique is often used with union queries -- mostly for sorting (for example if you want one query to have a "roll-up" or total line at the end of a result set.) – Hogan Nov 17 '13 at 03:05

wvdz · Answer 2 · 2013-11-16T11:20:28.803

1

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);

edited Nov 16 '13 at 11:20

answered Nov 15 '13 at 21:46

wvdz

16,251
4
53
90

won't this give the results of all 3 select statements, not just one? – Hogan Nov 15 '13 at 21:50
1

I may have misinterpreted the question. This will apply the logic for each row. – wvdz Nov 15 '13 at 21:52
I think this will work. Each of the three conditions in the Where clause excludes the other two conditions. It is a relatively simple query, that doesn't do the same work over and over. I knew there had to be a simpler answer than my approach. Thanks very much. – rogdawg Nov 15 '13 at 23:56
It only excludes the other conditions on a particular row, but does not prevent multiple rows from being returned. It is an incorrect answer. – WarrenT Nov 16 '13 at 00:27
Note that this still returns all three rows (and in random order, no less). Oh, and there's some syntax errors. – Clockwork-Muse Nov 16 '13 at 11:12
Yep. I have tested this, and it is returning records from each of the predicates, or Where conditions. It should only return results from one of the where conditions, and exclude the other two. Thank you for your efforts, @popovitsj, I really appreciate it. I will mark Hogan's response as the answer. I have tested it in my actual production environment and the results are correct. Thanks again. – rogdawg Nov 16 '13 at 16:06

score 0 · Answer 3 · answered Nov 16 '13 at 00:46

0

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

answered Nov 16 '13 at 00:46

WarrenT

4,502
19
27

I like this, it will work -- but my answer will run faster. Also, why the old style joins Warren? – Hogan Nov 17 '13 at 03:03
Normally I wouldn't have used the old style join, but my understanding is that the performance is the same. – WarrenT Nov 17 '13 at 03:27
There is no performance difference between join types. I mean my query has 3 select statements and yours has 6 -- so yours will not run as fast. – Hogan Nov 17 '13 at 03:32

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

3 Answers3