functions

[sri1961]

Can we define new functions ( similar to built-in , for example - trim ) for use in select statement.

If so can I have an example.

Thanks in advance
Sri

 

[olded]

Sri:

You can create stored procedures and call them from SQL:

Here's a sample stored procedure:

-- This procedure returns the number of seconds between datetime dt1 and dt2.
-- change to character first because the interval to integer conversion
-- fails. Ed Schaefer 09/23/98
CREATE PROCEDURE int_in_sec(dt1 DATETIME YEAR TO SECOND, dt2 DATETIME YEAR
TO SECOND) RETURNING INTEGER;
DEFINE int_sec INTERVAL SECOND(9) TO SECOND;
DEFINE xchar CHAR(20);
DEFINE xint INTEGER;

LET int_sec = dt2 - dt1; -- get the interval
LET xchar = int_sec; -- change to char
LET xint = xchar; -- change to integer

RETURN xint;
END PROCEDURE;

Here's a sample table:

create table sample_table
(
dt1 datetime year to second,
dt2 datetime year to second
);


with sample data:

insert into sample_table values ("2002-02-28 16:00:23", "2002-02-28 17:00:24&quot

and with the sample select:

select int_in_sec(dt1, dt2) from sample_table

returns the value: 3601

Regards,

Ed
Schaefer