10-11-13, 16:27

Posts: 3

SSRS in DB2

--------------------------------------------------------------------------------
My background is SQL Server. In our shop, we have db2 dates in our JDA system that we can query against as 6 digit integer, format YYMMDD. How can I write a query whose WHERE CLAUSE includes MM750LIB.CSHHDR.CSDATE >= Current Date - 20, or MM750LIB.CSHHDR.CSDATE >= ((Today's Date - Yesterday's Date) - 8 Days)? Please include the complete select statement so I don't confuse syntax.

#2 (permalink)

10-13-13, 11:01

tonkuma

Registered User

Location: Japan

Posts: 2,987

Quote:

MM750LIB.CSHHDR.CSDATE >= Current Date - 20

MM750LIB.CSHHDR.CSDATE >= INTEGER( TO_CHAR(Current Date - 20 DAYs , 'RRMMDD') )

Quote:

MM750LIB.CSHHDR.CSDATE >= ((Today's Date - Yesterday's Date) - 8 Days)

Doesn't "(Today's Date - Yesterday's Date)" return one?

Quote:

... we have db2 dates in our JDA system that we can query against as 6 digit integer, format YYMMDD.

This is a bad strategy(you might already realized).
You should use DATE datatype for dates columns.
Then you can avoid troublesome considerations/transformations in your queries.

Even if the data came from other system with that format,
transform the data into the string representaion of DATE datatype brfore store the data into DB2 table.

#3 (permalink)

10-13-13, 11:57

Bob_in_Houston

Registered User

SSRS and DB2

--------------------------------------------------------------------------------
I have a Microsoft SSRS report that queries against a db2 database. The date range in the report is Sunday thru Saturday of the previous week. So Today's Date - Yesterday's Date will always return the first day of the week, regardless of when the report is run. My real preference would be to have a @StartDate and an @EndDate created in the report and then to query against db2 using those 2 date parameters. Also, in some of my reports, there are multiple queries that use that same date range in the WHERE clause. The query I have written for this report, for instance, is a CTE that includes this. Unfortunately, I am a newbee to db2. So I do not know db2 syntax. The questions I sent are my desperate attempts to create the date range at the back end since SSRS is terrible at using db2 queries. By the way, in your date comparison "MM750LIB.CSHHDR.CSDATE >= INTEGER( TO_CHAR(Current Date - 20 DAYs , 'RRMMDD') )", what does RR mean?

#4 (permalink)

10-13-13, 13:33

tonkuma

Registered User

Location: Japan

First of all, one problem might be how SSRS provide the SQL to DB2.
(wheather some modificaions were done or not, before SSRS provide the query text to DB2?)

But I don't know SSRS,
so I can provide only how to do in DB2 SQL for your reqirements in the following descriptions.

(1)

Quote:

what does RR mean?

Please see
Table 1. Format elements for the VARCHAR_FORMAT function
in VARCHAR_FORMAT - IBM DB2 9.7 for Linux, UNIX, and Windows

(2)

Quote:

... Today's Date - Yesterday's Date will always return the first day of the week, regardless of when the report is run.

What is the first day of the week?

I payed atention to
"The date range in the report is Sunday thru Saturday of the previous week."
in your statements,
then I guessed the first day of the week might be Sunday in your requiremens.
( Please note that first day of the week is not common to all.

Quote:

DAYOFWEEK_ISO returns the day of the week ..., where 1 represents Monday.

DAYOFWEEK_ISO - IBM DB2 9.7 for Linux, UNIX, and Windows
)

As a consequence,
If Sunday was the first day of the week, please try
CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYs
or
CURRENT DATE - MOD(DAYOFWEEK_ISO(CURRENT DATE) , 7) DAYs

#5 (permalink)

Today, 16:33

Bob_in_Houston

Registered User

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?