Re: Cursor problem

On Fri, 18 Jun 2004 11:14:02 -0400, "Murray Sobol" <murray_sobol_at_dbcsmartsoftware.com> wrote:

>I am trying to compile the following code, but get the following error (see
>below):
>
>Here is the query:
>
>DECLARE
> v_freight1 VARCHAR2(20);
> v_freight2 VARCHAR2(20);
> v_freight3 VARCHAR2(20);
> v_freight4 VARCHAR2(20);
> v_freight5 VARCHAR2(20);
> v_freight6 VARCHAR2(20);
> v_bol VARCHAR2(20);
> dt_bol_date DATE;
> v_carrier VARCHAR2(10);
> c_flag CHAR(1);
> d_rate NUMBER(18,4);
> v_uom VARCHAR2(10);
> i_agn NUMBER;
> v_io CHAR(1);
> i_group_nbr NUMBER;
>
>CURSOR curs_group IS
> SELECT DISTINCT qe.bill_of_lading,
> qe.bill_of_lading_date,
> qe.freight_carrier_id,
> qe.rate_per_ticket_flag,
> qe.freight_charge_rate,
> qe.freight_charge_uom_code,
> fs.freight1,
> fs.freight2,
> fs.freight3,
> fs.freight4,
> fs.freight5,
> fs.freight6,
> qe.scale_ticket_inbound_outbound,
> qea.group_nbr
> FROM s1_quantity_entry qe,
> s1_quantity_entry_accrual qea,
> (SELECT qe.quantity_entry_nbr,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 1
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight1,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 2
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight2,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 3
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight3,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 4
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight4,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 5
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight5,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 6
> AND s1_quantity_entry_freight_seal.quantity_entry_nbr =
>s1_quantity_entry.quantity_entry_nbr),'-1') AS freight6
> FROM s1_quantity_entry
> WHERE qe.quantity_entry_source = 'IE'
> ) AS fs
> WHERE qe.quantity_entry_source = 'IE'
> AND qe.quantity_entry_nbr = fs.quantity_entry_nbr
> AND qe.bill_of_lading IS NOT NULL
> AND qe.freight_carrier_id IS NOT NULL
> AND qe.quantity_entry_nbr = qea.quantity_entry_nbr(+)
> AND ((qe.freight_charge_rate IS NOT NULL) OR
> (fs.freight1 <> '-1') OR
> (fs.freight2 <> '-1') OR
> (fs.freight3 <> '-1') OR
> (fs.freight4 <> '-1') OR
> (fs.freight5 <> '-1') OR
> (fs.freight6 <> '-1') OR
> qea.group_nbr IS NOT NULL
> )
> ORDER BY 1,2,3,4,5,6,7;
>
>BEGIN
>
>OPEN curs_group;
>
> LOOP
> FETCH curs_group INTO v_bol,
> dt_bol_date,
> v_carrier,
> c_flag,
> d_rate,
> v_uom,
> v_freight1,
> v_freight2,
> v_freight3,
> v_freight4,
> v_freight5,
> v_freight6,
> v_io,
> i_group_nbr;
>
> EXIT WHEN curs_group%NOTFOUND;
>
> SELECT accrual_group_nbr + 1
> INTO i_agn
> FROM s1_max_key;
>
> UPDATE s1_max_key
> SET accrual_group_nbr = i_agn;
>
> UPDATE s1_quantity_entry
> SET freight_group_nbr = i_agn
> WHERE quantity_entry_nbr IN
> (SELECT qe.quantity_entry_nbr
> FROM s1_quantity_entry qe,
> (SELECT qe.quantity_entry_nbr,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 1
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight1,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 2
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight2,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 3
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight3,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 4
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight4,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 5
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight5,
> NVL((SELECT freight_seal_tag
> FROM s1_quantity_entry_freight_seal
> WHERE sort_order = 6
> AND
>s1_quantity_entry_freight_seal.quantity_entry_nbr =
>qe.quantity_entry_nbr),'-1') AS freight6
> FROM s1_quantity_entry qe
> WHERE qe.quantity_entry_source = 'IE'
> ) AS fs
> WHERE qe.quantity_entry_nbr = fs.quantity_entry_nbr
> AND qe.quantity_entry_source = 'IE'
> AND qe.bill_of_lading IS NOT NULL
> AND qe.freight_carrier_id IS NOT NULL
> AND ((i_group_nbr IS NULL
> AND NOT EXISTS (SELECT 1
> FROM s1_quantity_entry_accrual
> WHERE
>s1_quantity_entry_accrual.quantity_entry_nbr = qe.quantity_entry_nbr
> )
> ) OR (EXISTS (SELECT 1
> FROM s1_quantity_entry_accrual
> WHERE
>s1_quantity_entry_accrual.quantity_entry_nbr = qe.quantity_entry_nbr
> AND
>s1_quantity_entry_accrual.group_nbr = i_group_nbr
> )
> )
> )
> AND ((qe.freight_charge_rate IS NOT NULL) OR
> (fs.freight1 <> '-1') OR
> (fs.freight2 <> '-1') OR
> (fs.freight3 <> '-1') OR
> (fs.freight4 <> '-1') OR
> (fs.freight5 <> '-1') OR
> (fs.freight6 <> '-1') OR
> i_group_nbr IS NOT NULL
> )
> AND qe.bill_of_lading = v_bol
> AND qe.freight_carrier_id = v_carrier
> AND (dt_bol_date IS NULL OR qe.bill_of_lading_date = dt_bol_date)
> AND qe.rate_per_ticket_flag = c_flag
> AND (d_rate IS NULL OR qe.freight_charge_rate = d_rate)
> AND (v_uom IS NULL OR qe.freight_charge_uom_code = v_uom)
> AND (v_freight1 = '-1' OR fs.freight1 = v_freight1)
> AND (v_freight2 = '-1' OR fs.freight2 = v_freight2)
> AND (v_freight3 = '-1' OR fs.freight3 = v_freight3)
> AND (v_freight4 = '-1' OR fs.freight4 = v_freight4)
> AND (v_freight5 = '-1' OR fs.freight5 = v_freight5)
> AND (v_freight6 = '-1' OR fs.freight6 = v_freight6)
> AND (qe.scale_ticket_inbound_outbound = v_io)
> );
>
> END LOOP;
>
>CLOSE curs_group;
>
>END;
>/
>
>Here is the error:
>
> NVL((SELECT freight_seal_tag
> *
>ERROR at line 36:
>ORA-06550: line 36, column 18:
>PLS-00103: Encountered the symbol "SELECT" when expecting one of the
>following:
>( - + mod not null others <an identifier>
><a double-quoted delimited-identifier> <a bind variable> avg
>count current exists max min prior sql stddev sum variance
>execute forall time timestamp interval date
><a string literal with character set specification>
><a number> <a single-quoted SQL string>
>ORA-06550: line 39, column 113:
>PLS-00103: Encountered the symbol "," when expecting one of the following:
>; return returning and or
>
>If I am reading the above error correctly, it seems to object to the inline
>view in the cursor.
>
>Any suggestions??
>
>Thanks
>Murray Sobol
>dbcSMARTsoftware inc.
>

sure. Several

1 always post your version
2 always post your version
3 always post your version

Verify the syntax of the UPDATE statement. I have the distinct feeling you can't use inline views at the right hand of a SET clause Usually, if you want this, you should code set (<col1>,<col2>) =
(select ....
 from
)
so a proper subquery instead of an inline view.

--
Sybrand Bakker, Senior Oracle DBA