OK I'm trying to create a sql query on our as400. I have a main table
like this:
acct prf acct no. sku
AAA 123 ABCD
AAA 123 XYZ
I have another table
Acct prf acct no. acct type
AAA 123 BU
and another
Acct prf Cont typ
AAA BUA
Finally, I have the table I want to pull information from:
Cont Typ acct typ sku price
XYZ 5.00
BUA XYZ 4.50
BUA BU XYZ 4.00
BUA ABCD 10.00
ABCD 12.00
I need to pull the price for each sku in my main table. I want a
perfect match of Cont, Acct, Sku if I can get it, a Cont, acct=' ',
sku match if not, and a Cont=' ', acct=' ', sku match if neither of
those exist. I tried using a correlated subquery, but can't get it to
work.
Please help.
Lee
Tonkuma - 29 Jun 2007 17:19 GMT
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".
[code]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
)
;[/code]