Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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: 1281
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: 6968
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 ejck thru rexx to multiple jobs insid... Susanta All Other Mainframe Topics 5 Tue Sep 19, 2017 1:39 pm
No new posts PLI - printing from multiple programs Pedro PL/I & Assembler 8 Fri Sep 08, 2017 6:36 am
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am
No new posts Multiple VSAM files single output fil... Mohan Kothakota DFSORT/ICETOOL 11 Wed Aug 09, 2017 7:57 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us