LEFT OUTER JOIN in the WHERE clause

[former_member189295]

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

[denis_konovalov]

I have fixed your tag, please select more careful next time.

[mhmohammed]

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