Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / June 2007

Tip: Looking for answers? Try searching our database.

"Best Fit" Match against multiple tables?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pythor - 28 Jun 2007 14:58 GMT
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]
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.