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;

can anyone hint something about the problem??
thanks in advance..

Cheers,
shishir.

On Jul 6, 3:44 pm, Jim Smith <usene... (AT) ponder-stibbons (DOT) com> wrote:
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.

On Jul 6, 4:11 am, Shishir <shishir... (AT) gmail (DOT) com> wrote:
I suspect it wants END rather than END CASE;

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

On Fri, 06 Jul 2007 18:37:16 -0000, Shishir <shishir456 (AT) gmail (DOT) com>
wrote:

Please learn the one activity for which people exposed to Microsux SQL
are too damn lazy, which is why they are parasiting on forums like
this one:

R E A D T H E F I N E M A N U A L

--
Sybrand Bakker
Senior Oracle DBA

On Jul 6, 6:11 am, Shishir <shishir... (AT) gmail (DOT) com> wrote:

CASE INACTIVE
WHEN 1 THEN 'Active'
WHEN 0 THEN 'InActive'
END DISPLAYTEXT

To prove this correct:

SQL> select a, b, inactive
2 from test;

A B INACTIVE
---------- ---------- ----------
1 1 1
2 1 1
3 1 1
3 4 0

SQL> select a, b,
2 CASE INACTIVE
3 WHEN 1 THEN 'Active'
4 WHEN 0 THEN 'InActive'
5 END DISPLAYTEXT
6 from test;

A B DISPLAYT
---------- ---------- --------
1 1 Active
2 1 Active
3 1 Active
3 4 InActive

SQL>



David Fitzjarrell