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 "_" 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
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 "_" 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