467,185 Members | 1,214 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

converting Oracle PL/SQL function to DB2 v9

I am a newbie and really needs some assistance. I have struggled with this code. Any assistantance will be very appreciated. The code is below....

CREATE FUNCTION increment_serial_1 (p_serial_num VARCHAR(4000) )
RETURNS VARCHAR(4000)
LANGUAGE SQL

BEGIN ATOMIC

DECLARE l_pattern VARCHAR(30) DEFAULT '[A-Z]';

--| l_components OWA_TEXT.VC_ARR;

--* [200042]"C:\MTK\projects\Unknown1\incrementnm.src"(29: 16)-(29:30)Unrecognized data type: OWA_TEXT.VC_ARR

DECLARE l_components UNKNOWN;

DECLARE l_serial_num_identif VARCHAR(7);

SET l_serial_num_identif = p_serial_num;

IF l_serial_num_identif = '8999999' THEN
SET l_serial_num_identif = 'A000001';

ELSEIF l_serial_num_identif = 'A999999' THEN
SET l_serial_num_identif = 'D000001';

ELSEIF l_serial_num_identif = 'D999999' THEN
SET l_serial_num_identif = 'G000000';

ELSEIF l_serial_num_identif = 'G999999' THEN
SET l_serial_num_identif = 'J000000';

ELSEIF l_serial_num_identif = 'J999999' THEN
SET l_serial_num_identif = 'M000000';

ELSEIF l_serial_num_identif = 'M999999' THEN
SET l_serial_num_identif = 'P000001';

ELSEIF l_serial_num_identif = 'P999999' THEN
SET l_serial_num_identif = 'T000000';

--| ELSIF OWA_PATTERN.MATCH(l_serial_num_identif, l_pattern, l_components)
--| THEN

--* [200011]"C:\MTK\projects\Unknown1\incrementnm.src"(57: 9)-(57:25)Reference to unknown object: OWA_PATTERN.MATCH
--* [200011]"C:\MTK\projects\Unknown1\incrementnm.src"(57: 60)-(57:71)Reference to unknown object: l_components

ELSEIF untranslated predicate. THEN

SET l_serial_num_identif = ORA.EMPTY_TO_NULL(COALESCE(SUBSTR(l_serial_num_ide ntif, 1, 1), '') || COALESCE(ORA.LPAD(ORA.TO_CHAR(ORA.TO_NUMBER(SUBSTR (l_serial_num_identif, 2)) + 1), 6, '0'), ''));

ELSE
SET l_serial_num_identif = ORA.LPAD(ORA.TO_CHAR(ORA.TO_NUMBER(l_serial_num_id entif) + 1), 7, '0');

END IF;

RETURN l_serial_num_identif;

END!
Aug 17 '07 #1
  • viewed: 1578
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Mark Wilson CPU | last post: by
3 posts views Thread by Jevon | last post: by
4 posts views Thread by --CELKO-- | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.