467,198 Members | 1,237 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,198 developers. It's quick & easy.

"Best Fit" Match against multiple tables?

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

Jun 28 '07 #1
  • viewed: 2066
Share:
1 Reply
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".

Expand|Select|Wrap|Line Numbers
  1. WITH prf_Price (sku, price, match_level) AS (
  2. SELECT P.sku, P.price
  3. , CASE
  4. WHEN P.Cont_typ = C.Cont_typ
  5. AND P.acct_typ = A.acct_type THEN
  6. 1
  7. WHEN P.Cont_typ = C.Cont_typ
  8. AND P.acct_typ = '' THEN
  9. 2
  10. WHEN P.Cont_typ = ''
  11. AND P.acct_typ = '' THEN
  12. 3
  13. END
  14. FROM Cont_typ  C
  15. , acct_type A
  16. , sku       S
  17. , Price     P
  18. WHERE A.acct_prf = C.acct_prf
  19. AND S.acct_prf = A.acct_prf
  20. AND S.acct_no  = A.acct_no
  21. AND P.sku      = S.sku
  22. )
  23. SELECT sku, price
  24. FROM prf_Price PP
  25. WHERE match_level
  26. = (SELECT MIN(match_level)
  27. FROM prf_Price PM
  28. WHERE PM.sku = PP.sku
  29. )
  30. ;
Jun 29 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Wm | last post: by
8 posts views Thread by elias.farah@scw.com.au | last post: by
1 post views Thread by =?ISO-8859-1?Q?Andr=E9?= | last post: by
11 posts views Thread by 200dogz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.