Hi,

I am new to informix, I need to convert an existing SQL Server stored procedure to its equivalent in Informix 9.21.

CREATE FUNCTION dbo.FN_Get_Order_Freq (@custid nchar(5))
RETURNS decimal(18,4) AS
BEGIN
DECLARE @retval decimal(18, 4)
DECLARE @prevdate datetime
DECLARE @currdate datetime
DECLARE @orders int
DECLARE @days int
DECLARE calc_freq_cursor CURSOR FOR
SELECT orderdate
FROM dbo.Orders
WHERE CustomerId = @custid
ORDER BY 1
SELECT @retval = 0
SELECT @prevdate = '1/1/2048'
SELECT @days = 0
SELECT @orders = 0
OPEN calc_freq_cursor
-- Perform the first fetch and store the values in variables.
FETCH NEXT FROM calc_freq_cursor
INTO @currdate
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATEDIFF(day, @prevdate, @currdate) > 0
BEGIN
SELECT @days = @days + DATEDIFF(day, @prevdate, @currdate)
SELECT @orders = @orders + 1
END
ELSE
BEGIN
SELECT @prevdate = @currdate
END
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM calc_freq_cursor
INTO @currdate
END
CLOSE calc_freq_cursor
DEALLOCATE calc_freq_cursor
IF @days > 0
SELECT @retval = @days / @orders
RETURN @retval

Any help greatly appreciated.

thanks,
sysman20