Hi Reeve
I use CTE's - Common Table expressions
Something like this
With selection1 as
(Select x,y,z from MyFile
where years_experience > 10),
selection2 as
(Select x,y,z from Selection1
where years_college >= 4),
selection3 as
(Select x,y,z from selection2
where PMP = 'YES')
Select x,y,z from selection3
fetch first row only
I don't know if you are familiar with CTE's
But if you look at the query - each select is looking at the last "file" created
Hope this makes sense
I would be interested to see If anyone has any other suggestions
Im always willing to learn new things
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Reeve
Sent: Monday, October 12, 2020 12:49 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: [EXTERNAL] Evaluation of SQL SELECT WHERE's
If I have a collection of WHERE clauses...
Select x,y,z from MyFile
where years_experience > 10
or years_college >= 4
or PMP = 'YES'
fetch first row only
...are the clauses tested in order or does the optimizer find one row from the optimized access path (or through a table scan) regardless of how the statement is coded and quit?
In this example, the goal is to select a row where years_experience > 10, and if no rows qualify, select a row where years_college >= 4, and so on.
Other than executing multiple SQL statements, is there a good technique for controlling the selection process? I'm trying to get my head around an ORDER BY/CASE construct but I don't quite have it.
Thanks!
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
