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

[Quoted] [Quoted] On Jul 6, 3:44 pm, Jim Smith <usene..._at_ponder-stibbons.com> wrote:
> In message <1183716848.286693.174..._at_g37g2000prf.googlegroups.com>,
> Shishir <shishir..._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>

[Quoted] no it dint work before i modified also.. basically what i want to achieve is to check the status of IsActive column which holds binary 0 or 1..i expect this procedute to display "active" when its one and inactive if 0..tats why case statement has been used..

in t-sql this can be easily done by the following statement: DISPLAYTEXT =

	CASE
		WHEN ISACTIVE = 1 THEN 'Active'
		WHEN ISACTIVE = 0 THEN 'InActive'
	END

Shishir. Received on Fri Jul 06 2007 - 13:11:53 CEST