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

[Quoted] On Jul 6, 4:11 am, Shishir <shishir..._at_gmail.com> wrote:
> 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>
>
> 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
> can u give me its pl/sql version..?
>
> Shishir.- Hide quoted text -
>
> - Show quoted text -

I suspect it wants END rather than END CASE; Received on Fri Jul 06 2007 - 20:09:28 CEST