Calculation working in SQL but not working in DB2 .

building semantic layer
1

Hi BO-Gurus ,

I have a situation in my hand and need your help . We are at present moving three very important universe from SQL server to DB2. While most of the objects are tested fine .There is one measure which is supposed to calculate a percentage is failing in DB2 while it was successful in SQL server .
The SQL server function contains isnull() function which we have replaced by COALESCE() function . While SQL server was able to calculate the % Db2 is simply producing 0%.

following are the SQL definitions of the measure in SQL server and in DB2 :

@Aggregate_Aware(
case when isnull(sum( case when (DM_JOX_AGGR_YR_TBL.CARRIER_ID = 554)
then (DM_JOX_AGGR_YR_TBL.TEU) else 0 end ),0) = 0 then 0 else
(isnull(sum( case when (DM_JOX_AGGR_YR_TBL.CARRIER_ID = 554)
then (DM_JOX_AGGR_YR_TBL.TEU) else 0 end ),0) / sum(DM_JOX_AGGR_YR_TBL.TEU) ) end,
case when isnull(sum( case when (DM_JOX_AGGR_YR_MTH_TBL.CARRIER_ID = 554)
then (DM_JOX_AGGR_YR_MTH_TBL.TEU) else 0 end ),0) = 0 then 0 else
(isnull(sum( case when (DM_JOX_AGGR_YR_MTH_TBL.CARRIER_ID = 554)
then (DM_JOX_AGGR_YR_MTH_TBL.TEU) else 0 end ),0) / sum(DM_JOX_AGGR_YR_MTH_TBL.TEU) ) end,
case when isnull(sum( case when (DW_JOX_ALL_DATA_TBL.CARRIER_ID = 554)
then (DW_JOX_ALL_DATA_TBL.TEU) else 0 end ),0) = 0 then 0 else
(isnull(sum( case when (DW_JOX_ALL_DATA_TBL.CARRIER_ID = 554)
then (DW_JOX_ALL_DATA_TBL.TEU) else 0 end ),0) / sum(DW_JOX_ALL_DATA_TBL.TEU) ) end)

@Aggregate_Aware(
case when COALESCE(sum( case when (DM_JOX_AGGR_YR_TBL.CARRIER_ID =554)
then (DM_JOX_AGGR_YR_TBL.TEU) else 0 end ),0) = 0 then 0 else
(COALESCE(sum( case when (DM_JOX_AGGR_YR_TBL.CARRIER_ID =554)
then (DM_JOX_AGGR_YR_TBL.TEU) else 0 end ),0) / sum(DM_JOX_AGGR_YR_TBL.TEU) ) end,
case when COALESCE(sum( case when (DM_JOX_AGGR_YR_MTH_TBL.CARRIER_ID =554)
then (DM_JOX_AGGR_YR_MTH_TBL.TEU) else 0 end ),0) = 0 then 0 else
(COALESCE(sum( case when (DM_JOX_AGGR_YR_MTH_TBL.CARRIER_ID =554)
then (DM_JOX_AGGR_YR_MTH_TBL.TEU) else 0 end ),0) / sum(DM_JOX_AGGR_YR_MTH_TBL.TEU) ) end,
case when COALESCE(sum( case when (DW_JOX_ALL_DATA_TBL.CARRIER_ID =554)
then (DW_JOX_ALL_DATA_TBL.TEU) else 0 end ),0) = 0 then 0 else
(COALESCE(sum( case when (DW_JOX_ALL_DATA_TBL.CARRIER_ID =554)
then (DW_JOX_ALL_DATA_TBL.TEU) else 0 end ),0) / sum(DW_JOX_ALL_DATA_TBL.TEU) ) end)

A quick response will be highly appreciated as I’m in a hurry .to me it’s quite baffling why it’s not working .

Thanks in advance.

souravroy3 :india: (BOB member since 2008-03-26)

2

isnull() function which we have replaced by COALESCE() function

isnull is a conditional function whereas coalesce is not. If you are replacing a conditional fuction with coalesce how would the case statement work? When will the sql in the else condition execute?

Prabir Maulik (BOB member since 2006-06-30)

3

Please ignore previous post… the curse of not reading the entire section :frowning:

Prabir Maulik (BOB member since 2006-06-30)

4

@prabir thanks prabir for taking a look at it but can someone come up with a solution?

souravroy3 :india: (BOB member since 2008-03-26)

5

You did check the database to make sure that for the DM_JOX_AGGR_YR_TBL.CARRIER_ID =554 the DM_JOX_AGGR_YR_TBL.TEU column contains values other than 0?

Prabir Maulik (BOB member since 2006-06-30)

6

Hi Prabir thanks for your interest it is solved now. It was a precision level limitation of db2 . I had to change 0 to 0.00000 and cast the calculations to decimal(15,5) to make it work.

souravroy3 :india: (BOB member since 2008-03-26)