Hi,
I've got a form that allows users to select values that will be used for the IN clause of a select Statement...
Running Oracle forms 11g.
I'm populating a field with a string made up of user selected values, so it looks something like 'REFRIGIRATION','DANGEROUS GOODS'. Each value that the user select is tagged to the string between single quotes.
This is the code used to build the STRING:
IF :cb_tempvar.v_ancillary IS NULL
THEN
:cb_tempvar.v_ancillary := :lbl_pcs.ancillary_name;
ELSE
:cb_tempvar.v_ancillary := ''''||:cb_tempvar.v_ancillary||''' ,'''||:lbl_pcs.ancillary_name||'''';
END IF;
RESULTS:
'DANGEROUS GOODS','REFRIGIRATION'
NOW, when the string (:cb_tempvar.v_ancillary) is built I go to my PROC:
PROCEDURE CALCULATE_ANCILLARY_SURCHRG IS
total_chrg NUMBER;
Cursor c_anc (v_list varchar2) IS
SELECT c.flat_charge, c.per_weight_charge, c.percent
FROM csl_anc c
WHERE csl_id = :v_shp_wsbl.csl_id
AND calculate_on_wsbl = 1
AND ancillary_name IN (v_list);
BEGIN
total_chrg := 0;
FOR anc_rec in c_anc(:cb_tempvar.v_ancillary)
LOOP
IF anc_rec.flat_charge is NOT NULL
THEN
total_chrg := total_chrg + anc_rec.flat_charge;
ELSIF anc_rec.per_weight_charge is NOT NULL
THEN
-- Do the formula to calculate Per Weight Charge
NULL;
ELSIF anc_rec.percent is NOT NULL
THEN
-- Do the formula to calculate Percent Charge
NULL;
END IF;
END LOOP;
:v_shp_wsbl.total_surcharges := total_chrg;
END;
But the query does not return any DATA (no errors). If the string has only one value ('REFRIGIRATION'), the query brings back data !!!
Am I building my string wrong?
Please help,
Marc L