Case Statement "SQL Command not properly ended"

building semantic layer
1

I am trying to bring different data on to a report, such as Hires, Transfers and Terms. I tried to replicate this SQL into a object called HIRES to start with, and this is what I have.


 case
  when ACTIONDIM.ACTION = 'HIR'
 then sum(EMPLOYEETRANSACTIONFACT.factcount)
	   else 0
end  as Hired
from EMPLOYEETRANSACTIONFACT
join ACTIONDIM
on EMPLOYEETRANSACTIONFACT.actionkey = ACTIONDIM.actionkey
and (EMPLOYEETRANSACTIONFACT.txneffectivedate >= '01-MAY-2005'
and EMPLOYEETRANSACTIONFACT.txneffectivedate <= '31-MAY-2005')
group by EMPLOYEETRANSACTIONFACT.departmentid, ACTIONDIM.action
end

I get "SQL not properly ended. " What am I doing wrong? Should I separate this into different objects or something like that?

Flame (BOB member since 2004-09-21)

2

We cannot have GROUP BY as part of the Object… That will be generated by Businessobjects based on the Dimensions and Measures used for a report…

So to go with your object definition of Hired then it should look something like this with the joins being done at the Universe Level and if needed you can add Predefined condition for constraining any dates…


SUM(
CASE 
   WHEN ACTIONDIM.ACTION = 'HIR' 
THEN
  EMPLOYEETRANSACTIONFACT.factcount
ELSE
  0
END
)

Sridharan :india: (BOB member since 2002-11-08)

3

Jah, I agree with Sridharan. For your date ranges, you can put a condition into the query.

Am I confused, or do you have an extra ‘END’ in your original SQL?

Judy

JMulders :us: (BOB member since 2002-06-20)

4

Thanks, that worked. For constraining on dates I can add a prompt to the report to pull only that range.

Flame (BOB member since 2004-09-21)