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

Hi Joep
You are correct
Apologies


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 Joep Beckeringh via MIDRANGE-L
Sent: Monday, October 12, 2020 4:38 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Joep Beckeringh <joep.beckeringh@xxxxxxxxxx>
Subject: 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

--
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