OraFAQ Forum: SQL & PL/SQL » Using (Conditional Logic) decode within decode

Home » SQL & PL/SQL » SQL & PL/SQL » Using (Conditional Logic) decode within decode

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

Using (Conditional Logic) decode within decode [message #8472]

Fri, 22 August 2003 17:55

Joyce
Messages: 13
Registered: March 2000

Junior Member

I had posted the summation using decode query. Now, building up on that query, I have the following scenario --

I am using decode to pull the actual $ profit_amount where indicator ='0' while subsitituting '0' for row where indicator = '1', (If indicator = 0, then get the $ profit_amount, else make the profit_amount = 0)
The profit_amount is summed using the sum function.
output is -
ID Indicator Profit_amount
100 1 0
100 0 120
101 1 0
101 0 355
107 1 0
107 0 75

I want to pull profit amounts for only those lines where category_type = 'USA' AND indicator = '0'.
If category type is not USA, and indicator is >= '1', then show a 'None' or '0'.
I am using the following code, but not getting the desired output (Error:ORA-00907 missing right parenthesis). I have checked for all the parenthesis and they are in place.

select a, b,
decode(table1.indicator, '0', decode (table2.categroy_type, 'USA',sum( table.profit_amount), 'None') '0')
from table1, table2, ... table(n)
where
--joins--
Group By
indicator,
category_type,
a,
b

Desired Output-
How do I get the desired output -
ID Indicator Category_type Profit_amount
100 1 USA 0
100 0 USA 120
101 1 Brazil 0
101 0 Brazil 0
107 1 USA 0
107 0 USA 75

I would appreciate getting any suggestions as to how to approach and resolve the problem.

Report message to a moderator

Re: Using (Conditional Logic) decode within decode [message #8476 is a reply to message #8472]

Fri, 22 August 2003 18:46

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You are receiving the error message because you forgot a comma where indicated below. However, your select statement bears no resemblance to your desired output. You are selecting a and b and want id and indicator and so forth. In the future, it would help if you would provide complete matching information, such as the actual table structure and column names, the full query including the join conditions and so forth.

select a, b, 
decode(table1.indicator, '0', decode (table2.categroy_type, 'USA',sum( table.profit_amount), 'None')
<b>, -- forgot this comma</b>
 '0')
from table1, table2, ... table(n)
where
--joins--
Group By
indicator,
category_type,
a,
b;

Report message to a moderator

Previous Topic:	problem with nclob
Next Topic:	question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 04:27:47 CDT 2024