Re: Oracle Analytical Functions

Found the error:

client_id := thisrow.datetime;

should be

client_id := thisrow.client_id;

All works well now,

Thanks very much,

Willem

Willem Buitendyk wrote:
> I tried this function but it keeps returning an error such as:
>
> ERROR: invalid input syntax for integer: "2007-05-05 00:34:08"
> SQL state: 22P02
> Context: PL/pgSQL function "lagfunc" line 10 at assignment
>
> I checked and there are no datetime values in the client_id field
> anywhere in my table 'all_client_times'
>
> I have no idea what is going on here ...
>
> Thanks for the code though - it has taught me a lot all ready; such as
> using, OUT and SETOF Record
>
> Willem
>
> Adam Rich wrote:
>>> and I would like to create a new view that takes the first table and
>>> calculates the time difference in minutes between each row so that the
>>> result is something like:
>>>
>>> client_id,datetime, previousTime, difftime
>>> 122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
>>> 455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1
>>> 455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5
>>> 299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24
>>>
>>> Any idea how I could replicate this in SQL from PG. Would this be an
>>> easy thing to do in Pl/pgSQL? If so could anyone give any directions
>>> as to where to start?
>>>
>>
>> You can create a set-returning function, that cursors over the table,
>> like this:
>>
>>
>> CREATE OR REPLACE FUNCTION lagfunc(
>> OUT client_id INT, OUT datetime timestamp, OUT
>> previousTime timestamp, OUT difftime interval)
>> RETURNS SETOF RECORD as $$ DECLARE
>> thisrow RECORD;
>> last_client_id INT;
>> last_datetime timestamp;
>> BEGIN
>>
>> FOR thisrow IN SELECT * FROM all_client_times ORDER BY client_id,
>> datetime LOOP
>> IF thisrow.client_id = last_client_id THEN
>> client_id := thisrow.datetime;
>> datetime := thisrow.datetime;
>> previousTime := last_datetime;
>> difftime = datetime-previousTime;
>> RETURN NEXT;
>> END IF;
>> last_client_id := thisrow.client_id;
>> last_datetime := thisrow.datetime;
>> END LOOP;
>>
>> RETURN;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> select * from lagfunc() limit 10;
>> select * from lagfunc() where client_id = 455;
>>
>>
>> Here I used an interval, but you get the idea.
>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>