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

Question

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.

Jerry S. · Answer

That solution works beautifully.

Thank you very much for your help.

Gianni Ceresa · Answer

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.

Gianni Ceresa · Answer

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.