How do we use CASE statement in a trigger 1

[cba321]

Dear All,

Platform: Windows 2000, Oracle 8i

I am having the following code inside the trigger which is given briefly below,


INSERT INTO ESP_MAT_CONST_OUTPUTS ( BEGIN_DATE, END_DATE, SITE_ID, PROCESS_STREAM_ID, MATCHEM_ID, CONSTITUENT_ID, DAILY_AVG_LIQ_SUR_TEMP_TLA, CONSTITUENT_VAPOR_MOLE_PCT)
SELECT
V.BEGIN_DATE,
V.END_DATE,
V.SITE_ID,
V.PROCESS_STREAM_ID,
V.MATCHEM_ID,
C.CONSTITUENT_ID,
V.DAILY_AVG_LIQ_SUR_TEMP_TLA,
(case when C.CONSTITUENT_VAPOR_WT_PCT Is Null then
(case when V.VP_AT_TLA = 0 then 0 else
GETVP(V.DAILY_AVG_LIQ_SUR_TEMP_TLA,C.CONSTITUENT_ID)*C.CONSTITUENT_MOLE_PCT/V.VP_AT_TLA
end)
FROM ESP_MAT_OUTPUTS V, ESP_MAT_CONSTITUENT C
WHERE V.PROCESS_STREAM_ID = :new.PROCESS_STREAM_ID
AND V.BEGIN_DATE >= :new.BEGIN_DATE
AND V.END_DATE <= :new.END_DATE
AND V.SITE_ID = :new.SITE_ID
AND C.MATCHEM_ID = V.MATCHEM_ID
AND V.DAILY_AVG_LIQ_SUR_TEMP_TLA IS NOT NULL
AND V.VP_AT_TLA IS NOT NULL
AND V.MATCHEM_ID IS NOT NULL;
END;

I get the following error
LINE/COL ERROR
-------- -----------------------------------------------------------------
243/4 PLS-00103: Encountered the symbol &quot;CASE&quot; when expecting one of
the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>



Can anyone please help.

Regards,
cba321

 

[jee]

You will need to use Dynamic SQL to execute your query in PL/SQL in Oracle 8i.

 

[cba321]

Dear Jee,

Can you please help me more with Dynamic SQL as i am not proficient with that and i do not have any idea about it.

Regards,
Rajesh

 

[jee]

Here is an example - not using a CASE statment, but does show how to use Dynamic SQL:

/* Set up to use dynamic SQL to perform lookup*/
v_qry := 'select name from employee' ||
         ' where  empno = ''12345''';
EXECUTE IMMEDIATE v_qry INTO v_name;

If you wanted to be able to pass the employee number in, you can use a variable like this:

/* Set up to use dynamic SQL to perform lookup*/
v_empno := '12345';
v_qry := 'select name from employee' ||
         ' where empno = :en';
EXECUTE IMMEDIATE v_qry INTO v_name USING v_empno;

 

[cba321]

Dear Jee,

Thanks a lot.

To be frank and honest with you, I did not or could not understand dynamic sql. So i took an alternative, I built a function and called it from inside the select statement in the trigger.

Thank you once again for all the patience and help.

Regards,
Rajesh