Subject | Stored Proc |
---|---|
Author | skotaylor |
Post date | 2003-09-26T15:39:15Z |
Can anyone see my obvious mistake in this SP?
Running the query portion in ISQL returns this data:
09/24/2003, 4.98
09/16/2003, 6.38
09/16/2003, 6.38
09/17/2003, 10.24
09/25/2003, 11.14
etc...
but when I run the SP via "SELECT * FROM drv_hours(:DrvID)" it returns
null for all three values. I've been trying everything but the right
thing.
<pre>
ALTER PROCEDURE DRV_HOURS( DRVID VARCHAR( 4 ) )
RETURNS ( CYCLE7 DECIMAL ( 9, 2 )
, CYCLE8 DECIMAL ( 9, 2 )
, CYCLE14 DECIMAL ( 9, 2 ) )
AS
declare variable DDate Date;
declare variable CTime decimal ( 9, 2);
BEGIN
FOR SELECT d.dumpdate, h.cycletime
FROM disp_leg d
join trk_hauls h on h.haulid = d.haulid
WHERE dumped = 'Y'
AND drvid = :DrvID
AND NOT d.HaulID is null
AND dumpdate between (current_date - 14) and current_date
into :DDate, :CTime
do
begin
CYCLE14 = CYCLE14 + :CTime;
if (DDate >= (Current_Date - 8 )) then
CYCLE8 = CYCLE8 + :CTime;
if (DDate >= (Current_Date - 7 )) then
CYCLE7 = CYCLE7 + :CTime;
end
SUSPEND;
END
</pre>
Cheers.
Scott.
Running the query portion in ISQL returns this data:
09/24/2003, 4.98
09/16/2003, 6.38
09/16/2003, 6.38
09/17/2003, 10.24
09/25/2003, 11.14
etc...
but when I run the SP via "SELECT * FROM drv_hours(:DrvID)" it returns
null for all three values. I've been trying everything but the right
thing.
<pre>
ALTER PROCEDURE DRV_HOURS( DRVID VARCHAR( 4 ) )
RETURNS ( CYCLE7 DECIMAL ( 9, 2 )
, CYCLE8 DECIMAL ( 9, 2 )
, CYCLE14 DECIMAL ( 9, 2 ) )
AS
declare variable DDate Date;
declare variable CTime decimal ( 9, 2);
BEGIN
FOR SELECT d.dumpdate, h.cycletime
FROM disp_leg d
join trk_hauls h on h.haulid = d.haulid
WHERE dumped = 'Y'
AND drvid = :DrvID
AND NOT d.HaulID is null
AND dumpdate between (current_date - 14) and current_date
into :DDate, :CTime
do
begin
CYCLE14 = CYCLE14 + :CTime;
if (DDate >= (Current_Date - 8 )) then
CYCLE8 = CYCLE8 + :CTime;
if (DDate >= (Current_Date - 7 )) then
CYCLE7 = CYCLE7 + :CTime;
end
SUSPEND;
END
</pre>
Cheers.
Scott.