| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Cursor problem
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);
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
)
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
NVL((SELECT freight_seal_tag
FROM s1_quantity_entry_freight_seal
WHERE sort_order = 2
AND
NVL((SELECT freight_seal_tag
FROM s1_quantity_entry_freight_seal
WHERE sort_order = 3
AND
NVL((SELECT freight_seal_tag
FROM s1_quantity_entry_freight_seal
WHERE sort_order = 4
AND
NVL((SELECT freight_seal_tag
FROM s1_quantity_entry_freight_seal
WHERE sort_order = 5
AND
NVL((SELECT freight_seal_tag
FROM s1_quantity_entry_freight_seal
WHERE sort_order = 6
AND
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
*
ORA-06550: line 36, column 18: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
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.
Received on Fri Jun 18 2004 - 10:14:02 CDT
 |
 |