I'm having a problem using syntax
SUM(CASE .. WHEN .. THEN END)
When it's used within as query, it works fine. When I enclose it within a BEGIN END block, it fails. For example:
create a temp table:
create table test_1 as select 'aa' COL1, 0 COL2 from dual;
Then insert a row:
insert into test_1(col1, col2)
select 'AA' COL1,
(case when 'V2' = 'V2' then '1' else '0' end ) COL2
from dual;
Result message is: 1 row created
This succeeds. Try to insert another row, same syntax wrapped in a begin and end:
begin
insert into test_1(col1, col2)
select 'AA' COL1,
(case when 'V2' = 'V2' then '1' else '0' end ) COL2
from dual;
end;
result message is:
Error at line 1
ORA-06550: line 4, column 6:
PLS-00103: Encountered the symbol "CASE" 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>
The error codes basically say something is wrong with the syntax but no clues as to what. Please, what am I doing wrong?
Thanks,
Dave