I have a cursor at present say
Cursor c1_cur is
SELECT fuel_meter_id
fuel_serial
fuel_operation
FROM fuel_tab
WHERE p_effective_from_date BETWEEN this_date AND (that_date + 1/86400)
AND fuel_meter_id = p_fuel_d
AND fuel_serial = p_serial;
v_c1_rec c1_cur%ROWTYPE;
/* above cursor at present retrieves
fuel_meter_id fuel_serial effective_from_date operation
5432 121 01-APR-1996 00:00:00 +
5432 121 05-MAR-2002 00:00:00 + */
BEGIN
OPEN c1
FETCH c1 into v_c1_rec;
IF c1_cur%NOTFOUND then
message('blablabal');
END IF;
LOOP
IF v_c1_rec.operation = '+' then
v_fuel_regi := v_fuel_regi + v_fuel_advance; --
--(declared and assigned 0 further up somewhere)
END IF;
process next fuel
FETCH c1_cur into v_c1_rec;
EXIT WHEN c1_cur%NOTFOUND;
close the above procedure
The cursor picks up two rows with same fuel_id and serial no although effect_from_date is different. I want to place some code which causes the loop to be exited when it sees rows which have similar id's(and serial's). Also at the moment the v_fuel_regi is ending up with a double value ( i.e should be 2 but result of above code makes it 4)
Will the following achieve this : -
Cursor c1_cur is
SELECT fuel_meter_id
fuel_serial
fuel_operation
FROM fuel_tab
WHERE p_effective_from_date BETWEEN this_date AND (that_date + 1/86400)
AND fuel_meter_id = p_fuel_d
AND fuel_serial = p_serial;
v_c1_rec c1_cur%ROWTYPE;
v_duplicate_fuel_id := 'FALSE'; -- new line
v_saved_fuel_id fuel_tab.fuel_id%TYPE: -- new line
BEGIN
OPEN c1
FETCH c1 into v_c1_rec;
IF c1_cur%NOTFOUND then
message('blablabal');
END IF;
LOOP
IF v_c1_rec.operation = '+' then
v_fuel_regi := v_fuel_regi + v_fuel_advance; --
--(declared and assigned 0 further up somewhere)
END IF;
process next fuel
FETCH c1_cur into v_c1_rec;
IF v_c1_rec.fuel_id = v_saved_fuel_id then --
v_duplicate_fuel_id := 'TRUE'; --
END IF;
EXIT WHEN c1_cur%NOTFOUND OR v_dupliacte_fuel_id = 'TRUE';
close the above procedure
Can anyone help???
Cursor c1_cur is
SELECT fuel_meter_id
fuel_serial
fuel_operation
FROM fuel_tab
WHERE p_effective_from_date BETWEEN this_date AND (that_date + 1/86400)
AND fuel_meter_id = p_fuel_d
AND fuel_serial = p_serial;
v_c1_rec c1_cur%ROWTYPE;
/* above cursor at present retrieves
fuel_meter_id fuel_serial effective_from_date operation
5432 121 01-APR-1996 00:00:00 +
5432 121 05-MAR-2002 00:00:00 + */
BEGIN
OPEN c1
FETCH c1 into v_c1_rec;
IF c1_cur%NOTFOUND then
message('blablabal');
END IF;
LOOP
IF v_c1_rec.operation = '+' then
v_fuel_regi := v_fuel_regi + v_fuel_advance; --
--(declared and assigned 0 further up somewhere)
END IF;
process next fuel
FETCH c1_cur into v_c1_rec;
EXIT WHEN c1_cur%NOTFOUND;
close the above procedure
The cursor picks up two rows with same fuel_id and serial no although effect_from_date is different. I want to place some code which causes the loop to be exited when it sees rows which have similar id's(and serial's). Also at the moment the v_fuel_regi is ending up with a double value ( i.e should be 2 but result of above code makes it 4)
Will the following achieve this : -
Cursor c1_cur is
SELECT fuel_meter_id
fuel_serial
fuel_operation
FROM fuel_tab
WHERE p_effective_from_date BETWEEN this_date AND (that_date + 1/86400)
AND fuel_meter_id = p_fuel_d
AND fuel_serial = p_serial;
v_c1_rec c1_cur%ROWTYPE;
v_duplicate_fuel_id := 'FALSE'; -- new line
v_saved_fuel_id fuel_tab.fuel_id%TYPE: -- new line
BEGIN
OPEN c1
FETCH c1 into v_c1_rec;
IF c1_cur%NOTFOUND then
message('blablabal');
END IF;
LOOP
IF v_c1_rec.operation = '+' then
v_fuel_regi := v_fuel_regi + v_fuel_advance; --
--(declared and assigned 0 further up somewhere)
END IF;
process next fuel
FETCH c1_cur into v_c1_rec;
IF v_c1_rec.fuel_id = v_saved_fuel_id then --
v_duplicate_fuel_id := 'TRUE'; --
END IF;
EXIT WHEN c1_cur%NOTFOUND OR v_dupliacte_fuel_id = 'TRUE';
close the above procedure
Can anyone help???