Home » Developer & Programmer » Reports & Discoverer » CASE function with multiple criteria (Discoverer 4.1 / Windows xp)
CASE function with multiple criteria [message #588057] Thu, 20 June 2013 10:32 Go to next message
mdfreeman
Messages: 4
Registered: June 2013
Location: Canada
Junior Member
Hi!

I am new to the forum. I've been using Discoverer for about 6 months now, mostly self taught.

I am using: Desktop / Discoverer 4.1 / Windows XP.

I am attempting to add a new calcauted column and have had some success with the CASE funtion but need to add additional criteria.

What I have that works is:

SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' THEN Total Spend Plus Commit ELSE 0 END)


What I need to add are a few additional criteria. I attempted and failed with a few variants of this:

SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND
Task Owing Company = '534' OR '915' THEN Total Spend Plus Commit ELSE 0 END)

The three criteria points that I am looking to includea are:

• Expenditure Type = 'Supplier Rebates'
• Capitalizable = 'Y'
• Task Owing Company = '534' OR '915'

Thank you in advance for the help!
Re: CASE function with multiple criteria [message #588058 is a reply to message #588057] Thu, 20 June 2013 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "failed" mean?

Regards
Michel
Re: CASE function with multiple criteria [message #588059 is a reply to message #588058] Thu, 20 June 2013 10:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
This isn't valid:
Task Owing Company = '534' OR '915'

Needs to be either:
(Task Owing Company = '534' OR Task Owing Company = '915')

or
Task Owing Company IN ('534', '915')
Re: CASE function with multiple criteria [message #588068 is a reply to message #588059] Thu, 20 June 2013 12:00 Go to previous messageGo to next message
mdfreeman
Messages: 4
Registered: June 2013
Location: Canada
Junior Member
@Michel - Good point. Next time I'll makes sure I include the error information in my question.

@cookiemonster
Thank you! I am really close!
If I excluded the 'Task Owing Company' the calculation works but if I include it the results are zero.
Also, for some reason Oracle automatically adds in single quotes around'Task Owing Company'

The equation now looks like this:

SUM(CASE WHEN Expenditure Type = 'Supplier Rebates' AND Capitalizable = 'Y' AND 'Task Owing Company' IN ('534','915') THEN Total Spend Plus Commit ELSE 0 END)
Re: CASE function with multiple criteria [message #588071 is a reply to message #588068] Thu, 20 June 2013 12:06 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is
THEN Total Spend Plus Commit 
valid in Discoverer (I don't use it so I don't know)? Because, it is not in regular SQL. (What does it mean, anyway? "Total Spend Plus Commit"?)
Re: CASE function with multiple criteria [message #588074 is a reply to message #588071] Thu, 20 June 2013 12:32 Go to previous messageGo to next message
mdfreeman
Messages: 4
Registered: June 2013
Location: Canada
Junior Member
Thanks for the question Littlefoot.

'Total Spend Plus Commit' is a field in the db that gets summed up

The piece of the code that doesn't seem to work is when I try to include the criteria:

AND 'Task Owing Company' IN ('534','915') 


Or

('Task Owing Company' = '534' OR 'Task Owing Company' = '915')


Result ends up being zero also Oracle adds the single quotes around 'Task Owing Company'
Re: CASE function with multiple criteria [message #588075 is a reply to message #588074] Thu, 20 June 2013 12:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said, I don't know Discoverer at all. If it was an "ordinary" SELECT statement, I'd say that it is an invalid name as there shouldn't be any space within a column name. Also, Oracle isn't case sensitive (i.e. Task_Owing_Company is the same as TASK_OWING_COMPANY or TasK_OWinG_CompANY). Is it the case with Discoverer as well?

How does Discoverer know that Task Owing Company is a single field (and not, for example one that is called "Task" and another one called "Owing Company"? Therefore, I suppose that it is OK that it enclosed it into single quotes (or brackets or whatever else).

What Discoverer did (with these single quotes) is that it checks whether string 'Task Owing Company' is equal to a string '534' or a string '915' (of course, it is not, and that's why it returns 0 as the result). The question is: why did it include single quotes? Could you substitute them with double quotes (if that's acceptable) and see what happens? Can you remove them?
Re: CASE function with multiple criteria [message #588076 is a reply to message #588075] Thu, 20 June 2013 12:51 Go to previous messageGo to next message
mdfreeman
Messages: 4
Registered: June 2013
Location: Canada
Junior Member
Quote:
How does Discoverer know that Task Owing Company is a single field (and not, for example one that is called "Task" and another one called "Owing Company"?


I was thinking the same thing but in the first part of teh code there is another field Expenditure Type and Oracle doesn't put single quotes around it and the calculation comes back with results.
Re: CASE function with multiple criteria [message #588077 is a reply to message #588076] Thu, 20 June 2013 12:57 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Exactly! That's what I was wondering (but forgot to write it). Oh well, I'd suggest you to wait for someone who knows Discoverer, but our last expert who regularly visited OraFAQ stopped doing that in September 2012. I'm afraid I can't help; hopefully, someone else will. Meanwhile, maybe you should visit OTN Discoverer forum, I guess that these people might be able to help you.
Previous Topic: problem generating dos reports in 10g while migrating 6i to 10g
Next Topic: oracle reports place holder column
Goto Forum:
  


Current Time: Thu Mar 28 18:35:30 CDT 2024