Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: DB2 Functions : How can I use untyped parameters ?

    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
    Last edited by DBFinder; 01-23-10 at 12:04. Reason: specify version

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 01-25-10 at 10:27. Reason: Specified schema db2admin. explicitly in create function statement.

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •