Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle SQL Subtract columns with case condition in Apex IG

VinipandaMay 26 2021

In my oracle apex interactive grid source query, i have 2 columns.
Both are by default float in DB but need to be represented as comma separated, hence the usage of to_char.
The first column is simple and second is derived via case.
Now i need to subtract these 2 and put result in a 3rd virtual column.
When i write the query something like:

 select to_char(KPI_1,'999,999,999,999.99') CURRENT_KPI, 
case when dept=1 then to_char(KPI_2,'999,999,999,999.99') 
else to_char(KPI_3,'999,999,999,999.99') end as NEW_KPI, 
CURRENT_KPI-NEW_KPI RESULT 
from KPI_DATA;

So here i get error, NEW_KPI is invalid identifier, whether i validate in application or run in oracle database(sql developer 19)
How can i write the query to subtract CURRENT_KPI and the result of NEW_KPI
Oracle 12c Apex 20.2

Comments

Post Details

Added on May 26 2021
1 comment
98 views