Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    8

    Unanswered: SSIS query to extract DB2 data

    SQL query to extract DB2data

    --------------------------------------------------------------------------------

    Hi i have a query
    select 12 * int( "SQLUSER"."BTT120".yyyy) + int("SQLUSER"."BTT120".mm) as actual,
    12*year(CURRENT DATE) + month(CURRENT DATE) as ending,
    12*year(CURRENT DATE) + month(CURRENT DATE)-3 as starting
    from "SQLUSER"."BTT120"
    where "SQLUSER"."BTT120".yyyy ='2007' and "SQLUSER"."BTT120".mm in ('10','11','12')

    which gives me ourput

    Actual ending Starting
    24094 24097 24094
    24095 24097 24094
    24096 24097 24094


    My actual requirment is to get the data for the rolling 3 months for which i used the same logic as above in the WHERE clause.from above the Actual column will always be between Starting and Ending column output.


    SELECT "SQLUSER"."BTT120".YYYY CONCAT "SQLUSER"."BTT120".MM AS MO_YR
    FROM "SQLUSER"."BTT120"
    where (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) between
    (12*year(CURRENT DATE)) + month(CURRENT DATE)-3 and
    (12*year(CURRENT DATE)) + month(CURRENT DATE)

    everything looks good but iam not getting output, there is data for the date range

    (12 * int( "SQLUSER"."BTT120".yyyy)) + int("SQLUSER"."BTT120".mm) = 24094
    (12*year(CURRENT DATE)) + month(CURRENT DATE)-3 = 24094
    (12*year(CURRENT DATE)) + month(CURRENT DATE) = 24097

    can anyone help me if iam wrong in syntax. new to DB2

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Why don't you use simple datetime arithmetics?
    Code:
    SELECT ...
    FROM   sqluser.btt120 AS t
    WHERE  DATE('0001-01-01') + (INT(t.yyyy)-1) YEARS + (INT(t.mm)-1) MONTHS BETWEEN CURRENT DATE AND CURRENT DATE + 3 MONTHS
    You may have to figure out how you define your 3-month interval with respect to day-precision.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2008
    Posts
    8
    this is what i have comeup with

    WHERE DATE(YYYY CONCAT '-' CONCAT MM CONCAT '-' CONCAT '1') BETWEEN
    CURRENT DATE AND CURRENT DATE - 3 MONTHS

    but when i add this condetion to the query and run it in a SSIS package its give me an errou after 15 to 20 minutes
    [IBM][CLI DRIVER][DB2] SQL090SN unsuccessful execution due to resource limit being exceeded. "ASUTIME" limit =......SQLSTATE=57014

    how do i get ride of this error is it something to do with the DB2 server query time out settings?
    This is something troubling me for many other queries. iam at the developing stage yet so lot of queryes to be tested.

    Any help appriciated
    Thanks ALL

Posting Permissions

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