Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Subselect questions
Hi Mark,
I believe the variable is in scope.
I have :
BEGIN
Declare vTripStartDate date
BEGIN
Select Into vTripStartDate
END;
SQL STATEMENT
END;
One thing I didnt mention is that this is an Update statement.
update table
set (var1, var2, var3) = ( Select Statement )
So does it lose the scope because of the update select?
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
news:y6adncoI4tC0-NjZRVn-gw_at_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:50:47 CDT