Solved

How to reference the primary table in a nested select in an Update statement

Posted on 2008-10-24
3
297 Views
Last Modified: 2013-12-07
I'm getting the following error when trying to run the attached Update statement.   It occurs on the line in the nested selects that reference the DT.taskorder field.  

Can anyone help?

PL/SQL:  ORA-00904: "DT"."TaskOrder": invalid identifier.

UPDATE go.Tasks DT

SET	StartDate = 

	(Select startdate from 

		(select startdate 

		from go.Tasks X 

		where X.ePHPID 	= p_ePHPID 

		and X.Method 	= p_Method 

		and X.TaskOrder	= DT.taskorder 

		and X.Subtask 	= 'N' 

		order by X.taskorder)

	Where RowNum = 1),

	EndDate =

	(Select endDate from 

	         (Select endDate 

                                  from go.Tasks W

		where W.ePHPID 	= p_ePHPID 

		and W.method 	= p_Method 

		and W.TaskOrder	= DT.taskorder 

		and W.Subtask	= 'N'  

		order by W.taskorder),

	Where rowNum = 1)

where ePHPID = p_ePHPID 

and Method = p_Method 

and Subtask = 'Y';

Open in new window

0
Comment
Question by:sgrzesik
3 Comments
 
LVL 5

Expert Comment

by:jfmador
ID: 22798449
Hello sorry I don't know much oracle but I you are defining twice your query, Under SQL Server i would give a try to something like this


update Tasks

SET Tasks.StartDate = t.StartDate, Tasks.EndDate = t.EndDate

from Tasks inner join

(

	Select TasksOrder, min(startdate) as startdate, min(endDate) as enddate  

	FROM go.Tasks x

	inner join go.Tasks dt on x.TaskOrder = dt.TaskOrder and x.EPHPID = dt.EPHPID AND x.Method = dt.Method

	where X.ePHPID 	= p_ePHPID 

	and X.Method 	= p_Method 

	and X.Subtask 	= 'N' 

	AND dt.subtask  = 'Y'

	GROUP BY TasksOrder

) t on Tasks.TasksOrder = t.TasksOrder

Open in new window

0
 
LVL 9

Accepted Solution

by:
MarkusId earned 500 total points
ID: 22798604
Hi!

As far as I know it was not possible (at least until version 9) to reference an external column in a nested select. So you might this give a try:
UPDATE go.Tasks DT

SET     StartDate = 

        (Select startdate from 

                (select startdate, X.TaskOrder

                from go.Tasks X 

                where X.ePHPID  = p_ePHPID 

                and X.Method    = p_Method 

                and X.Subtask   = 'N' 

                order by X.taskorder) x

        Where x.taskOrder = dt.taskorder

        and   RowNum = 1),

        EndDate =

        (Select endDate from 

                 (Select endDate, x.TaskOrder

                                  from go.Tasks W

                where W.ePHPID  = p_ePHPID 

                and W.method    = p_Method 

                and W.Subtask   = 'N'  

                order by W.taskorder) w

        Where w.taskorder = dt.taskorder

          and rowNum = 1)

where ePHPID = p_ePHPID 

and Method = p_Method 

and Subtask = 'Y';

Open in new window

0
 

Author Comment

by:sgrzesik
ID: 22799145
That works, thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now