Re: SQL SELECT: controlling the order of evaluation

[x y <xy6581@xxxxxxxxx>]

Paul, thanks but this is the reverse of what I'm trying to do (this feature
will be useful anyway).  I want to specify the evaluation order: look for a
matching ZIP first, and if not found, a matching city, and if not found. a
matching state all in one SELECT.

More experimenting!

On Thu, Feb 2, 2023 at 12:39 PM Therrien, Paul via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> Look at Case When ... clause. For each value found return a different
> value
>         Case
>         When zip = 33166  'ZIP'
>               When City 'MAIMI' 'CITY'
>         When county = 'DADE'  'COUNTY'
>         When State = 'FL' 'STATE
>         End as MatchField
>
> The column MatchField in your result will tell you which value matched:
> ZIP/CITY/COUNTY/STATE
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of x y
> Sent: Thursday, February 2, 2023 3:26 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: SQL SELECT: controlling the order of evaluation
>
> When I have multiple conditions on my WHERE clause, can I control the
> order in which they're evaluated?
>
> Example: the client in postal code 33166, Miami, Dade County, Florida, is
> looking for a local resource.  I'd like to write select {} from {} where
> state='FL' or county='DADE' or city='MIAMI' or postal='33166' fetch first
> row only and then tell SQL to return a result by evaluating first on
> postal, then city, then county, and then state.
>
> I'm trying to avoid multiple SELECT statements.  It's impractical to add
> "helper" data in the table, which will have > 100 million records.  Putting
> "postal='33166'" first doesn't provide the desired result.  A UNION ALL
> won't work.  I don't think a JOIN will work but I'm trying to figure out
> how it might work.
>
> It looks like SQL selects the first row that meets any of the selection
> criteria, i.e., SQL is giving all the conditions equal weight).  I'm using
> CASE in my ORDER BY for other requirements but I can't figure out if that's
> the solution here.
>
> Thanks for your ideas!
>
> --x
> --
> 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.
>
> --
> 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.