Reference Pivoted Cursor in PL/SQL

Question

In Relation to my Other question (Pivot Multiple Tables with Default Values),

How can i use the result set of a Pivoted SQL in a PL/SQL block? Data set is below:

ELEMENT_NAME                ELEMENT_ENTRY_ID    1_INPUT_VALUE       1_SCREEN_ENTRY_VALUE    2_INPUT_VALUE   2_SCREEN_ENTRY_VALUE    3_INPUT_VALUE   3_SCREEN_ENTRY_VALUE    4_INPUT_VALUE   4_SCREEN_ENTRY_VALUE    5_INPUT_VALUE   5_SCREEN_ENTRY_VALUE        
------------------------    ------------------- ----------------    --------------------    --------------- ----------------------- --------------- ----------------------  --------------- ----------------------- --------------- ----------------------
VERTEX                      72249257            393                 0                       395                                     391                                     392             33-000-0000             394                                         
ALC Workers Compensation    72249258            393                 0                       395                                     394                                     391                                     392             33-065-0000                 
PTO Taken Plan              72523856            392                 33-065-3190             391                                     393             0                       395                                     394                                         
Regular Wages               72249260            234                 9.7                     236                                     235                                     233                                                                                 
US_TAX_VERTEX               72249259            391                                         394                                     393             100                     395                                     392             33-065-2920                 
Workers Compensation        72249256            455                                         456             33-000-0000

I'm trying to use the provided Query in the answer in the block below but Its erroring out:

declare

    cursor C_cur is
    select *
    from    (With src As (SELECT  lmnt.element_name
                              ,   lmnt.element_entry_id
                              ,   nval.input_value_id
                              ,   nval.screen_entry_value
                              ,   row_number() over (partition by lmnt.element_name, lmnt.element_entry_id
                                                     order by lmnt.element_name  ) rn
                          FROM    XX_SAMPLE_ELEMENTS lmnt
                              ,   XX_ENTRY_VALUES    nval
                              ,   XX_ELEMENT_VALUES  eval
                          where   lmnt.element_entry_id = nval.element_entry_id
                          and     eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID          
                          order by lmnt.element_name)
            SELECT * FROM src
            PIVOT (max( input_value_id ) As input_value,
                   min( screen_entry_value ) as screen_entry_value
                   FOR (rn) IN (1,2,3,4,5)));

    l_input_value  varchar2(100);

begin

    for C_rec in C_cur loop
        l_input_value := C_rec.1_INPUT_VALUE;
    end loop;

end;

PLS-00103: Encountered the symbol ".1" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

How can i Reference the 1st Input value 1_INPUT_VALUE?

You may be able to reference that field as a quoted identifier "1_INPUT_VALUE". However, I would suggest aliasing the IN list. For example: FOR (rn IN (1 AS N1, 2 AS N2.... Doing so should make your pivoted column names N1_INPUT_VALUE, which I believe won't need to be enquoted. — Chris Hep
@HepC, that definitely worked! please put that as an answer so I can up-vote. — Migs Isip

Chris Hep Chris Hep · Accepted Answer · 2017-08-10T22:08:15

You have two options to resolve this issue. The first is to use a quoted identifier, surrounding the column name in double quotes.

declare

    cursor C_cur is
    select *
    from    (With src As (SELECT  lmnt.element_name
                              ,   lmnt.element_entry_id
                              ,   nval.input_value_id
                              ,   nval.screen_entry_value
                              ,   row_number() over (partition by lmnt.element_name, lmnt.element_entry_id
                                                     order by lmnt.element_name  ) rn
                          FROM    XX_SAMPLE_ELEMENTS lmnt
                              ,   XX_ENTRY_VALUES    nval
                              ,   XX_ELEMENT_VALUES  eval
                          where   lmnt.element_entry_id = nval.element_entry_id
                          and     eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID          
                          order by lmnt.element_name)
            SELECT * FROM src
            PIVOT (max( input_value_id ) As input_value,
                   min( screen_entry_value ) as screen_entry_value
                   FOR (rn) IN (1,2,3,4,5)));

    l_input_value  varchar2(100);

begin

    for C_rec in C_cur loop
        l_input_value := C_rec."1_INPUT_VALUE"; -- !! Change here
    end loop;

end;

The other option (which I personally would favor) is to give the pivot list an alias which allows it to conform to the non-quoted identifier rules. For example:

declare

    cursor C_cur is
    select *
    from    (With src As (SELECT  lmnt.element_name
                              ,   lmnt.element_entry_id
                              ,   nval.input_value_id
                              ,   nval.screen_entry_value
                              ,   row_number() over (partition by lmnt.element_name, lmnt.element_entry_id
                                                     order by lmnt.element_name  ) rn
                          FROM    XX_SAMPLE_ELEMENTS lmnt
                              ,   XX_ENTRY_VALUES    nval
                              ,   XX_ELEMENT_VALUES  eval
                          where   lmnt.element_entry_id = nval.element_entry_id
                          and     eval.INPUT_VALUE_ID = nval.INPUT_VALUE_ID          
                          order by lmnt.element_name)
            SELECT * FROM src
            PIVOT (max( input_value_id ) As input_value,
                   min( screen_entry_value ) as screen_entry_value
                   FOR (rn) IN (1 AS N1,2 AS N2,3 AS N3,4 AS N4,5 AS N5))); -- !! Change here

    l_input_value  varchar2(100);

begin

    for C_rec in C_cur loop
        l_input_value := C_rec.N1_INPUT_VALUE; -- !! Change here
    end loop;

end;

Reference Pivoted Cursor in PL/SQL

1 Answers