Re: case inside a select statement..how to deal wit it??

In message <1183716848.286693.174930_at_g37g2000prf.googlegroups.com>, Shishir <shishir456_at_gmail.com> writes
>
>hi,
>how to deal with a case statement inside a select statement as shown
>below :
>This is the pl/sql code that is generated by the sqldeveloper for its
>corresponding t-sql code:
>Im getting these errors on compiling :
>Line # = 8 Column # = 7 Error Text = PL/SQL: SQL Statement ignored
>Line # = 12 Column # = 73 Error Text = PL/SQL: ORA-00923: FROM keyword
>not found where expected
>Line # = 24 Column # = 9 Error Text = PLS-00103: Encountered the
>symbol "FROM" when expecting one of the following:
>
> begin case declare end exception exit for goto if loop mod
> null pragma raise return select update while with
> <an identifier> <a double-quoted delimited-identifier>
> <a bind variable> << close current delete fetch lock insert
> open rollback savepoint set sql execute commit forall merge
> pipe
>
>
>CREATE OR REPLACE PROCEDURE usp_ESTMATEGetProjectList
>(
> cv_1 IN OUT SYS_REFCURSOR
>)
>AS
>BEGIN
> OPEN cv_1 FOR
> SELECT A.ProjectId,
> A.ProjectCode,
> A.ProjectName,
> A.DESCRIPTION,
> sqlserver_utilities.convert('CHAR(15)', A.CreateDate,
>106) Date,
> A.OWNER,
> A.StartDate,
> A.EndDate,
> A.StatusId,
> A.IsActive,
> A.TemplateId,
> B.StatusName,
> CASE
> WHEN ISACTIVE = 1 THEN DISPLAYTEXT:='Active'
> WHEN ISACTIVE = 0 THEN DISPLAYTEXT:='InActive'
> END CASE;
> FROM PROJECTPROJECTMAIN A,
> PROJECTPROJECTSTATUS B
> WHERE A.STATUSID = B.STATUSID;
>END;
> i modified the initial code which was something like :
>CASE
> WHEN ISACTIVE = 1 THEN 'Active'
> WHEN ISACTIVE = 0 THEN 'InActive' END DISPLAYTEXT
>to
>CASE
> WHEN ISACTIVE = 1 THEN DISPLAYTEXT:='Active'
> WHEN ISACTIVE = 0 THEN DISPLAYTEXT:='InActive'
> END CASE;
>
Why did you modify it? Did it work before you modified it?

The error is almost certainly because you can't do assignment (DISPLAYTEXT:=) in a sql statement.

What were you trying to achieve?

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>