Re: PL/SQL Cursor with embedded Select on same view

I had this problem once back in the day and it was because the PL/SQL engine version i was using was 'old'. Was something like PL/SQL Release 2.??
Mine worked when used in PL/SQL under Sql*Plus but failed within Forms.

Had to rewrite the query to get round it.

You didnt give us the version number of SQL*Plus so othes cannot advise.
Log into SQL*Plus and cut and paste the version numbers like below.

Connected to:
Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production With the Partitioning option
PL/SQL Release 8.0.6.0.0 - Production

M

jdouvas_at_healthetech.com (JudyD) wrote in message news:<90f4d8fa.0301280958.72f208f_at_posting.google.com>...
> Within SQL Plus, I executed a select statement with an embedded select
> on the same view as the parent select statement. It worked fine.
> Pulling that select statement into PL/SQL under a cursor definition
> statement failed with an error message that the embedded table or view
> didn't exist. Are embedded selects on the same structure as the parent
> disallowed on PL/SQL? Is there a work-around? HELP Please!
>
> CURSOR my_cur IS
> SELECT a.user_id,
> a.log_date,
> a.cola,
> a.colb,
> a.colc,
> a.cold,
> sum(a.amount) amount
> FROM my_view_mv a
> WHERE a.user_id = p_USER_ID
> and a.log_date between p_LOG_DATE1 and p_LOG_DATE2
> and a.cole=(
> SELECT max(a2.cole)
> FROM my_view_mv a2
> WHERE a2.log_date=a.log_date
> and a2.cola=a.cola
> and a2.user_id=a.user_id
> and a2.colb=a.colb
> and a2.food_id=a.food_id)
> GROUP BY a.user_id,
> a.log_date,
> a.cola,
> a.colb,
> a.colc,
> a.cold;
Received on Wed Jan 29 2003 - 03:18:39 CST