Re: Problem with Cursor-Please help!

On Wed, 2 Jan 2002 11:29:37 -0500, "Raj" <rajattili_at_hotmail.com> wrote:

>Hi,
>
>I am having a weird problem with an SQL I am trying to use as a cursor. Here
>is the SQL:
>
>select * from group_at_ruat t1
> where nvl(trunc(update_date),trunc(sysdate))>
> decode((select trunc(upd_date)
> from process_track
> where upper(ltrim(rtrim(table_name)))='GROUP'
> and id=t1.quest_group),null,trunc(sysdate))
>
>The problem is that this runs fine if I run it from the SQL prompt and
>returns me data, but if I put it as a cursor, I get the following error
>messages:
>
>PLS-00103: Encountered the symbol "SELECT" when expecting one of
>the following:
>( - + mod not null others <an identifier>
><a double-quoted delimited-identifier> <a bind variable> avg
>count current exists max min prior sql stddev sum variance
>execute forall time timestamp interval date
><a string literal with character set specification>
><a number> <a single-quoted SQL string>
>
>PLS-00103: Encountered the symbol "," when expecting one of the
>following: ;
>
>The SELECT mentioned in the above error message is the select in the 3rd
>line of my SQL statement. The second error message refers to the first ","
>in the last line. I've been trying to fix this for the last couple of days,
>but couldn't do it. Can anyone point me to where am doing this wrong?
>
>Thanks for your help.
>
>-Raj
>

Move the decode inside the subquery
select decode( trunc(upd_date), null, trunc(upd_date), trunc(sysdate) which is just equivalent to trunc(nvl(upd_date, sysdate))

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jan 02 2002 - 11:39:39 CST