BusinessObjects Board

SQL code

building semantic layer
#1

Hi,

I’d like to sum up one of our daily sales objects by month and year, hoping to improve reporting speed.

Found the code for it in another forum for Oracle.
CASE WHEN To_Number ( To_Char (SalesDate, ‘yyyy’)) = To_Number ( To_Char (Sysdate, ‘yyyy’)) THEN SUM(SalesRevenue)
ELSE Sum(0)

Can anyone help me translate this into SQL to create those objects?
Is there a way to calculate month do date and ytd too?

Thanks,
Christiane

Chriscat (BOB member since 2005-10-04)

#2

I think as far as oracle is concerned the code you mention will work fine for your object except there is no need to use sum. Just create a measure and then specify sum function for aggregation and BO will handle it.
Regarding MTD and YTD i think this topic has been discuessed before many times. Please do a search on the forum. That will save you some time. :yesnod:

Hope this helps

salam :pakistan: (BOB member since 2005-01-27)

#3

:blue: NOOOOOOOOOOOOOOOOOOO! Don’t do it. :nonod:

Measures should have SQL aggregation on them. Let your database do the summing and not the reports.

The code given will work, but I prefer it like:
Sum(CASE WHEN To_Number ( To_Char (SalesDate, ‘yyyy’)) = To_Number ( To_Char (Sysdate, ‘yyyy’)) THEN SalesRevenue
ELSE 0
END )

Steve Krandel :us: (BOB member since 2002-06-25)

#4

:crazy_face: True Dont know what was I thinking. :crazy_face:

salam :pakistan: (BOB member since 2005-01-27)

#5

Chriscat,

As Steve told, the code will aggregate Sales Revenue for the year.

For the month,


Sum(CASE WHEN To_Number ( To_Char (SalesDate, 'yyyy')) = To_Number ( To_Char (Sysdate, 'yyyy') and To_Number ( To_Char (SalesDate, 'MM')) = To_Number ( To_Char (Sysdate, 'MM'))THEN SalesRevenue ELSE 0 END )

BO_Chief :us: (BOB member since 2004-06-06)

#6

I tried the code before, but got an error telling me that To_Char is not a recognized function name when I parse the object. I

assumed it had to do with the code being oracle, hence I asked.

Why am I getting the error and what can I do to fix it?

Chriscat (BOB member since 2005-10-04)

#7

What database are you using, if not Oracle?

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

MS SQL server,hence the ask for translation into MS SQL, but I realised I left out mentioning that we have MS and not Oracle, sorry.

So back to my original question, I need the SQL code of this to be “translated” so my MS SQL server knows what I am talking about.

Chriscat (BOB member since 2005-10-04)

#9

I think you need to use the cast or convert function instead of to_char.

Also replace sysdate with getdate()

Steve Krandel :us: (BOB member since 2002-06-25)

#10

For SQL Server, your code should be

Sum(CASE WHEN datepart(yyyy,SalesDate) = datepart(yyyy,getdate()) and datepart(mm,SalesDate) = datepart(mm,getdate()) THEN SalesRevenue ELSE 0 END)

Regards,
Mark

#11

I have a similar requirement.

But if I use SUM in the measure object, Webi is throwing an error message
ORA-00937: not a single-group group function

It works fine without SUM. Why should I use SUM again in the measure object?

BOBJGuy :uk: (BOB member since 2010-02-25)

#12

Why use again?
Where are you using SUM twice?
What is the exact SQL code of your measure (defined in the SELECT box of Designer)?

In addition, do refrain from posting the essentially same question twice, Please do not cross-post. It leads to fragmented discussions and is against our forum rules.

Andreas :de: (BOB member since 2002-06-20)

#13

This is the SQL I use in the SELECT part of Measure Object properties. User prompts forTrans_Date and on the report I should display MTD, YTD according to thedates passed by the user

MTD:-

SUM 
( 
   CASE WHEN Trans_Date >= trunc(Trans_Date,'mm')  AND Trans_Date <= Trans_Date  

      THEN DECODE (SKU_FLAG, 'ABC', SALE_AMOUNT, ‘BCA', (SALE_AMOUNT)*10), 0) 
      ELSE 0 
   END 
)

[Moderator Edit: Added code formatting - Andreas]

BOBJGuy :uk: (BOB member since 2010-02-25)

#14

:?: Does that not always evaluate to TRUE?
Seems like this is not the real SQL, is it?
And where are you using SUM twice?

Andreas :de: (BOB member since 2002-06-20)

#15

Since it is a Measure Object, Do I need to use SUM again in the SELECT part? In the object properties I am choosing SUM aggregate projection. So, if I use it again in the SQL it would be two times. Isn’t it?

BOBJGuy :uk: (BOB member since 2010-02-25)

#16

Projection is not the same as aggregation :wink:

Dave Rathbun :us: (BOB member since 2002-06-06)

#17

Using SUM in the SELECT box in Designer and SUM under properties for projection are two different things! See here, please.

Andreas :de: (BOB member since 2002-06-20)

#18

What should I do now?

If I use SUM I am getting the “ORA-00937: not a single-group group function” because it is looking for GROUP BY Syntax.

I am confused :cry:

BOBJGuy :uk: (BOB member since 2010-02-25)

#19

But, Why am I getting that ORA error message?!!!

BOBJGuy :uk: (BOB member since 2010-02-25)

#20

You haven’t posted your real code, have you?

This works:

sum(case expression here end)

This does not work and throws the error you are mentioning:

case when x then sum(y) else sum(z) end

Your sum has to be the outermost function in order to work correctly.

Dave Rathbun :us: (BOB member since 2002-06-06)