How do I code this complex select statement

[Glenn Robinson]

Hello,

I have two tables I'm working on:

In tableA each row consists of an object, its required status and its
current status e.g.

Object Req Status Cur Status

MYOBJECT1 OK NOTOK
MYOBJECT2 *MYSTS PROBLEM

In the above the Req Status column can be a single value (OK) or point to a
list of values (*MYSTS) held in tableB.

If the Req Status column is a list (denoted by the * in the 1st char
position) then I need to refer to tableB for the list of statuses e.g.

tableB

Spec Stat Valid Stat

*MYSTS OK
*MYSTS MAYBE
*MYSTS UNCERTAIN
*JSSTS NOTOK
*JSSTS UNCERTAIN
What I need to do is:

Select all objects from tableA where the Cur Status is < > to the Req Status

That's easy enough. In pseudo programming terms I need to say:

If tableA.Req Status field is a special status (* in first char position)
then compare tableA.Cur Status against the tableB.Valid Status entries
where tableB.Spec Stat = tableA.Req Status

else

just compare the Cur Status to the Req Status column

endif

Can anyone help me on this?

Thanks
Glenn

--
Glenn

[Tonkuma]

An example:
CREATE TABLE GTableA
(Object CHAR(10) NOT NULL
,Req_Status CHAR(10) NOT NULL
,Cur_Status CHAR(10) NOT NULL
);

INSERT INTO GTableA
VALUES
('MYOBJECT1', 'OK', 'NOTOK')
,('MYOBJECT2', '*MYSTS', 'PROBLEM')
;

CREATE TABLE GTableB
(Spec_Stat CHAR(10) NOT NULL
,Valid_Stat CHAR(10) NOT NULL
);

INSERT INTO GTableB
VALUES
('*MYSTS', 'OK')
,('*MYSTS', 'MAYBE')
,('*MYSTS', 'UNCERTAIN')
,('*JSSTS', 'NOTOK')
,('*JSSTS', 'UNCERTAIN')
;

------------------------- Commands Entered --------------------------
SELECT Object
, COALESCE('(V)'||b.Valid_Stat, '(R)'||a.Req_Status) Req_or_Valid
, Cur_Status
FROM GTableA a
LEFT OUTER JOIN
GTableB b
ON SUBSTR(a.Req_Status,1,1) = '*'
AND b.Spec_Stat = a.Req_Status
WHERE a.Req_Status <> a.Cur_Status
ORDER BY
Object;
--------------------------------------------------------------------

OBJECT REQ_OR_VALID CUR_STATUS
---------- ------------- ----------
MYOBJECT1 (R)OK NOTOK
MYOBJECT2 (V)OK PROBLEM
MYOBJECT2 (V)MAYBE PROBLEM
MYOBJECT2 (V)UNCERTAIN PROBLEM

4 record(s) selected.
If all the values of b.Spec_Stat in TableB begin with '*',
ON condition SUBSTR(a.Req_Status,1,1) = '*' would be not necessary.
SELECT Object
, COALESCE('(V)'||b.Valid_Stat, '(R)'||a.Req_Status) Req_or_Valid
, Cur_Status
FROM GTableA a
LEFT OUTER JOIN
GTableB b
ON b.Spec_Stat = a.Req_Status
WHERE a.Req_Status <> a.Cur_Status
ORDER BY
Object;