SQL Statement Producing Error in PL/SQL Casting Multiset to Object Type

[AJW_ID01]

I'm trying to improve some data processing by manipulating some subset data via object types.

I initially ran this SQL statement, inserting into a table, which runs fine:

insert into readflag_test
select
mpan_core,
CASE
WHEN Sumcount > 0 then 'Val Rd'
ELSE 'Read' END as Readflag
from
(
select
mpan_core,
sum(Readflag) Sumcount
from(
select distinct
a.msy_mpan_core as mpan_core,
CASE
WHEN a.effective_from_date > b.effective_from_date and b.last_read_date > b.effective_from_date then '1'
WHEN a.effective_from_date <= b.effective_from_date and b.last_read_date > b.effective_from_date then '0'
ELSE null END as Readflag
from
reading_sets a,
(select mpan_core, effective_from_date, last_read_date from oracle81.noreads_report_safe) b
where
a.msy_mpan_core||null = b.mpan_core and
reading_completeness!='E' and
a.reading_status in ('0','1') and
a.reading_state||null='4')
where Readflag is not null
group by mpan_core);


I then attempted to run it as a component in a PL/SQL routine as follows:

select cast(multiset(
select
mpan_core,
CASE
WHEN Sumcount > 0 then 'Val Rd'
ELSE 'Read' END as Readflag
from
(select
mpan_core,
sum(Readflag) Sumcount
from
(select distinct
a.msy_mpan_core as mpan_core,
CASE
WHEN a.effective_from_date > b.effective_from_date and b.last_read_date > b.effective_from_date then '1'
WHEN a.effective_from_date <= b.effective_from_date and b.last_read_date > b.effective_from_date then '0'
ELSE null END as Readflag
from
reading_sets a,
(select mpan_core, effective_from_date, last_read_date from oracle81.noreads_report_safe) b
where
a.msy_mpan_core||null = b.mpan_core and
reading_completeness!='E' and
a.reading_status in ('0','1') and
a.reading_state||null='4')
where Readflag is not null
group by mpan_core))
as noreads_readflag_table_type)
into v_noreads_readflag_table
from dual;

This is throwing out some error message as follows, and I don't seem to be able to deduce where it is!

CASE WHEN Sumcount > 0 then 'Val Rd' ELSE 'Read' END
*
ERROR at line 114:
ORA-06550: line 114, column 1:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod null

table avg count current max min prior sql stddev sum variance
execute the forall time timestamp interval date



Is there some constraint on the use of CASE statements for casting multisets? Or is there some basic error I have overlooked?

I can't understand why it seems to run fine on SQL command line inserting to a table, but then falls over when inserting to ab object-type.

:-(


- Tony.

[AJW_ID01]

Hmmm,

After further testing, I'm given over to thinking that perhaps you can't have CASE statements occurring within SQL statements inside PL/SQL blocks.

Comments?

- Tony

[slimdave]

In 8i, PL/SQL had it's own SQL engine that lagged the regular engine in features. In 9i there is only one SQL engine, so this would not be an issue.

You can workaround the problem by using dynamic sql in 8i PL/SQL for this situation.