Using the IF Condition in SQL with an Oracle Database
The conditional retrieval of data is a typical requirement. You retrieve the content and make some decisions on that data in your code before using it. This is a two step process. With an Oracle database, you can achieve this in one step with a single SQL statment. The statement uses the DECODE function, which gives the equivalent of if - elseif - elseif - endif block.
SELECT DECODE(min(balance),0,0,max(balance)) from tblBankBal
First, the value of min(balance) (the first argument) is computed. It is then checked with zero(0)(the second argument). That means if (min(balance) == 0), then the third argument is returned. If they are not equal then the fourth argument is returned, in this case it will be the max(balance).
<
It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com. Already a member?
To become a member of DevX.com create your Member Profile by completing the form below. Membership is free!
If you have a hot tip and we publish it, we'll pay you. However, due to accounting overhead we no longer pay $10 for a single tip submission. You must accumulate 10 acceptable tips to receive payment. Be sure to include a clear explanation of what the technique does and why it's useful. If it includes code, limit it to 20 lines if possible. Submit your tip here.
