Outer join Issue

[former_member203645]

my outerjoin is not working as expected.

below sql is working fine to display all the months for that particular period even if there is no revenue..

SELECT
sum(RPH.FACT.GRSREVENUE),
RPH.DIMDATES.MONTHYEAR
FROM
RPH.FACT_V RIGHT OUTER JOIN RPH.DIMDATES ON (RPH.FACT.SALESDATE = RPH.DIMDATES.DATEVALUE)
where
RPH.DIMDATES.DATEVALUE BETWEEN {ts '2011-01-01 00:00:00'} AND {ts '2012-12-13 11:56:55'}
GROUP BY
RPH.DIMDATES.MONTHYEAR


---------------Issue----------------

When I add a new join the outer join is not working.


SELECT
sum(RPH.FACT.GRSREVENUE),
RPH.DIMDATES.MONTHYEAR,
RPH.DIMSTATEACCOUNT.STATECODE
FROM
RPH.FACT_V
RIGHT OUTER JOIN RPH.DIMDATES ON (RPH.FACT.SALESDATE = RPH.DIMDATES.DATEVALUE)
inner JOIN RPH.DIMSTATEACCOUNT ON (RPH.DIMSTATEACCOUNT.STATESID = RPH.FACT.STATESID)
where
RPH.DIMDATES.DATEVALUE BETWEEN {ts '2011-01-01 00:00:00'} AND {ts '2012-12-13 11:56:55'}
GROUP BY
RPH.DIMDATES.MONTHYEAR,
RPH.DIMSTATEACCOUNT.statecode
_________________

[Former Member]

Hi RUC,

Do you still have the issue or were you able to close this?

Thanks

Mallik

[Former Member]

Hi Usha,

Please do not leave the threads hanging as unanswered in case your issue is resolved?Mark it as Assumed answered/Answered

Thanks

Mallik

[Former Member]

Hi,

The issue might be 2nd join (inner join) which is filtering data from the above outer join set. So change the order of your joins i.e 1 st join should be inner join and 2nd should be outer as suggested by Mallik.

Thanks,

Suresh Babu Aluri.

[Former Member]

Hi RUC,

If the above syntax doesn't support on your DB then try below.

SELECT

sum(RPH.FACT.GRSREVENUE),

RPH.DIMDATES.MONTHYEAR,

RPH.DIMSTATEACCOUNT.STATECODE

FROM

RPH.DIMSTATEACCOUNT.STATESID

INNER JOIN

RPH.FACT_V ON RPH.DIMSTATEACCOUNT.STATESID = RPH.FACT.STATESID

RIGHT OUTER JOIN RPH.DIMDATES ON RPH.FACT.SALESDATE = RPH.DIMDATES.DATEVALUE

where

RPH.DIMDATES.DATEVALUE BETWEEN {ts '2011-01-01 00:00:00'} AND {ts '2012-12-13 11:56:55'}

GROUP BY

RPH.DIMDATES.MONTHYEAR,

RPH.DIMSTATEACCOUNT.statecode

Thanks

Mallik

[Former Member]

Hi RUC,

Try running it this way by pushing your inner join to where clause if this syntax is supported in your DB, just to be clear to execute inner and outer joins separately.

SELECT

sum(RPH.FACT.GRSREVENUE),

RPH.DIMDATES.MONTHYEAR,

RPH.DIMSTATEACCOUNT.STATECODE

FROM

RPH.FACT_V

RIGHT OUTER JOIN RPH.DIMDATES ON (RPH.FACT.SALESDATE = RPH.DIMDATES.DATEVALUE)

where

RPH.DIMSTATEACCOUNT.STATESID = RPH.FACT.STATESID

AND

RPH.DIMDATES.DATEVALUE BETWEEN {ts '2011-01-01 00:00:00'} AND {ts '2012-12-13 11:56:55'}

GROUP BY

RPH.DIMDATES.MONTHYEAR,

RPH.DIMSTATEACCOUNT.statecode

Thanks

Mallik

[Former Member]

Hi RUC,

Which Database and BO version are you using?

Does the query give incorrect results or any error message?

Regards,

Yuvraj Singh