DB2 Functions : How can I use untyped parameters ?

DBFinder · #1 (**permalink**) 01-23-10, 11:03

Hi Guys,

I have mixed env at workplace. Few servers including DEV and STG servers are below Ver 9.5. I need to implement STRIP function.
How can I specify that the middle parameteris UN-TYPED. ?

STRIP('00002305.007',LEADING,'0')

I need to learn how implement LEADING. For example, if I specify it as CHAR or VARCHAR then user will have to specify it as 'LEADING'.

DB2 V8.5,V9.1,V9.5 WSE on Win2K3

Help appreciated.

DBFinder

n_i · #2 (**permalink**) 01-23-10, 22:11

I think what you mean is an enumeration type, not an "untyped" parameter. I don't think DB2 supports enumeration types. Your best bet would be to use character or integer codes.

DBFinder · #3 (**permalink**) 01-25-10, 05:13

Thanks Nick,

So that means I cannot simulate same function in older versions. e.g. STRIP(CHAR(12.45),LEADING,'0')

Is there any other way ?

regards

DBFinder

tonkuma · #4 (**permalink**) 01-25-10, 09:11

A workqround used in the old days was to create a table with one row data,
then join it in a query, like this...
CREATE TABLE constants
(Yes CHAR(1)
,No CHAR(1)
,On INTEGER
,Off INTEGER
,LEADING CHAR(1)
,TRAILING CHAR(1)
);

INSERT INTO constants
VALUES ('Y', 'N', 1, 0, 'L', 'T');

SELECT STRIP(CHAR(12.45),LEADING,'0')
FROM constants;

tonkuma · #5 (**permalink**) 01-25-10, 09:25

An example of use of this tecnique.....
Create UDF:

Code:

------------------------------ Commands Entered ------------------------------
CREATE FUNCTION db2admin.STRIP (STR VarChar(4000), BLT VarChar(8), SC VarChar(1))
 RETURNS VARCHAR(4000)
 SPECIFIC STRIP_V2_3P
 LANGUAGE SQL
 CONTAINS SQL
 NO EXTERNAL ACTION
 DETERMINISTIC
RETURN 
CASE upper(substr(BLT,1,1))
WHEN 'T' THEN
   translate(rtrim(translate(Str, ' ' || SC, SC || ' ')), ' ' || SC, SC || ' ')
WHEN 'L' THEN
   translate(ltrim(translate(Str, ' ' || SC, SC || ' ')), ' ' || SC, SC || ' ')
WHEN 'B' THEN
   translate(ltrim(rtrim(translate(Str, ' ' || SC, SC || ' '))), ' ' || SC, SC || ' ')
END;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

Error usage of the function:

Code:

------------------------------ Commands Entered ------------------------------
SELECT db2admin.STRIP(CHAR(12.45),LEADING,'0')
  FROM sysibm.sysdummy1;
------------------------------------------------------------------------------
SQL0206N  "LEADING" is not valid in the context where it is used.  
SQLSTATE=42703

Succesful:

Code:

------------------------------ Commands Entered ------------------------------
SELECT db2admin.STRIP(CHAR(12.45),'LEADING','0')
  FROM sysibm.sysdummy1;
------------------------------------------------------------------------------

1                   
--------------------
12.45               

  1 record(s) selected.

Succesful, too:

Code:

------------------------------ Commands Entered ------------------------------
SELECT db2admin.STRIP(CHAR(12.45),LEADING,'0')
  FROM constants;
------------------------------------------------------------------------------

1                   
--------------------
12.45               

  1 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT db2admin.STRIP(CHAR(12.45),LEADING,'0')
  FROM sysibm.sysdummy1
     , constants;
------------------------------------------------------------------------------

1                   
--------------------
12.45               

  1 record(s) selected.

DBFinder · #6 (**permalink**) 01-25-10, 09:31

Great work tonkuma,

Really good trick, I will save it for use.

However I meant to provide an interface for programmers ( our dev team) to write SQL for v9.5 even though they have v8.5 on one of their staging server. The 'from constants' clause won't be there in their standard SQL.

Regards

Internet Services

Web Development

Digital Marketing

Consumer Tech