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

On Jul 6, 11:09 am, Patrick <pgov..._at_u.washington.edu> wrote:
> 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;

[Quoted] But the syntax of case statement in pl/sql expects end case followed by semicolon..or can u propose some alternative solution for this problem in pl/sql. Received on Fri Jul 06 2007 - 20:37:16 CEST