SQL Statement Producing Error in PL/SQL Casting Multiset to Object Type
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78

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

    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.
    Last edited by AJW_ID01; 10-15-2003 at 06:41 AM.

  2. #2
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width