Home » SQL & PL/SQL » SQL & PL/SQL » DEODE function - how many arguments?
DEODE function - how many arguments? [message #184120] Tue, 25 July 2006 08:05 Go to next message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

Hi all,
I am using a rather long decode function in my query:

sum(decode(PREXTERNALID,'LDEV5235', FD.UNITS, 0,'LDEV5230', FD.UNITS, 0,'LDEV5231', FD.UNITS, 0,'LDEV5232', FD.UNITS, 0,'LDEV5233', FD.UNITS, 0,'LDEV5234', FD.UNITS, 0,'LDEV5235', FD.UNITS, 0,'LDEV5236', FD.UNITS, 0,'LDEV5237', FD.UNITS, 0,'LDEV5238', FD.UNITS, 0, 'LDEV5235', FD.UNITS, 0,'LDEV5239', FD.UNITS, 0,'LDEV5281', FD.UNITS, 0))


as you can see it has a lot of arguments and I have just noticed that some of the last ones are being ignored - but if I move them to the front, they are included.
Can anyone tell me what the maximum number of arguments is?

also, is it possible to use decode with a 'LIKE' function (I wouldn't need so many argument then: Like 'LDEV523%' etc)

thanks in advance,
Matt

[Updated on: Tue, 25 July 2006 08:08]

Report message to a moderator

Re: DEODE function - how many arguments? [message #184122 is a reply to message #184120] Tue, 25 July 2006 08:12 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
By adding a bit of formatting:

decode(PREXTERNALID
	,'LDEV5235', FD.UNITS
	, 0,'LDEV5230'
	, FD.UNITS, 0
	,'LDEV5231', FD.UNITS
	, 0,'LDEV5232'
	, FD.UNITS, 0
	,'LDEV5233', FD.UNITS
	, 0,'LDEV5234'
	, FD.UNITS, 0
	,'LDEV5235', FD.UNITS
	, 0,'LDEV5236'
	, FD.UNITS, 0
	,'LDEV5237', FD.UNITS
	, 0,'LDEV5238'
	, FD.UNITS, 0
	, 'LDEV5235', FD.UNITS
	, 0,'LDEV5239'
	, FD.UNITS, 0
	,'LDEV5281', FD.UNITS
	, 0)

I would guess that you might want to alter your decode.

Quote:

also, is it possible to use decode with a 'LIKE' function (I wouldn't need so many argument then: Like 'LDEV523%' etc)
No, but but you can use a searched Case expression.

Jim
Re: DEODE function - how many arguments? [message #184123 is a reply to message #184122] Tue, 25 July 2006 08:14 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
You might make it easy:

sum(decode(substr(PREXTERNALID,1,7)), 'LDEV523',FD.UNITS,0)


Jim
Re: DEODE function - how many arguments? [message #184124 is a reply to message #184120] Tue, 25 July 2006 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe my eyes are bugging out, but I think you may have left out at least one value in your decode because you have multiple results for your search criteria of zero. That won't get you anywhere.

Thanks Jim, the formatting helped, but I am not sure that your answer is correct. It looks like OP has multiple results for zero as opposed to zero being the result for multiple matches. Maybe my eyes are still shleprocked.

[Updated on: Tue, 25 July 2006 08:19]

Report message to a moderator

Re: DEODE function - how many arguments? [message #184126 is a reply to message #184123] Tue, 25 July 2006 08:19 Go to previous message
matpj
Messages: 115
Registered: March 2006
Location: London, UK
Senior Member

JSI2001 wrote on Tue, 25 July 2006 14:14

You might make it easy:

sum(decode(substr(PREXTERNALID,1,7)), 'LDEV523',FD.UNITS,0)


Jim




Genius!
thanks for that, and thanks for the other replies..
Previous Topic: Calculation using Discoverer
Next Topic: sql generating sql
Goto Forum:
  


Current Time: Mon Dec 05 15:22:04 CST 2016

Total time taken to generate the page: 0.17191 seconds