DECODE function in SQL

[evild]

Im not sure where to post this but here is the closest match I can find.

I would like somebody to explain the DECODE function within pl/sql. I have some queries passed on to me by a former employee and I have to continue where he left off.

To me the use of decode seems to be very complicated and I would like some clarification.

For example, I have the following line:

having (sum(decode(z31_credit_debit,'C',z31_sum,0)) -
sum(decode(z31_credit_debit,'D',z31_sum,0)))
< -500

I know that that this is supposed to check for values less than -500. But I am confused with the syntax. 'C' and 'D' are the possible values in the z31_credit_debit field and the z31_sum is an integer.

I dont think i need to explain my db, just need help with getting my head round this decode function

 

[jimbopalmer]

Decode looks at the first field and uses the value of it, then next two fields are a pair the first is a value, the second is a result Decode will return, this pair can be repeated many times, finally there is a default value returned if no other match is made.

z31_credit_debit is the first field, in the first decode, if it is 'C' we return the value z31_sum, since this is the only pair, any other value in z31_credit_debit reurns 0

z31_credit_debit is the first field, in the second decode, if it is 'D' we return the value z31_sum, since this is the only pair, any other value in z31_credit_debit reurns 0

One COULD write a single decode sum(decode(z31_credit_debit,'C',z31_sum,'D', -z31_sum, 0)) to do a similar sum, but it might be even more confusing I tried to remain child-like, all I acheived was childish.

 

[sem]

The main advantage of using DECODE is its speed: you do not need to switch context between pls/sql and sql engines. Regards, Dima