--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> What about something like
>
> SELECT UID, COALESCE(CASE WHEN FAMET = :FAMET2 THEN 4 ELSE 0 END) +
> COALESCE(CASE WHEN FALLYKSUS = :FALLYKSUS2 THEN 3 ELSE 0 END) +
> COALESCE(CASE WHEN FOSAKOND = :FOSAKOND2 THEN 2 ELSE 0 END)
> AS PRIORITY
> FROM TAB_T
> WHERE TSOON = :TSOON
> AND (FAMET = :FAMET OR FAMET IS NULL)
> AND (FALLYKSUS = :FALLYKSUS OR FALLYKSUS IS NULL)
> AND (FOSAKOND = :FOSAKOND OR FOSAKOND IS NULL)
> ORDER BY 2 DESC

Thanks Set, I'll test this one too! In the meantime I came up with
similar solution:

SELECT FIRST 1 UID,
COALESCE(FOsakond - :Osakond, 1) +
COALESCE(FAllyksus - :AYksus, 2) +
COALESCE(FAmet - :Amet, 4)
FROM TAB_T
WHERE(Tsoon = :Tsoon)AND
((FAmet = :Amet)OR(FAmet IS NULL))AND
((FOsakond IS NULL)OR(FOsakond = :Osakond))AND
((FAllyksus = :AYksus)OR(FAllyksus IS NULL))
ORDER BY 2 ASC

So the exact match should have "weigth" 0 and worst match 7. I say
"should" as I haven't actually tested it, but it looks OK "on paper" :)

> I guessed that matching FOSAKOND and FALLUKSUS with FAMET NULL would be
> better than matching FAMET with FOSAKOND AND FALLUKSUS both NULL, if

not

> you'll have to change the values (e.g. CASE FALLYKSUS = 2 and

FOSAKOND = 1).

I decided (I'm implementing new feature and do have luxury to change
requirements a bit) to make it other way around - matching FAMET with
FOSAKOND AND FALLUKSUS both NULL is better than matching FOSAKOND and
FALLUKSUS with FAMET NULL. This makes it a bit simpler and easier to
follow...


ain