Days between in DB2

building semantic layer
1

Hello,
I am trying to figure out how to calculate the difference between two dates, Policy effective and Policy Cancel effective. here is my syntax:

DAYS(UV_QUOTE.POLICY_EFFECTIVE_DATE) -
DAYS(UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE)

that should give me the difference between the two points in time. What I am seeing on the report is really strange. It seems to be multiplying this value by 3. No idea why.

Here is the SQL from Webi

SELECT
  UV_QUOTE.POLICY_EFFECTIVE_DATE,
  UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE,
  DAYS(UV_QUOTE.POLICY_EFFECTIVE_DATE) -
DAYS(UV_QUOTE.POLICY_CANCEL_EFFECTIVE_DATE)
FROM
  (
  select * from QHDM.FT_QUOTE_MQT
  ) AS UV_QUOTE
WITH UR

Nothing exciting there. Is my calculation all wrong? Is there a better way to calculate between in DB2?

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. Thank you, Andreas.]

Doolie (BOB member since 2007-08-08)

2

I guess that my first question is, why are you doing an inline view as your from and not just selecting the columns directly from the table, can’t really see the point of doing that.

Secondly why don’t you just bring the dates into your report and use the DaysBetween function in Webi.

I do however believe the problem with your query is that you don’t have any unique information to filter the report on, so Webi automatically groups all the rows returned and sums them together.

Try running the query directly onto your DB2 database and see what your result set it, you may likely see more rows returned in DB2 than Webi and if this is the case, go to the Properties tab and display in Webi and turn off the auto row aggregation to show all rows.

Hope this helps.

plessiusa :netherlands: (BOB member since 2004-03-22)