Oracle SQL Subtract columns with case condition in Apex IG

Hi, @Vinipanda

I see two problems here.

1. When you define an alias in a SELECT clause, you can use that alias in the ORDER BY clause of the same query, but that's the only place you can use it in the same query. If you want to use the same computed value later in the same SELECT clause, then you either have to repeat the computation, or else do the computation in a sub-query; then you can use it wherever you want in the super-query
2. current_kpi and new_kpi are the values passed back by TO_CHAR. TO_CHAR returns a VARCHAR2, but the - operator doesn't work on VARCHAR2s. Use the original NUMBER columns, not the formatted VARCHAR2s, in the - operation.

The following query solves of those problems:

WITH  got_new_kpi_num  AS
(
	SELECT  kpi_1
	,	CASE
		  WHEN dept = 1
		  THEN kpi_2
		  ELSE kpi_3
		END AS new_kpi_num
	FROM 	kpi_data
)
SELECT  TO_CHAR (kpi_1,       '999,999,999,999.99') AS current_kpi
,	TO_CHAR (new_kpi_num, '999,999,999,999.99') AS new_kpi
,	TO_CHAR (kpi_1 - new_kpi_num,
			   '   999,999,999,999.99') AS result 
FROM  got_new_kpi_num
;

If you'd care to post a little sample data (e.g., CREATE TABLE and INSERT statements), then I could test it.