Where statement vs Case?

building semantic layer
1

Ok I’m pretty sure I know the answer to this one, but figured hearing it from others out loud would make me feel a bit easier. Here’s my situation with a particular object.
Dealing with payroll data… we have line details which can include taxes, deductions, and earnings.
These items are seperated in multiple tables in the database and joined via a derrived table in my universe. When joined, the original table origin is denoted as a “LineItemCategory” column in the Derived table, where “E” denotes it came from the earnings…

So I have two ways to create a pure ‘earning’ object:

  1. Sum(Case When DT.LineItemCategory=‘E’ Then DT.Amount Else 0 end)
    OR
  2. Sum(Dt.Amount) WHERE DT.LineItemCategory = ‘E’

Basically an inline filter or a where filter… I’m leaning toward #1, as I recall issues with where statements… but I cannot remember what those issues are.
Am I correct that Inline filtering is the prefered method? or does it not really matter?

JPetlev (BOB member since 2006-11-01)

2

Choice 1, never use where statments in objects, use CASE :)!

They will affect all the items in the query, worst case scenario you will have to have five queries to do the same as one, if you had a number of measures defined with where statements…

Mak 1 :uk: (BOB member since 2005-01-06)

3

It matters a lot. If you use “Where” then that gets applied to the whole SQL - always when you use that object - and thats not typically what you want to do.

The CASE statement applied solely to whatever is within the CASE

Seems obvious which is the better approach.

dessa :madagascar: (BOB member since 2004-01-29)

4

That’s right, Doh! :yesnod:
I knew there was a reason my old universes didn’t use case statements, I just couldn’t remember why. It’s been a while since I had to start a new universe from scratch, sometimes I forget some of those old decision items…

Thanks.

JPetlev (BOB member since 2006-11-01)

5

I guess you are just dusting down your universe hat Mr Petlev, we have all been there… :slight_smile: .

Mak 1 :uk: (BOB member since 2005-01-06)