Using SQL UDF

49 Views

As we may know SAP IQ lack INET datatype and inet_ntoa/inet_aton functions as well. I wrote small SQL function which does conversion:

CREATE OR REPLACE FUNCTION inet_ntoa (IN ipaddr UNSIGNED INT)

RETURNS CHAR(15)

DETERMINISTIC

BEGIN ATOMIC

DECLARE one,two,three,four TINYINT;

DECLARE RestOfIP UNSIGNED INT;

DECLARE ret CHAR(15);

SET one=ipaddr/POWER(2,24);

SET RestOfIP=ipaddr-one*POWER(2,24);

SET two=RestOfIP/POWER(2,16);

SET RestOfIP=RestOfIP-(two*POWER(2,16));

SET three=RestOfIP/POWER(2,8);

SET four=RestOfIP-(three*256);

SET ret=one || '.' || two || '.' || three || '.' || four;

RETURN (ret);

END

but when I use that in select like:

SELECT inet_ntoa(dstaddr) AS dstaddr FROM table - execution on 56k rows is 10sec. When I try to achieve same result but without using UDF (embed those calcs in SELECT itself) execution took only 1 sec. But SQL looks realy awful and long. Is there anything that could be done?

SELECT

CAST(tr.dstaddr/POWER(2,24) AS TINYINT) || '.' ||

CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT) || '.' ||

CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)/POWER(2,8) AS UNSIGNED INT) || '.' ||

CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)-256*CAST(CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)-CAST(CAST(tr.dstaddr-CAST(tr.dstaddr/POWER(2,24) AS TINYINT)*POWER(2,24) AS UNSIGNED INT)/POWER(2,16) AS UNSIGNED INT)*POWER(2,16) AS UNSIGNED INT)/POWER(2,8) AS UNSIGNED INT) AS UNSIGNED INT) as dstaddr FROM table