case in pl/sql block.....

[kanna]

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.

[DaPi]

In a recent thread Chris Long recommended using dynamic sql:
http://forums.dbasupport.com/forums/...0&pagenumber=2

[slimdave]

TRUNC(a.date_block_code1) = '31-dec-2001'

... is bad practice. Do ...

TRUNC(a.date_block_code1) = to_date('31-dec-2001','DD-mon-YYYY')

[chrisrlong]

Originally posted by DaPi
In a recent thread Chris Long recommended using dynamic sql:
http://forums.dbasupport.com/forums/...0&pagenumber=2

Yeah, and if Chris Long says you should do it, then you better do it. I hear he's really smart. (although a bit too conceited, if you ask me)

- Chris

[slimdave]

That's actually the type of CASE statement that would convert easily to a decode(), which i believe PL/SQL would have no problem with.

Also, you seem to be doing some rather funky things there -- could ...

Decode(b.block_code_desc,
   'J',Decode(Trunc(a.date_block_code1),
      To_Date('31-dec-2001','DD-mon-YYYY'),'Y'||a.date_block_code1||a.current_balance))

You could even just ...

select
   'Y'||
   to_char(a.date_block_code1,'DD-Mon-YYYY')||
   a.current_balance
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 AND
   b.block_code_desc = 'J' AND
   a.date_block_code1 >= To_Date('31-dec-2001','DD-mon-YYYY') AND
   a.date_block_code1 < To_Date('31-dec-2001','DD-mon-YYYY') + 1

... couldn't you?

I'd also be nervous about the implicit conversion you seem to have on a.current_balance

[sree_sri]

As Slimdave said, You can replace CASE code with DECODE
Or
Pull values using the cursor and use the
"IF-ELSIF-ELSE-ENDIF" statements. That's what I did.

Sree.

[kanna]

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

[DaPi]

Originally posted by chrisrlong
. . . . Chris Long . . . . he's . . . . a bit too conceited, if you ask me

So you've noticed it too

[kanna]

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

[TomazZ]

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.