Re: Tricky SQL

["Aguirre, Jose' A" <jaguir@LSUMC.EDU>]

select  FIELD
from    TABLE
where   FIELD A= FIELD B
AND     L.EFFDT =       (select max(EFFDT)
                         from   PS_VENDOR_LOC l2
                         where  l2.VENDOR_ID = L.VENDOR_ID
                         and    l2.VNDR_LOC = L.VNDR_LOC
                         and    l2.EFFDT <= GETDATE())


José A. Aguirre


-----Original Message-----
From: Stein, Steve [mailto:Steve.Stein@ISACS.COM]
Sent: Monday, July 10, 2000 11:02 AM
To: SQR-USERS@LIST.IEX.NET
Subject: Tricky SQL


I need to do a query where it takes the latest dated row from one table
(PS_VOUCHER_LINE) and the latest dated row from an effective dated table
(PS_CM_PRODCOST).  It is probably very simple but I'm having no luck.
Anyone can help?

So far I used psquery tool to get this SQL:

SELECT B.MERCHANDISE_AMT, B.INV_ITEM_ID, C.TL_COST, C.BUSINESS_UNIT,
C.INV_ITEM_ID, TO_CHAR(A.INVOICE_DT,'YYYY-MM-DD')
  FROM PS_VOUCHER A, PS_VOUCHER_LINE B, PS_CM_PRODCOST C
  WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
     AND A.VOUCHER_ID = B.VOUCHER_ID
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_CM_PRODCOST C_ED
        WHERE C.BUSINESS_UNIT = C_ED.BUSINESS_UNIT
          AND C.INV_ITEM_ID = C_ED.INV_ITEM_ID
          AND C.CONFIG_CODE = C_ED.CONFIG_CODE
          AND C_ED.EFFDT <= SYSDATE)
     AND C.INV_ITEM_ID = B.INV_ITEM_ID
     AND EXISTS (SELECT MAX( D.INVOICE_DT)
  FROM PS_VOUCHER D, PS_VOUCHER_LINE E
  WHERE D.BUSINESS_UNIT = E.BUSINESS_UNIT
     AND D.VOUCHER_ID = E.VOUCHER_ID)
     AND B.INV_ITEM_ID = 'A039901100'

but it was returning all the PS_VOUCHER_LINE rows

             11.52      A039901100               10.0000        I0005
A039901100      1999-08-26
             17.28      A039901100               10.0000        I0005
A039901100      1999-10-06
             12.96      A039901100               10.0000        I0005
A039901100      1999-10-21
             23.04      A039901100               10.0000        I0005
A039901100      1999-12-01
             17.28      A039901100               10.0000        I0005
A039901100      1999-12-10
             17.28      A039901100               10.0000        I0005
A039901100      1999-12-10.