Re: [EXTERNAL] Evaluation of SQL SELECT WHERE's

Alan, I think you missed the 'or' in the where clause. You start by selecting on years experience and then narrowing it down. But if there is noone with more than 10 years experience you get nothing.

Reeve, I think you are missing an order by, so even if there are persons with a lot of experience you might end up with someone just having PMP 'YES'.

Building on Alan's solution:

With selection1 (type, x, y, z) as (Select 1, x, y, z from MyFile where years_experience > 10),
     selection2 (type, x, y, z) as (Select 2, x, y, z from MyFile where years_college >= 4),
     selection3 (type, x, y, z) as (Select 3, x, y, z from MyFile where PMP = 'YES')
      select type, x, y, z  from selection1
union select type, x, y, z  from selection1
union select type, x, y, z  from selection1
order by type
fetch first row only;

You could refine the order by; if there are two people with more than 10 years experience, you don't know which one you get.

Joep Beckeringh

Op 12-10-2020 om 10:10 schreef Alan Shore via MIDRANGE-L:

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