'Select case if <0 then '0' [duplicate]

Looking for a bit of guidance here. I've got a query performing a balance calculation that is also being formatted for display on a letter we've got going out. I'm looking to display 0 if Total_Billable - Annual_Sum <0. Basically, I want to avoid showing negative numbers for cleanliness. See below:

select TO_CHAR(NVL(:TOTAL_BILLABLE2 - :ANNUAL_SUM2,0),'$999,990')<br>
       into :REMAINING_BALANCE <br>
FROM SPRIDEN <br>
 where SPRIDEN.SPRIDEN_ID = :Student_ID



I feel like I'm VASTLY overthinking where to place my CASE statement, but this is my first go round using variables to calculate a stored value in another variable. Any help would be appreciated!

sqloraclecasesql-insert


Solution 1:[1]

Use a CASE WHEN:

select TO_CHAR(CASE WHEN :TOTAL_BILLABLE2 - :ANNUAL_SUM2<0 
                    THEN 0 
                    ELSE :TOTAL_BILLABLE2 - :ANNUAL_SUM2 END,'$999,990')
       into :REMAINING_BALANCE 
FROM SPRIDEN 
 where SPRIDEN.SPRIDEN_ID = :Student_ID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luuk

Related Questions