Hi,

I have the following

BEGIN
<>
FOR r_idh IN (SELECT instrumentId, lastSalePrice from tibex_instrumentIndexMap) LOOP
v_lastTradePrice:=NULL;
v_currentDate:=trunc(to_date('20030120','YYYYMMDD'));
v_currentDateString:=to_char(v_currentDate,'YYYYMMDD');
-- v_currentDate:=trunc(sysdate)
<>
WHILE v_lastTradePrice IS NULL LOOP
v_startOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'000000');
v_endOfDay:=eod_epoch.date_to_epoch(v_currentDateString,'235959');
<>
FOR r_ex IN c_latestBookTrade(r_idh.instrumentId) LOOP
v_lastTradePrice:=r_ex.price;
EXIT Latest_BookTrade;
END LOOP Latest_BookTrade;
IF v_lastTradePrice IS NULL THEN
v_currentDate:=v_currentDate -1;
EXIT daily_loop WHEN v_currentDate < k_startDate;
END IF;
END LOOP daily_loop;

IF (v_lastTradeprice IS NULL) THEN
dbms_output.put_line ('Untraded Instrument ' || r_idh.instrumentId);
ELSIF
v_lastTradeprice != r_idh.lastSalePrice THEN
dbms_output.put_line ('HAwk Alert ' || r_idh.instrumentId);
END IF;
END LOOP Instrument_Loop;
END;
/


The v_currentDate variable should be set to trunc(sysdate) and when it goes into the loop it should go backwards day by day until it finds a dbms_output.put_line ('HAwk Alert ' || r_idh.instrumentId);

It seems to pick out the correct info when you hard code the date into
v_currentDate, but it doesn't seem to be working in the loop which goes back one day at a time. when v_currentdate is set to sydate which is ideally what I want. when it is set to sysdate it only justifies the dbms_output.put_line ('Untraded Instrument ' || r_idh.instrumentId) which I know is wrong!

Hope this is clear and any shedding of light would be much appreciated..


Thanks in advance