Results 1 to 5 of 5

Thread: SSRS and DB2

  1. #1
    Join Date
    Oct 2013
    Posts
    8

    Unanswered: SSRS and DB2

    Question 1:
    I wrote the following query. In the WHERE clause, HDR.CSDATE and Store.STSDAT are both Date Fields with the following structure: Length = 6, Field Type = Packed. My objective is to query the date range Sunday to Saturday of the previous week. I would like it to have the flexibility to be run on any day of the current week. Can anyone please help me with the syntax?

    SELECT Fields
    FROM MM750LIB.CSHHDR HDR
    left join MM750LIB.TBLSTR Store
    on hdr.csstor = Store.STRNUM

    where
    HDR.CSDATE >= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 7 DAYs , 'RRMMDD') )
    and HDR.CSDATE <= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 1 DAYs , 'RRMMDD') )
    and Store.STSDAT <= INTEGER( TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 1 DAYs , 'RRMMDD') )

    [SQL0171] Argument 1 of function TO_CHAR not valid.




    Question 2:
    If dates were stored in date format in db2, as in SQL Server, this would be a little easier. I could then just pass the 2 named parameters @StartDate and @EndDate, which are created in the report, as the following:

    SELECT Fields
    FROM MM750LIB.CSHHDR HDR
    left join MM750LIB.TBLSTR Store
    on hdr.csstor = Store.STRNUM
    WHERE
    HDR.CSDATE >= @StartDate
    And HDR.CSDATE <= @EndDate
    And Store.STSDAT <= @EndDate


    Does db2 allow the passing of named parameters? If yes, how would I write that query?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Question 1:
    ...
    ... Can anyone please help me with the syntax?

    ...
    ...
    [SQL0171] Argument 1 of function TO_CHAR not valid.
    What DB2 version/release and platform OS are you using?

    The expression
    "TO_CHAR((CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 7 DAYs , 'RRMMDD')"
    was valid on DB2 9.7.5 for Windows.

    Here is an example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    db2level
    ------------------------------------------------------------------------------
    
    D:\IBM\SQLLIB_V97\tools>db2level
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with 
    level identifier "08060107".
    Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23286_27924", 
    and Fix Pack "5".
    Product is installed at "D:\IBM\SQLLIB_V97" with DB2 Copy Name "DB2COPY1".
    Note: The expressions could be simplified like begin_previous_week_2 and end_previous_week_2.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CURRENT DATE AS CURRENT_DATE
         , (CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 7 DAYs AS begin_previous_week
         , (CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs) - 1 DAYs AS end_previous_week
         , CURRENT DATE - (DAYOFWEEK(CURRENT DATE) + 6) DAYs AS begin_previous_week_2
         , CURRENT DATE - DAYOFWEEK(CURRENT DATE) DAYs       AS end_previous_week_2
         , CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs AS begin_this_week
     FROM  sysibm.sysdummy1
    ;
    ------------------------------------------------------------------------------
    
    CURRENT_DATE BEGIN_PREVIOUS_WEEK END_PREVIOUS_WEEK BEGIN_PREVIOUS_WEEK_2 END_PREVIOUS_WEEK_2 BEGIN_THIS_WEEK
    ------------ ------------------- ----------------- --------------------- ------------------- ---------------
    2013-10-16   2013-10-06          2013-10-12        2013-10-06            2013-10-12          2013-10-13     
    
      1 record(s) selected.

    Question 2:
    ...
    Does db2 allow the passing of named parameters? If yes, how would I write that query?
    Yes.
    in Compound SQL statements
    Compound SQL (inlined) - IBM DB2 9.7 for Linux, UNIX, and Windows
    Compound SQL (embedded) - IBM DB2 9.7 for Linux, UNIX, and Windows
    Compound SQL (compiled) - IBM DB2 9.7 for Linux, UNIX, and Windows
    and/or in Function / Stored procedure
    CREATE FUNCTION (SQL scalar, table, or row) - IBM DB2 9.7 for Linux, UNIX, and Windows
    CREATE PROCEDURE (SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows


    By the way,
    The predicate "Store.STSDAT <= @EndDate" in WHERE clause in you query make "left join" to be equivalent to INNER JOIN.
    So, I thought the query should be like this.
    Code:
    SELECT Fields
     FROM  MM750LIB.CSHHDR hdr
     LEFT  OUTER JOIN
           MM750LIB.TBLSTR Store
      ON   Store.STRNUM =  hdr.csstor
       AND Store.STSDAT <= @EndDate
     WHERE hdr.CSDATE BETWEEN @StartDate AND @EndDate
    ;
    Last edited by tonkuma; 10-15-13 at 19:28. Reason: Add a reference to Function

  3. #3
    Join Date
    Oct 2013
    Posts
    8

    SSRS and DB2

    I have been told that this version of DB2 is the I-Series. The environment is AS400. If you want to know the specific version, how do I look it up?

  4. #4
    Join Date
    Oct 2013
    Posts
    8

    SSRS and DB2

    DB2 Version: I-Series 5, Version 6, Release 1

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The format string RR(and YY) are not supported by DB2 for i Version 6 Release 1.
    They are supported from DB2 for i Version 7 Release 1.

    So, please try something like this, to select Sunday to Saturday of the previous week.
    Code:
    ...
     WHERE HDR.CSDATE
           BETWEEN INTEGER( REPLACE( CHAR(
                      current date - (DAYOFWEEK(current date) + 6) DAYs
                    , ISO) , '-' , '' )
                   ) - 20000000
               AND INTEGER( REPLACE( CHAR(
                      current date - DAYOFWEEK(current date) DAYs
                    , ISO) , '-' , '' )
                   ) - 20000000
       AND ...

Posting Permissions

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