Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to combine multiple queries having similar logic

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Anu Vijayaraghavan

New User


Joined: 13 Feb 2008
Posts: 16
Location: Chennai

PostPosted: Wed Oct 28, 2009 1:25 pm    Post subject: How to combine multiple queries having similar logic
Reply with quote

i have 4 key fields (A,B,C,D) in a WHERE clause.
the selection criteria required is

Select *
from table
where A='something'
and B='something'
and C='something'
and D='something'

if no records are returned by the above query i need to run the next query

Select *
from table
where A='something'
and B='something'
and C='something'

if this also doesnot return any records i need to run the next query
Select *
from table
where A='something'
and B='something'

can these three queries be combined into a single query so that if subquery1 doesnot return any records subquery2 should be run and so on..
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 28, 2009 1:52 pm    Post subject:
Reply with quote

Anu,
You can club in the following way.

Code:
Select *
from table
where
(A='something'
and B='something'
and C='something'
and D='something' ) OR
(A='something'
and B='something'
and C='something' ) OR
(A='something'
and B='something' )


I presume each group will have different values, other wise you can use only last query.
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Oct 28, 2009 3:15 pm    Post subject:
Reply with quote

Hi Srihari,

Above Query will fetch record based upon all three condition.
But as per requirment by Anu.
Second condition is checked only when first condition result in sqlcode = +100 and so on....
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 28, 2009 3:36 pm    Post subject:
Reply with quote

Bhairon,
You are right. But I don't think this can be achieved by a single query.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 28, 2009 4:01 pm    Post subject:
Reply with quote

if you would use a cursor,
and modify the Select as:
Code:

SELECT '4', column, column,colum
FROM TABLE
WHERE <4 conditions

UNION

SELECT '3', column, column,colum
FROM TABLE
WHERE <3 conditions


UNION

SELECT '2', column, column,colum
FROM TABLE
WHERE <2 conditions



then you would have the literal 4, 3 or 2 accompanying each row
(which would indicate which WHERE conditions were satisfied)
then you could do it in one query.


[personal opinion start]
not knowing the goal of the 3 queries makes this a difficult quesiton.
obviously the combined query will take longer than 1 at a time
as well as, 4 or 3 could hit, thus saving the resouces to run the 3d query

if you worked for me, I would tell you to have 3 single queries.
[personal opinion end]
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Wed Oct 28, 2009 4:37 pm    Post subject:
Reply with quote

*del*
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Oct 29, 2009 5:18 am    Post subject:
Reply with quote

I believe both the queries give same result sets. After fetching the row, we can evaluate the fields A,B,C and D to find out which condition has retrieved the row. In addition, UNION in this case will have to scan three times the same table. Please correct me if I am wrong.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Oct 29, 2009 6:16 am    Post subject:
Reply with quote

Srihari G.,

the question was: a single query - I supplied a single query.

now, is the requirement rational or not - I believe not.

if A B C and D are an index, no table scan is necessary.

We don't have enough info to decide if a CURSOR is necessary or not.
We don't know if a singleton select will work.

in order to evaluate the host variables for A, B, C and D we would need to select them.

at a minimum we need a WHERE clause for A and B - without a select for A and B.

but then you have the problem of how to ORDER BY.
because we again have no idea how many rows will be returned,
nor do we know how to ORDER them.

I agree on principle
that the evaluation of what
'WHERE conditions would be satisfied'
should be accomplished with code instead of DB2.

your query would/could run faster,
but my query removes the necessity for complicated host variable compares.

we are arguing about who can spit the farthest;
when we both know we should not be spitting in the first place.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 4 Fri Mar 24, 2017 2:24 pm
No new posts Need help with below queries ashek15 DB2 3 Thu Mar 23, 2017 1:38 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts Need help in building a logic Benchwarmer All Other Mainframe Topics 4 Wed Feb 22, 2017 2:49 am
No new posts Calling procedure with multiple entri... steve-myers PL/I & Assembler 5 Fri Jan 27, 2017 3:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us