Re: Subselect questions

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 18 Apr 2006 18:16:09 -0400
Message-ID: <y6adncoI4tC0-NjZRVn-gw@comcast.com>

"MD" <md_at_md.com> wrote in message news:H1d1g.16527$IZ2.13038_at_dukeread07... Hi,
I'm using 9i and I have this join in a procedure:

INNER JOIN
    (
    Select EscPer.escalationpercent, EscPer.headerid     From delta.tblriversegescalation EscPer

        INNER JOIN
        (
            Select MAX(EffectiveDate) as EffectiveDate, HeaderID
            From delta.tblriversegescalation seg
            Where trunc(seg.effectivedate) <= trunc(vTripStartDate)
            Group By HeaderID
        ) MaxEsc
        ON MaxEsc.EffectiveDate = EscPer.EffectiveDate
                and MaxEsc.headerid = EscPer.headerid

    ) SegEsc
ON SegEsc.headerid = SegHead.recordid

My question is,
Is there any way to have a local variable declared in a subselect?

Thanks,
MD

SQL doesn't have variables -- so if you're talking about declaring a PL/SQL variable, no, that needs to be done in PL/SQL, not in any part of the SQL statement

where does your vTripStartDate get its value? it needs to be declared in that context and referenced either as an in-scope PL/SQL variable or an in-scopy host variable, or perhaps as a PL/SQL parameter

++ mcs Received on Tue Apr 18 2006 - 17:16:09 CDT