Db2

Db2

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  DB2 query

    Posted Wed February 19, 2020 05:46 AM
    Hi All,

    I have one field with in DB2 View and that is character datatype. That field is having 3 kind of formats

    1. 4MAR17
    2. 12367891217
    3. 0618

    I want a calculated field pointing to the above field and get output as week and year combination (WWYY).

    Referring to above point #1, I should get 0817 (08th week of 2017 year). As 4MAR 17 will fall into 8th or 9th week of 2017. And condition is 2017 1st week to be considered from 1st Monday

    Referring to above point #2, I should get last right 4 digits of that 11 digit number i.e., 1217 which means 12th week of 2017

    Re​​​ferring to above point #3, I should get same like input i.e., 0618​

    Then my calculated field contains all this date formats in single format instead of splitting in different formats.

    ------------------------------
    sundeep kotaru
    ------------------------------

    #Db2


  • 2.  RE: DB2 query

    Posted Thu February 20, 2020 02:12 AM
    Edited by System Admin Fri January 20, 2023 04:44 PM
    Hi sundeep, I have written the SQL as below.
    For cases other than the above three formats, additional CASE predicate clauses must be written.

    WITH t AS
(
SELECT '4MAR17' col1 FROM sysibm.sysdummy1
UNION ALL SELECT '12367891217' FROM sysibm.sysdummy1
UNION ALL SELECT '0618' FROM sysibm.sysdummy1
)
SELECT CASE WHEN LENGTH(RTRIM(col1)) = 6  THEN LPAD(WEEK(TO_DATE(col1,'DDMONYY')) - 1, 2, '0')||TO_CHAR(TO_DATE(col1,'DDMONYY'),'YY')
            WHEN LENGTH(RTRIM(col1)) = 11 THEN SUBSTR(col1,8,4)
            WHEN LENGTH(RTRIM(col1)) = 4  THEN col1
       ELSE NULL END wwyy
  FROM t



WWYY                                                                                                                                                                        
----
0817                                                                                                                                                                        
1217                                                                                                                                                                        
0618                                                                                                                                                                        

  3 record(s) selected.


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------

    Original Message


  • 3.  RE: DB2 query

    Posted Thu February 20, 2020 05:02 PM
    > I have one field with in DB2 View and that is character datatype. That field is having 3 kind of formats
    > 
    > 1. 4MAR17
    etc

    CASE WHEN field_is_in_format_1 THEN convert_to_desired_format
       ...
       ELSE '0000' END

    You can create the tests for deciding on the input format and the expression for converting each to the desired format.  Not hard but possibly messy - it will be a good learning exercise. 

    For format 1 you might need POSSTR or an embedded CASE to convert MAR to month 3, and WEEK_ISO to convert to weeks.  I'll leave it as an exercise how to handle the difference between your definition of first week and WEEK_ISO's definition.

    ------------------------------
    JAMES CAMPBELL
    ------------------------------

    Original Message


Related Content