Forum Stats

  • 3,781,628 Users
  • 2,254,532 Discussions


Oracle SQL Subtract columns with case condition in Apex IG

Vinipanda Member Posts: 106 Red Ribbon

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, 
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
    Frank Kulash Member, Moderator Posts: 41,386 Red Diamond
    edited May 26, 2021 3:57PM

    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.