Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: Fetching data according to date criteria

    Hi all

    I'm trying to bring back data with certain criteria but am struggling with the date criteria. I need to fetch data from an "APPLICATION_DATE" column but the date needs to be 12 or more months ago from current date. I am using version 8.1 on z/OS. Please let me know if you need any other details.

    Thanks
    Andre van Zyl

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    >= current date - 1 years
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Try this:
    "select current_date - 12 months from sysibm.sysdummy1"
    and read this Universe - A Global Village: DB2 Basics: Fun With Date And Time

  4. #4
    Join Date
    Oct 2009
    Posts
    8
    Thanks for the quick replies...

    Here is the code I'm using but am receiving an error(see attachment):

    SELECT DISTINCT A.MASTER_ID,
    A.COMPANY_CODE,
    C.DIRECTORY_ID,
    B.PLAN_CODE,
    B.STRT_OF_PRM_ACT_DT,
    B.APPLICATION_DATE,
    B.RB_CVG_STATUS,
    B.PARENT_ID,
    B.CVG_TYPE,
    A.POLICY_STATUS,
    D.INSURED_TYPE
    FROM GS20.CONTT A,
    GS20.CVGT B,
    GS20.ROLES C,
    GS20.DPXXT D
    WHERE A.MASTER_ID = B.MASTER_ID AND
    A.POLICY_STATUS IN ('A','P','G') AND
    B.RB_CVG_STATUS IN ('A') AND
    A.COMPANY_CODE = B.COMPANY_CODE AND
    A.MASTER_ID = C.MASTER_ID AND
    A.COMPANY_CODE = C.COMPANY_CODE AND
    A.RESTRICTION_CODE = '' AND
    A.MASTER_ID IN (SELECT MASTER_ID FROM GS20.CVGT
    WHERE B.MASTER_ID NOT LIKE '69355UCB%' GROUP BY MASTER_ID HAVING COUNT(*) = 1) AND
    B.APPLICATION_DATE >= CURRENT DATE - 1 YEARS
    FETCH FIRST 5 ROWS ONLY

    Thank You
    Andre van Zyl
    Attached Thumbnails Attached Thumbnails error.bmp  

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the data type of APPLICATION_DATE column of GS20.CVGT table?

  6. #6
    Join Date
    Oct 2009
    Posts
    8
    Hi Tonkuma

    The data type is DECIMAL(8, 0)

    Thanks

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Apart from the error SQL0401N, following part of your query looks strange for me.
    I thought that result of your query would be same, if B.MASTER_ID NOT LIKE '69355UCB%' was moved to WHERE clause of main query.
    A.MASTER_ID IN (SELECT MASTER_ID FROM GS20.CVGT
    WHERE B.MASTER_ID NOT LIKE '69355UCB%' GROUP BY MASTER_ID HAVING COUNT(*) = 1) AND
    Because, the subquery will return null(no row), if B.MASTER_ID LIKE '69355UCB%'. Then, WHERE condition of main query would result to false.

    If B.MASTER_ID NOT LIKE '69355UCB%', the subquery will be equivalent to:
    Code:
    (SELECT MASTER_ID
       FROM GS20.CVGT
      GROUP BY MASTER_ID
     HAVING COUNT(*) = 1
    )
    Where B.MASTER_ID would be a column of the following table of your query:
    ...
    FROM GS20.CONTT A,
    GS20.CVGT B,
    GS20.ROLES C,
    ...
    Last edited by tonkuma; 10-27-09 at 11:02.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The data type is DECIMAL(8, 0)
    What is the format of the column?
    Is it yyyymmdd?

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the column was DECIMAL(8, 0) with format "yyyymmdd.",
    then my idea is "convert it to date, then use date calculation of DB2(like Rahul Singh and dr_te_z wrote)".

    Here is an example:
    DATE( TRANSLATE('abcd-ef-gh', CHAR(B.APPLICATION_DATE), ' abcdefgh.') ) >= CURRENT DATE - 1 YEAR

    This was not tested.
    But, I saw PDF manual "DB2 Universal Database for z/OS SQL Reference Version 8".
    CHAR

    .....

    Decimal to Character

    .....

    The result is the fixed-length character string representation of the argument in the
    form of an SQL decimal constant. The result includes a decimal-character and p
    digits, where p is the precision of the decimal-expression. If the argument is
    negative, the first character of the result is a minus sign. Otherwise, the first
    character is a blank, which means that a positive value always has one leading
    blank.


    The leading blank is not returned for CAST(decimal-expression AS CHAR(n)).

    The length of the result is 2+p, where p is the precision of the decimal-expression.

    .....

Posting Permissions

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