Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Decode and invalid arguments

Status
Not open for further replies.

shauntwo

Technical User
Jul 9, 2002
64
US
Hi,

I'm trying to use the SQL below to create a summary of the count and sum of contracts submitted either on paper or via the Internet. I'm new to the DECODE function, but because our Internet contracts have a certain Internet code, it's my understanding that using the nested DECODEs below should do the trick. However, I keep getting an "Invalid number of arguments" message, and I'm wondering if my parens are out of place (see the interpretation below):

SELECT fdwx.fdwxz_cost_center.channel_code,
count(decode(fdwx.fdwxh_ew_contract.internet_code, to_number(null),decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1),'_',1,0)),1) "Paper Contracts",
sum(decode(fdwx.fdwxh_ew_contract.internet_code, to_number(null),decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1), '_', dealer_price,0)), dealer_price) "Paper Sales",

count(decode(fdwx.fdwxh_ew_contract.internet_code, to_number(null),0,decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1),'_',0,1)) "Internet Contracts",
sum(decode(fdwx.fdwxh_ew_contract.internet_code, to_number(null),0,decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1),'_',0,dealer_price))) "Internet Sales",

fdwx.fdwxh_ew_contract.fiscal_month, fdwx.fdwxh_ew_contract.fiscal_year

FROM fdwx.fdwxh_ew_contract, fdwx.fdwxz_cost_center, fdwx.fdwxi_madis_cust

WHERE fdwx.fdwxh_ew_contract.fk_fdwxi_dealercus = fdwx.fdwxi_madis_cust.customer number
AND fdwx.fdwxi_madis_cust.cost_center_code = fdwx.fdwxz_cost_center.code
AND fdwx.fdwxh_ew_contract.fiscal_month <= '9'
AND fdwx.fdwxh_ew_contract.fiscal_year = '2002'

GROUP BY fdwx.fdwxz_cost_center.channel_code, fdwx.fdwxh_ew_contract.fiscal_month, fdwx.fdwxh_ew_contract.fiscal_year


Basically, if the Internet Code field is blank or contains an underscore &quot;_&quot; in the code, it's a paper contract; if the code is not null and doesn't contain an underscore, it's an Internet contract. I've been struggling with this for a couple days now, and would much appreciate any help.

Shaun
 
A quick one. I can see a misplaced &quot;)&quot; in your &quot;Paper Contracts&quot; part of your SQL, check also the other columns.

OLD:
count(decode(fdwx.fdwxh_ew_contract.internet_code, to_number(null),
decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1),'_',1,0)
),1)&quot;Paper Contracts&quot;

NEW:
count(decode(fdwx.fdwxh_ew_contract.internet_code,NULL,
decode(substr(fdwx.fdwxh_ew_contract.internet_code, 2, 1),'_',1,0),1)) &quot;Paper Contracts&quot;


Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War
 
Well, I've tried that and a couple of other less-intuitive combinations, and I get either a &quot;missing right parenthesis&quot;, &quot;FROM not ended properly&quot;, or &quot;invalid number of arguments&quot;. Could it be possible that I'm just not using the statements in the right way?

Shaun
 
I think you still have to check structure:

General syntax for DECODE:

DECODE(<variable> ,value1,return1,
,value2,return2,
,......,.......,
default)

So in your case:

COUNT(DECODE(<variable> ,value1,return1,
,value2,return2,
,......,.......,
default))

With nested DECODES:


COUNT(DECODE(<variable> ,value1,DECODE(<variable2>,value21,return21,default),
,value2,return2,
,......,.......,
default))

So , every aggregate line SHOULD end with '))', one for the outer DECODE and one for the aggregate function.

Are you sure that you have the return values specified right? (I see different structures there ....)
T. Blom
Information analyst
tbl@shimano-eu.com
 

I suggest for you to recreate your sql, one column at a time to isolate the problem. For example, write your sql for &quot;Paper Contracts&quot; first then do the others later. You are definitely encountering a common mistake in matching your ')' because you have nested functions, where the only solution is a careful investigation and rewriting of the sql.
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War
 
Well, I noticed part of my problem now - an underscore in one of my field names in the WHERE clause was missing. However, now I've run into another problem. Per your suggestions, I'm looking at my DECODE statements line by line, and it seems as though regardless of whatever criteria I specify in the DECODE, my resultset brings back &quot;everything.&quot; In other words, it seems as though my DECODE criteria is ignored. I would normally specify criteria in the WHERE clause, but why would I need to do that in this case if the DECODE does already?

Thanks for your great help.
Shaun
 

The DECODE does not actually put a criteria on the result set but rather place a workaround on the data retrieved by limiting those data that conforms to the decode condition. I know that your concern here is the count aggregate function, I think whether you put a decode there, everything will still be counted. So I suggest you do a SUM(decode(field, 1, 1, 0)), where if field=1 (satisfies the condition) put a 1 value otherwise 0. This should simulate the counting that you want to put in place.
Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top