-1

I faced with obscure code on PL/SQL in my job, please, could you explain for me, what does that mean?

PL/SQL execute 'select' request e.g.

DECODE(SIGN(ABS(SUM(amount))) - :3, -1, 0, SUM(AMOUNT)),

What is the ' - :3'? What is the function of?

Mykhailo Marufenko
  • 61
  • 6
  • 1
    There is no DECODE function in MySql. Is that Oracle PL/SQL ? If yes, then `:3` is a name of a bind variable. – krokodilko Apr 13 '15 at 15:16
  • Yes, it is PL SQL code. It calls up simply sql 'select' request and use in request this bind varible. But what it bind??? I don't have another ':3' in this request or in the remaining code? How I can find which number (amount) this code use in this olace? – Mykhailo Marufenko Apr 13 '15 at 15:22
  • 3
    Please edit the question to show the whole SQL string, and the way it's being run with `execute immediate` - that will make it easier to explain what the bind variable represents and where its actual run-time value is coming from. – Alex Poole Apr 13 '15 at 15:26
  • 1
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS631 –  Apr 13 '15 at 17:35

1 Answers1

2

This can be used as a good example of how to break down a confusing line of code to figure out what is going on. You didn't include the entire query, so some things will have to be taken out of context. Firstly, add some spaces to clarify the individual nested functions and make it more readable for now:

DECODE(  SIGN( ABS( SUM(amount)  )  ) - :3, -1, 0, SUM(AMOUNT)   ),

Then analyze them inner-most first to understand what is going on:

DECODE(  SIGN( ABS( **SUM(amount)**  )  ) - :3, -1, 0, SUM(AMOUNT)   ),

First sum the column called "amount" that is part of the query that we don't see

DECODE(  SIGN( **ABS( SUM(amount)  )**  ) - :3, -1, 0, SUM(AMOUNT)   ),

Then get it's absolute value (removes the sign)

DECODE(  **SIGN( ABS( SUM(amount)  ) )** - :3, -1, 0, SUM(AMOUNT)   ),

Then return a value indicating the sign. If < 0 return -1, or if = 0 then return 0 and if > 0 return 1. It can only ever be 0 or 1 due to the ABS() function though. In effect, it seems to be testing if the value is 0 or not.

DECODE(  **SIGN( ABS( SUM(amount)  ) ) - :3**, -1, 0, SUM(AMOUNT)   ),

From that, subtract whatever value was passed in the 3rd bind variable (others have given info on bind variables). This is the expression that will be tested in the decode statement.

If the value is -1, return 0, else return the sum of the amount column.

Some things don't seem to make sense, like getting the absolute value which strips the sign, then calling the sign() function. However, without specs and the full select which would show what type of value :3 is it is hard to know what the original intention was. I suspect this is why you are here, as you are the lucky one tasked with figured out why this is not working as expected. As you figure it out, add to a comment in the code to both clarify your thinking and to help the next person.

Good luck!

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • I don't write code, I should test them - and your answer - is usefull. thank you. I can't copy whole code - because it protected the rights of the company. – Mykhailo Marufenko Apr 14 '15 at 09:24