SQL code

system · November 2, 2005, 4:33am

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)

system · November 2, 2005, 4:59am

What database are you using, if not Oracle?

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

system · November 2, 2005, 5:03am

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)

system · November 2, 2005, 7:00am

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

Also replace sysdate with getdate()

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

MarkP · November 2, 2005, 11:25am

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

system · February 25, 2010, 9:08pm

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 (BOB member since 2010-02-25)

system · February 25, 2010, 9:11pm

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 (BOB member since 2002-06-20)

system · February 25, 2010, 9:18pm

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 (BOB member since 2010-02-25)

system · February 25, 2010, 9:22pm

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 (BOB member since 2002-06-20)

system · February 25, 2010, 9:38pm

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 (BOB member since 2010-02-25)

system · February 25, 2010, 9:39pm

Projection is not the same as aggregation

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

system · February 25, 2010, 9:40pm

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

Andreas (BOB member since 2002-06-20)

system · February 25, 2010, 9:50pm

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

BOBJGuy (BOB member since 2010-02-25)

system · February 25, 2010, 10:23pm

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

BOBJGuy (BOB member since 2010-02-25)

system · February 25, 2010, 11:34pm

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 (BOB member since 2002-06-06)

system · February 26, 2010, 3:11am

Yes. Sum(Case …End) in a measure select statement worked.Thanks.
Dave, I have gone through your thread about Object Projection and aggregation. I did not understand it. I will read it again. Can you please give the basic difference in one line or as simple as you can?

But how to write the object SQL for the following MTD YTD Measures

Users prompt for the date range date1 and Date2.According to that I have to calculate MTD and YTD. The query in the above thread has hardcoded dates. Instead of that it has to take the user date range. There is no Object level SQL. I have not written anything yet

BOBJGuy (BOB member since 2010-02-25)

system · February 26, 2010, 1:42pm

Aggregation happens on the database server during query execution. Projection happens on the client report during slice and dice. They are not substitutes for each other. Please also read the posts Andreas linked you to.

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

system · February 26, 2010, 6:39pm

Thank you Dave. I will go through your blogs one more time and see if I can understand them.

I did not understand the second part of your statement: “Projection happens on the client report during slice and dice”

I think when you define SUM() in the select part, the WebI query automatically groups by while reports execution. Am I correct?
One more question for you. Can I do SUM() in the SELECT part of a Dimension object?!! Sorry, if I am acting too stupid

BOBJGuy (BOB member since 2010-02-25)

system · February 26, 2010, 8:52pm

I don’t know how to explain it much more clearly than I did on my blog… there are screen shots and everything.

A group by clause will be added automatically, if needed, yes.

If you do that, it becomes a measure object instead. As it should.

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

system · February 26, 2010, 9:10pm

Do you mean if I add SUM to a dimension object SQL, it will automatically change to a Measure in Universe. I never tried that!!

I am going through your blogs again. I think I am getting it now. I will let you know if I have any questions.

BOBJGuy (BOB member since 2010-02-25)