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

hi,
[Quoted] [Quoted] 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;

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

                  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. Received on Fri Jul 06 2007 - 12:14:08 CEST