I have below tables with sample data: Fiddle Link: https://dbfiddle.uk/kBy8qPoS
Create table Sale (RestaurantId INT, INCLN_SPND VARCHAR2(10 CHAR), SPND_AMT NUMBER(10,2), EXCLN_SPND VARCHAR2(10 CHAR), EXCLN_AMT NUMBER(10,2))
INSERT INTO Sale
Select 111, 'IS01', 333.45, 'EIS01', 23 from dual
union
Select 111, 'DS01', 23.45, 'EDS01', 2 from dual
union
Select 112, 'IS01', 134.45, 'EIS01', 13 from dual
union
Select 112, 'DS01', 34.45, 'EDS01', 2 from dual
Create table Consts(Name VARCHAR(20), Value NUMBER(10,2))
INSERT INTO Consts
Select 'MFR_PCT', 0.3 from dual
union
Select 'DSR_PCT', 0.4 from dual;
Create table Results(RestaurantId INT, Spnd01 VARCHAR(10), SpndExcln01 VARCHAR(10), DISNT01 VARCHAR(10), DISNTExcln01 VARCHAR(10), Fn VARCHAR(200))
INSERT INTO Results
Select 111, 'IS01', 'EIS01', 'DS01', 'EDS01', '(IS01-EIS01) / (DS01 - EDS01) * MFR_PCT' from dual
union
Select 112, 'IS01', 'EIS01', 'DS01', 'EDS01', '(IS01-EIS01) / (DS01 - EDS01) * DSR_PCT' from dual
I want to select results from “Results” table by replacing all the occurrences of constant values with their corresponding amount values which I will be evaluating later.
I want to do this using recursive query as my current solution of using cursor for each record of “Result” table to replace values is proving not very optimized as there can be upto 4 million records. below will be the result of query:
111 | 333.45 | 23 | 23.45 | 2 | (333.45-23) / (23.45 -2) * 0.3
112 | 134.45 | 13 | 34.45 | 2 | (134.45-13) / (34.45-2) * 0.4
I am following one of the answers by @solomon-yakobson but finding it difficult to implement & seeking for some advise here.
Thanks.