By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,752 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

"Best Fit" Match against multiple tables?

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.