--- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@...>
wrote:

>
> Hi,
>
> I have table like
>
> CREATE TABLE TAB_T (
> UID INTEGER PRIMARY KEY,
> Tsoon INTEGER NOT NULL,
> FOsakond INTEGER,
> FAllyksus INTEGER,
> FAmet INTEGER
> );
>
> and I want to get UID for a given Tsoon value which best matches with
> remaining fields. Ie given Tsoon = 10, FAllyksus = 1, FOsakond = 2,
> FAmet = 3, if there is a row with these values then I want it's UID.
> In case there isn't exact match, next best would be one where FOsakond
> IS NULL but other columns do match.
> If there isn't such one, then I want one where FAllyksus IS NULL but
> FOsakond and FAmet do match. And so on... is there a way to make such
> a query or do I have to try one by one till I find combination which
> exists?

This sort of logic is simple in a stored procedure.

SET TERM ^ ;

CREATE OR ALTER PROCEDURE SP_TAB_T_UID
(
Tsoon INTEGER,
FOsakond INTEGER,
FAllyksus INTEGER,
FAmet INTEGER
)
RETURNS
(
UID INTEGER
)
AS
BEGIN
UID = NULL;

SELECT UID
FROM TAB_T
WHERE Tsoon = :TSOON
AND FAllyksus = :FAllyksus
AND FOsakond = :FAllyksus
AND FAmet = :FAmet
INTO :UID;

IF (UID IS NOT NULL) THEN
BEGIN
SUSPEND;
LEAVE;
END

-- FOsakond IS NULL but other columns do match.

SELECT UID
FROM TAB_T
WHERE Tsoon = :TSOON
AND FAllyksus = :FAllyksus
AND FOsakond IS NULL
AND FAmet = :FAmet
INTO :UID;

IF (UID IS NOT NULL) THEN
BEGIN
SUSPEND;
LEAVE;
END

-- etc
END
^

SET TERM ; ^

COMMIT;

SELECT UID
FROM SP_TAB_T_UID(10, 2, 1, 3);

---

I am not going to do all your work for you, but you can see how the
above procedure works, and it is a reasonably trivial job to carry on
the procedure to your particular requirement.

Adam