Re: Tricky SQL

[Arjan Hoornstra <ahoor@YAHOO.COM>]

Steve,

The exists sub-query is not correlated. The tables D
and E are not used in another part of the query (each
aliassed table is another one). There must lie your
problem...
I think the last subquery must be
instead of:
>      AND EXISTS (SELECT MAX( D.INVOICE_DT)
you need:
>      AND A.INVOICE_DT = (SELECT MAX( D.INVOICE_DT)

Arjan

--- "Stein, Steve" <Steve.Stein@ISACS.COM> wrote:
> 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.


__________________________________________________
Do You Yahoo!?
Get Yahoo! Mail – Free email you can access from anywhere!
http://mail.yahoo.com/