Re: Evaluation of SQL SELECT WHERE's

[Arnie Flangehead <arnie.flangehead@xxxxxxxxx>]

Yes, I think Birgitta's solution is more compact and more elegant than
mine.

I've added it to my source code samples for future use.

On Tue, Oct 13, 2020 at 5:35 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> I'd solve the problem as follows:
>
> with myfile( dept, name, years_experience, years_college, PMP)
>   as (values( 'X', 'Tom  ',  8,  3,  'NO '),
>             ( 'X', 'Dick ',  2,  5,  'YES'),
>             ( 'X', 'Harry',  2,  4,  'YES'),
>             ( 'X', 'Peter', 12,  3,  'YES'),
>             ( 'X', 'Tim'  ,  5,  2,  'YES'))
> Select Dept, Name, Years_Experience, Years_College, PMP,
>        Case When Years_experience > 10 then 1 Else 0 End as "Experience",
>        Case When Years_College    >= 4 then 1 Else 0 End as "College",
>        Case When PMP = 'YES'           Then 1 Else 0 End as "With PMP"
> from MyFile
> Where    Years_Experience > 10
>       or Years_College    >=  4
>       or PMP              = 'YES'
> Order By "Experience" Desc, "College" Desc, "With PMP" Desc, Dept, Name ;
>
> BTW the query optimizer does not need to read the table several times.
> It can even use multiple (different) indexes for the same table in the same
> query, return a bit map with in indicator set to on for all matching rows
> for each index, and then merge the bitmaps before accessing the data/rows
> themselves.
> There are also a lot of other techniques/temporary objects, hash tables,
> hash scans etc for accessing the data as fast as possible.
> Just run your query through visual explain and have a look what happens.
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Arnie
> Flangehead
> Sent: Montag, 12. Oktober 2020 14:30
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: Evaluation of SQL SELECT WHERE's
>
> p.s. I forgot to put the second level into the yc ranking, without which
> you
> might get an occasional false result. Should read:
>
> rank() over ( partition by dept order by
>               years_college desc,
>               years_experience desc ) as yc,
> --
> 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