Hi!

I have to write a sp, which

1. divides an interval (argument) in smaller intervals
2. calculates the avg-values for these intervals; the PK are timestamps
3. returning the values as resultset for processing in Java

1 and 2 are not worse, but 3 is difficult.

Thanks in advance, Michael

On Fri, 28 May 2004 12:32:06 +0200, Michael Drewitz wrote:

What's so difficult?

You just
select @avg1, @avg2, ...
for the local variables into which you've placed the calculated values.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

Michael Peppler wrote:

returned only a few values per query. And thats a really terrible design
for a Java-programmer.

Michael, too :-)

On Fri, 28 May 2004 14:29:06 +0200, Michael Drewitz wrote:

I guess you need to be more explicit in what you are trying to accomplish.
Maybe there's a way to write a single SQL statement/result set that
returns the required data...

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

Michael Drewitz wrote:

Possibly you could insert the results into a temp table and then select
the table out at the end of the sproc. That way you'd be able to handle
any number of output calculations.

Would that satisfy a Java Programmer?

Adam

Michael Peppler wrote:
[snip]
I would like to do the following (sorry for the maybe wrong syntax, its
not my SP):
begin
--arbitrary values, handed over as arguments
declare @start_ timestamp;
declare @end_ timestamp;
--smaller intervals
declare @interval_begin timestamp;
declare @interval_end timestamp;

declare @timediff int;
set @timediff= datediff(@start_, @end_);
set @interval_begin=@start;
set @interval_end = dateadd(day, @interval_begin);
--how fill this table and retrieving the content?
declare local temporary table temp_values;

while @timediff > 0 LOOP
select avg(fieldName) into temp_values where datediff(@interval_begin,
fieldname) > 0 and datediff(@interval_end, fieldname) < 0;
-- setting @interval_begin = @interval_end and increasing @interval_end
set @timediff = datediff(@interval_begin, @end_);
END LOOP

select * from temp_values; --(?)
end

another question: Is it possible to apply a handed over varchar or
string in the select expression?

Hope it's clear, Michael

Adam H wrote:
[snip]

-- The dialect is Transact-SQL ?
declare local temporary table temp_values(avg_values double);
...
select avg(fieldname) into #temp_values from tablename where ..;

Then I get the error 'item #temp_values already exists'
I posted some pseudo code in another message.
Michael

"Michael Drewitz" <midrmatrix (AT) web (DOT) de> wrote


Ummm, is this ASA or ASE? And which release?

Michael Drewitz wrote:
Since you're getting an error (ie, you're actually submitting something to
the dataserver) could you post the actual (not pseudo) code that you're
working with?

What you want to do (insert values into a temp table and then select all
values as one result set) *IS* doable and fairly straight forward ... but
at this point I can't tell if you're getting errors because of 'syntax' or
'logic' issues. It would be a lot easier to work on this in one languange
(T-SQL as opposed to pseudo-code).

--
Mark A. Parsons

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

On Fri, 28 May 2004 17:26:36 +0200, Michael Drewitz wrote:

<snip>

OK - this could look something like this:

create proc calc_avg
@start datetime
, @end datetime
as
declare @interval_begin datetime, @interval_end datetime
, @timediff int

select @timediff = datediff(ss, @start, @end)
select @interval_begin=@start
select @interval_end = dateadd(day, @interval_begin)
create table #tmp(value float) -- don't know if it's a float here?

while @timediff > 0
begin
insert #tmp (value)
select avg(fieldName)
where datediff(ss, @interval_begin, fieldname) > 0
and datediff(ss, @interval_end, fieldname) < 0

select @timediff = datediff(ss, @interval_begin, @end)
end

select value from #tmp

go

You may want to use different units for the datediff() calls (I used
seconds), and maybe you need to add additional columns to the #tmp table.

How do you mean?


Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html