case in pl/sql block.....
Hi all,
I am using oracle 8.1.0.6,
here i am giving a case statement in sql statement
SELECT (CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN 'Y' END),
(CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN a.date_block_code1 END),
(CASE WHEN ((b.block_code_desc='J') AND TRUNC(a.date_block_code1) = '31-dec-2001') THEN a.current_balance END)
FROM L_CUST_ACCT_DAILY a, ref_block_code b
WHERE a.countrycode = 'UK'
AND b.countrycode = 'UK'
AND a.block_code1 = b.block_code;
it's working.
if i am giving that case statement in pl/sql block
declare
a varchar2(100);
b varchar2(100);
c varchar2(100);
begin
SELECT
CASE WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
'Y'
END case1,
CASE
WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
a.date_block_code1
END case2,
CASE
WHEN b.block_code_desc='J' AND TRUNC(a.date_block_code1) = '31-dec-2001' THEN
a.current_balance END case3 into a, b, c
FROM L_CUST_ACCT_DAILY a, ref_block_code b
WHERE a.countrycode = 'UK'
AND b.countrycode = 'UK'
AND a.block_code1 = b.block_code;
dbms_output.put_line(a || b || c);
end;
then it is giving the following error..
ERROR at line 7:
ORA-06550: line 7, column 4:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( * - + all mod null
table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date
;
can anyone help regarding this......
thanks in advance...
kanna.
thanks for your replies regarding case to decode
hi all,
thanks to everyone for the answer,
but i finished yesterday itself with DECODE,
i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?
this is my query.....
so please clarify this......
thanks and regards
kanna
thanks for your replies regarding case to decode
hi all,
thanks to everyone for the answer,
but i finished yesterday itself with DECODE,
i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?
this is my query.....
so please clarify this......
thanks and regards
kanna
Re: thanks for your replies regarding case to decode
Quote:
Originally posted by kanna
i want to know whether we can use CASE statement in pl/sql block of oracle version 8.1.0.6?
No, you cannot.