Can a CASE statement be used in a complex join in the Physical layer?

Jerry S.

OBIEE 11g.   11.1.1.7.141014

We have two files: FileA and FileB.

This is how we need to join the files:

If FileB.Type = 'XXX' and FileA.Status = 'Active'

then the join would be

FileB.ID = FileA.ID

and FileB.Start-Date >= FileA.Start-Date

and FileB.End-Date <= FileA End-Date

For all other situations, the join would be:

FileB.ID = FileA.ID

The only way I can think of doing this is to use some type of CASE statement, but I dont know how to use

a CASE statement in a join in the Physical layer, and I'm not even sure if it is possible.

I'd appreciate any help anyone can give, and thank you in advance.

Gianni Ceresa

Hi Jerry,

Yes you can use a CASE in a physical join.

But you have to go into the Expression Builder as you don't have a special button like in 10g to create a complex join vs. a normal join, so OBIEE automatically makes it complex if required and it will decide based on the condition you write into the field inside the Expression Builder.

Jerry S.

I have tried this (Not my actual field names) in the expression builder for the physical join, but it wont take it:

CASE

   WHEN DB1.dbo.FileB.Type = 'XXX' and DB1.dbo.FileA.Status = 'Active'

   THEN

      DB1.dbo.FileB.ID = DB1.dbo.FileA.ID and

      DB1.dbo.FileB.Start-Date >= DB1.dbo.FileA.Start-Date and

      DB1.dbo.FileB.End-Date <= DB1.dbo.FileA End-Date

   ELSE

      DB1.dbo.FileB.ID = DB1.dbo.FileA.ID

END

This is the error I get:     [nQSError : 27002] Near <=>: Syntax error [nQSError: 26012].

The fields are being selected using the expression builder so they should be technically correct.

Gianni Ceresa

Well, your CASE WHEN doesn't really make sense from a SQL point of view ...

In a SQL query you can't write that kind of CASE WHEN, the THEN condition (and the ELSE) can't contain the = condition but it must return a column and you put the = after the CASE WHEN ... END.

You must rewrite your CASE WHEN in a different way, but think at it like you would write it in a SQL query.

Gianni Ceresa

You can try something like that (quickly done, so double check the logic ...):

DB1.dbo.FileB.ID = DB1.dbo.FileA.ID

AND

CASE WHEN DB1.dbo.FileB.Type = 'XXX' and DB1.dbo.FileA.Status = 'Active'

THEN DB1.dbo.FileB.Start-Date

ELSE DB1.dbo.FileA.Start-Date

END >= DB1.dbo.FileA.Start-Date

AND

CASE WHEN DB1.dbo.FileB.Type = 'XXX' and DB1.dbo.FileA.Status = 'Active'

THEN DB1.dbo.FileB.End-Date

ELSE DB1.dbo.FileA.End-Date

END <= DB1.dbo.FileA.End-Date

The idea is to always have the ID = ID check as you have it into the WHEN and ELSE and then use 2 CASE WHEN to check for FileB.Start-Date and FileB.End-Date. If the CASE WHEN doesn't match the END return the same field as the matched one (from FileA) so it produce something like ID=ID AND 1=1 AND 1=1 which is still correct.

Gianni Ceresa

Another options, maybe a bit simpler and also faster ...

DB1.dbo.FileB.ID = DB1.dbo.FileA.ID

AND

(

        DB1.dbo.FileB.Type = 'XXX'

AND DB1.dbo.FileA.Status = 'Active'

AND DB1.dbo.FileB.Start-Date >= DB1.dbo.FileA.Start-Date

AND DB1.dbo.FileB.End-Date <= DB1.dbo.FileA.End-Date

) OR (

      DB1.dbo.FileB.Type <> 'XXX'

OR DB1.dbo.FileA.Status <> 'Active'

)

Actually you don't need a CASE WHEN at all, just some logic in your condition and I'm sure this one will perform match better than the previous ugly one with the CASE WHEN and your DB will manage it without problems.

Jerry S.

That solution works beautifully.

Thank you very much for your help.