Mark A. Parsons wrote:

call dba.avg_temperature_values('2004-05-27 09:00:00', '2004-06-27
10:00:00', 5) I get an empty result set.
It seems to be the query in the insert expression because

select avg(fldtemperature) from tblrawdata where
datediff(second, @interval_begin, fldtemperature) > 0

executed on the console causes the error
'ASA error 157: cannot convert 13 to a date'.
If I uncomment the following message, the SP itself causes the same
error. Curious.


CREATE PROCEDURE DBA."avg_temperature_values" @start_time timestamp,
@end_time timestamp, @datepart int
AS
declare @interval_begin timestamp, @interval_end timestamp, @timediff
int, @current_value float
select @timediff = datediff(second, @start_time, @end_time)
select @interval_begin = @start_time

if @datepart = 1 select @interval_end = dateadd(year, 1, interval_begin)
if @datepart = 5 select @interval_end = dateadd(day, 1, @interval_begin)

create table #temp_values(avg_values float)

while @timediff > 0
begin
insert #temp_values(avg_values) select avg(fldtemperature) from
tblrawdata where
datediff(second, @interval_begin, fldtemperature) > 0 and
datediff(second, @interval_end, fldtemperature) < 0
and fldtemperature > -999

-- the message is for debugging purposes but causes an error 'cannot
convert 13 to a date'
--message '#temp_values: ' || (select * from #temp_values) to client

select @timediff = datediff(second, @interval_begin, @end_time)
select @interval_begin = @interval_end

if @datepart = 1 select @interval_end = dateadd(year, 1,
@interval_begin)
if @datepart = 2 select @interval_end = dateadd(month, 1,
@interval_begin)
end

select avg_values from #temp_values

Thanks in advance, Michael

Michael Drewitz wrote:

I work with ASE, not ASA, so FWIW ...

What is the value of 'fldtemperature'? The datediff() function is
expecting the 2nd and 3rd arguments to be valid dates. My guess, without
seeing the actual data, is that the fldtemperature value is not a valid
date.

Not sure if this was a typo ... but you should replace 'interval_begin'
with '@interval_begin' (above).

Again, sounds like fldtemperature is not a valid date ... the name of this
column doesn't look/sound like it stores date values ... and the fact that
you're testing the column for a value '> -999' would appear to confirm my
suspicion.

Are you sure you want to be testing a date value against what appears to be
a temperature (ie, non-date) value?

----------------------

If this still isn't helping you I'd suggest you pop over to the
sybase.public.sqlanywhere.* newsgroups (news server = forums.sybase.com).
Those are ASA-specific groups where you could get more help (assuming the
issue is ASA syntax).

--
Mark A. Parsons

Iron Horse, Inc.
iron_horse (AT) NOSPAM (DOT) compuserve.com