I have an interactive grid with fixed rows and need to calculate the formula on pre-rendering.
So the source query is:
select kpi,monthly,yearly from kpi where project_id = :P1_PROJECT_ID;
I need to modify this so that the row where kpi='Gross' is calculated on rendering.
It looks in the grid like:
I am trying to write sql query but it doesn;t work. What am i doing wrong here?
select kpi,
case when KPI='Gross'
then to_char(case when KPI='Profit' then to_number(replace(nvl(monthly,0),',','')) end /
case when KPI='Loss' then to_number(replace(nvl(monthly,0),',','')) end ,'999,999,999,999')
else to_char( monthly,'999,999,999,999') end as monthly,
case when KPI='Gross'
then to_char(case when KPI='Profit' then to_number(replace(nvl(yearly,0),',','')) end /
case when KPI='Loss' then to_number(replace(nvl(yearly,0),',','')) end ,'999,999,999,999')
else to_char( yearly,'999,999,999,999') end as yearly,
from kpi where project_id = :P1_PROJECT_ID;
To_char is used to display values as comma separated. So it would when KPI=Gross, it will divide the columns where kpi=profit by kpi=Loss and dispaly result.
Also the result in the row where KPI=Gros should also have % concatenated.
Apex 20.2
How can this be achieved?