|
View previous topic :: View next topic
|
| Author |
Message |
Anu Vijayaraghavan
New User
Joined: 13 Feb 2008
Posts: 16
Location: Chennai
|
|
|
|
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 |
|
 |
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
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 |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
Bhairon,
You are right. But I don't think this can be achieved by a single query. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| *del* |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
