OraFAQ Forum: Reports & Discoverer » CASE function with multiple criteria

Home » Developer & Programmer » Reports & Discoverer » CASE function with multiple criteria (Discoverer 4.1 / Windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CASE function with multiple criteria [message #588057]

Thu, 20 June 2013 10:32

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!

Report message to a moderator

Re: CASE function with multiple criteria [message #588058 is a reply to message #588057]

Thu, 20 June 2013 10:33

Michel Cadot
Messages: 67640
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does "failed" mean?

Regards
Michel

Report message to a moderator

Re: CASE function with multiple criteria [message #588059 is a reply to message #588058]

Thu, 20 June 2013 10:36

cookiemonster
Messages: 13895
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')

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

Report message to a moderator

Re: CASE function with multiple criteria [message #588068 is a reply to message #588059]

Thu, 20 June 2013 12:00

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)

Report message to a moderator

Re: CASE function with multiple criteria [message #588071 is a reply to message #588068]

Thu, 20 June 2013 12:06

Littlefoot
Messages: 21647
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

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"?)

Report message to a moderator

Re: CASE function with multiple criteria [message #588074 is a reply to message #588071]

Thu, 20 June 2013 12:32

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')

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

Result ends up being zero also Oracle adds the single quotes around 'Task Owing Company'

Report message to a moderator

Re: CASE function with multiple criteria [message #588075 is a reply to message #588074]

Thu, 20 June 2013 12:42

Littlefoot
Messages: 21647
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?

Report message to a moderator

Re: CASE function with multiple criteria [message #588076 is a reply to message #588075]

Thu, 20 June 2013 12:51

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.

Report message to a moderator

Re: CASE function with multiple criteria [message #588077 is a reply to message #588076]

Thu, 20 June 2013 12:57

Littlefoot
Messages: 21647
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.

Report message to a moderator

Previous Topic:	problem generating dos reports in 10g while migrating 6i to 10g
Next Topic:	oracle reports place holder column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 18 21:21:27 CST 2021