Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: Troubles with LPAD

    What am I missing here?? I want to pad with zeroes the week number column for the week numbers that are in the single digit e.g. "week number 01" instead of "week number 1" (DB2 v9 LUW).

    db2 "SELECT WEEK_NUM, LPAD ( DIGITS(WEEK_NUM),2,'0' ) FROM MONTHDAY_DIM "

    SQL0440N No authorized routine named "LPAD" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

    db2 describe table monthday_dim

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    MONTHDAY_KEY SYSIBM TIMESTAMP 10 0 Yes
    MONTHDAY_DATE SYSIBM TIMESTAMP 10 0 Yes
    WEEK_END_DATE SYSIBM TIMESTAMP 10 0 Yes
    YEAR_NUM SYSIBM INTEGER 4 0 Yes
    MONTH_NUM SYSIBM INTEGER 4 0 Yes
    MONTH_NAME SYSIBM VARCHAR 10 0 Yes
    WEEK_NUM SYSIBM INTEGER 4 0 Yes
    MONTH_OVERALL_NUM SYSIBM INTEGER 4 0 Yes
    WEEKDAY_NUM SYSIBM INTEGER 4 0 Yes
    WEEKDAY_NAME SYSIBM VARCHAR 9 0 Yes
    WEEK_OVERALL_NUM SYSIBM INTEGER 4 0 Yes
    ……………..

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LPAD function is supported from DB2 9.7 for LUW, according to manuals.

    By the way,
    DIGITS returns with leading zeroes.
    LPAD might be not applicable for your requirements.

    Try RIGHT or SUBSTR funcion, like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     MONTHDAY_DIM
    ( week_num ) AS (
    VALUES 
       1
    ,  2
    , 13
    )
    SELECT week_num
         , DIGITS(week_num)                 AS DIGITS_week_num
         , RIGHT(DIGITS(week_num) , 2)      AS RIGHT_week_num
         , SUBSTR(DIGITS(week_num) , 9 , 2) AS SUBSTR_week_num
     FROM  MONTHDAY_DIM
    ;
    ------------------------------------------------------------------------------
    
    WEEK_NUM    DIGITS_WEEK_NUM RIGHT_WEEK_NUM SUBSTR_WEEK_NUM
    ----------- --------------- -------------- ---------------
              1 0000000001      01             01             
              2 0000000002      02             02             
             13 0000000013      13             13             
    
      3 record(s) selected.
    Note 1: Tested on DB2 9.7 for LUW.
    Note 2: RIGHT returns VARCHAR(4000) on DB2 9 for LUW.
    Last edited by tonkuma; 04-26-13 at 22:42. Reason: Add Note 1 and 2.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I suggest you check the manual for functions that are available in DB2 9.1 for LUW (if that's what you meant by DB2 v9).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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