Oracle SQL Subtract columns with case condition in Apex IG

Vinipanda

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

Frank Kulash

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.