Skip to Content
avatar image
Former Member

LEFT OUTER JOIN in the WHERE clause

Hi All,

I am seeking your suggestion.

I need to be able to create a LEFT OUTER JOIN in the WHERE clause of the object in the screenshot below. The reason I want to do it in the object WHERE clause is because if I created an additional universe join it would result in a circular join and if I resolved the circular join via an alias then it would not give me the join structure I need. Previously, before SQL Server 2012 I would be able to add a LEFT OUTER JOIN in the WHERE clause by using the *= operator, so for example dbo.Prices.PriceDate_Dim_Key*=dbo.Date_Dim.EffPriceDate_Dim_Key , but this is no longer allowed.

Essentially, we need to be able to create either:

  • 1.An outer join in the object WHERE clause, or
  • 2.An outer join in a universe, that is not a circular join.

The question is: how can we now force UDT to create a LEFT OUTER JOIN in the WHERE clause of a universe object definition ?

It used to be possible to create an outer join in a object WHERE clause using the *= operator in the older versions of SQL server, .

Thanks and regards,

Sudam

udt.png (14.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 06, 2018 at 03:51 PM

    Hi Sudam,

    First things first, it's not a good idea to have where clause in the dimension objects, use Case statements instead. If you still want to do it that way, update the condition in Where clause as below.

    dbo.Prices>ProceDate_Dim_Key = dbo.Date_Dim.EffPriceDate_Dim_Key OR dbo.Prices>ProceDate_Dim_Key Is Null

    Let us know if this works!

    Thanks,
    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded