Make names of first, second and third table as sku, acct_type and
Cont_typ.
And name last table that include price as Price.
Then, I thought that it is worth to try following Query.
While I have no environment of AS/400 or iSeries to try it,
I couldn't find no inconsistency with syntax by referencing "DB2
Universal Database for iSeries SQL Reference Version 5 Release 2".
- WITH prf_Price (sku, price, match_level) AS (
-
SELECT P.sku, P.price
-
, CASE
-
WHEN P.Cont_typ = C.Cont_typ
-
AND P.acct_typ = A.acct_type THEN
-
1
-
WHEN P.Cont_typ = C.Cont_typ
-
AND P.acct_typ = '' THEN
-
2
-
WHEN P.Cont_typ = ''
-
AND P.acct_typ = '' THEN
-
3
-
END
-
FROM Cont_typ C
-
, acct_type A
-
, sku S
-
, Price P
-
WHERE A.acct_prf = C.acct_prf
-
AND S.acct_prf = A.acct_prf
-
AND S.acct_no = A.acct_no
-
AND P.sku = S.sku
-
)
-
SELECT sku, price
-
FROM prf_Price PP
-
WHERE match_level
-
= (SELECT MIN(match_level)
-
FROM prf_Price PM
-
WHERE PM.sku = PP.sku
-
)
-
;
