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

    Unanswered: sql to db2 query

    hi iam new to db2 just need on query to be executed in my package against db2 database.this is my query in sql.I dont have a db2 client to check the syntax or the functions for db2.can any one of u convert this query to a db2 query.Thanks in ADV.

    SELECT table1.YYYY & table1.MM as MO_YR,
    table2.CNTRYCD AS CNTRY_CD,
    Sum(table1.AMT) AS [VALUE]
    FROM table1
    INNER JOIN table2
    ON (table1.MM = table2.MM)
    AND (table1.YYYY = table2.YYYY)
    WHERE (table1.YYYY BETWEEN YEAR(DATE()) AND YEAR(DATEADD("m",-3,DATE())))
    AND (table1.MM BETWEEN MONTH(DATE()) AND MONTH(DATEADD("m",-3,DATE())))
    GROUP BY table2.CNTRYCD, table1.YYYY, table1.MM

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 uses regular standard SQL for datetime arithmetics. So you can rephrase your predicates like this:
    Code:
    WHERE table1.YYYY BETWEEN YEAR(CURRENT DATE) AND YEAR(CURRENT DATE -3 MONTHS) AND ...
    I don't know what the '&' in the SELECT list is supposed to be doing. Everything else is looking fine.

    p.s: Did you have a look at the available datetime data types? You can explicitly store DATEs, TIMEs, and TIMESTAMPs. Separating the storage as you have done is more expensive (because less compact) than splitting year and month portions. And DB2 is smart enough to exploit indexes if you query like this:
    Code:
    table1.date BETWEEN CURRENT DATE AND (CURRENT DATE + 3 MONTHS - DAYS(CURRENT DATE))
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2008
    Posts
    8
    & is to be replased with concat function and yaa i could not guess the date function thank you.
    now i am running into a new issue with the where condetion. when ther is a changein year the first condetion ie yyyy(year)gives 2007 and 2008 and the second condetion mm(month)is giving 1 and 10. which means in 2007 1st to 10th month and 2008 1st 10th month.
    My requirment is to get data for running three months ie oct this date till today. any suggestions

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So why don't you simply use a DATE data type and then you don't have to worry about calculating the correct year/month combination? If you can't adjust the data model, you could convert the year/month combination to a valid DATE value (by using 01 as day component) and then query with this value as I mentioned above. (Note that this may have an impact on the indexability of the predicate.

    Note: The standard string concatenation operator is '||', which is also supported by DB2.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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