There is a table that contains values that are used in formulas. There are simple variables, that do not contain any expression, and also there are some variables that combined from simple variables into formula. I need to figure out if is it possible to do a SELECT query to get a readable formula based on aliases it contains. Each of these aliases could be used in other formulas.
Let's say that there are two tables:
ITEM TABLE
| ID | Name | FORMULA_ID |
|---|---|---|
| 1 | Item name 1 | f_3 |
| 2 | Item name 2 | f_26 |
FORMULA TABLE
| ID | EXPRESSION | ALIASE | NAME |
|---|---|---|---|
| f_1 | null | var_100 | Ticket |
| f_2 | null | var_200 | Amount |
| f_3 | var_100 * var_200 | var_300 | Some description |
So is there any chance to query, with result like:
| ITEM_NAME | READABLE_EXPRESSION |
|---|---|
| Item name 1 | Ticket * Amount |
CodePudding user response：
Try this:
with items(ID,Name,Formula_Id) AS (
select 1, 'Item name 1', 'f_3' from dual union all
select 2, 'Item name 2', 'f_26' from dual
),
formulas (ID, EXPRESSION, ALIAS, NAME) as (
select 'f_1', null, 'var_100', 'Ticket' from dual union all
select 'f_2', null, 'var_200', 'Amount' from dual union all
select 'f_3', 'var_100 * var_200', 'var_300', 'Some description' from dual
),
rnformulas (id, EXPRESSION, ALIAS, NAME, rn) as (
select fm.*, row_number() over(order by id) as rn from formulas fm
),
recsubstitute( lvl, item_id, rn, expression ) as (
select 1, it.id, 0, fm.expression
from items it
join rnformulas fm on it.formula_id = fm.id
union all
select lvl 1, item_id, fm.rn, replace(r.expression, fm.alias, fm.name)
from recsubstitute r
join rnformulas fm on instr(r.expression, fm.alias) > 0 and fm.rn > r.rn
)
select item_id, expression from (
select item_id, expression, row_number() over(partition by item_id order by lvl desc, rn asc) as rn
from recsubstitute
)
where rn = 1
;
ITEM_ID EXPRESSION
---------- ------------------------------------------------------------
1 Ticket * Amount
Note that it's far to be bullet proof against all situations, especially recursion in the aliases.
CodePudding user response：
Some improvement with another set of data:
with items(ID,Name,Formula_Id) AS (
select 1, 'Item name 1', 'f_3' from dual union all
select 2, 'Item name 2', 'f_4' from dual
),
formulas (ID, EXPRESSION, ALIAS, NAME) as (
select 'f_1', null, 'var_100', 'Ticket' from dual union all
select 'f_2', null, 'var_200', 'Amount' from dual union all
select 'f_3', 'var_100 * var_200', 'var_300', 'Some description' from dual union all
select 'f_4', 'var_300', null, 'Other description' from dual
),
rnformulas (id, EXPRESSION, ALIAS, NAME, rn) as (
select fm.*, row_number() over(order by id) as rn from formulas fm
),
recsubstitute( lvl, item_id, rn, expression ) as (
select 1, it.id, 0, fm.expression
from items it
join rnformulas fm on it.formula_id = fm.id
union all
select lvl 1, item_id, fm.rn, replace(r.expression, fm.alias, nvl(fm.expression,fm.name))
from recsubstitute r
join rnformulas fm on instr(r.expression, fm.alias) > 0
)
select item_id, expression from (
select item_id, expression, row_number() over(partition by item_id order by lvl desc, rn asc) as rn
from recsubstitute
)
where rn = 1
;
1 Ticket * Amount
2 Ticket * Amount
CodePudding user response：
Split the space-delimited formulas into rows. Join the expression parts to the aliases and replace the alias with the name. Join this to the item_table using LISTAGG to concatenate the rows back into a single column.
WITH formula_split AS (
SELECT DISTINCT ft.id
,level lvl
,regexp_substr(ft.expression,'[^ ] ',1,level) expression_part
FROM formula_table ft
CONNECT BY ( ft.id = ft.id
AND level <= length(ft.expression) - length(replace(ft.expression,' ')) 1 ) START WITH ft.expression IS NOT NULL
),readable_tbl AS (
SELECT ft.id
,ft.lvl
,replace(ft.expression_part,ftn1.aliase,ftn1.name) readable_expression
FROM formula_split ft
LEFT JOIN formula_table ftn1 ON ( ft.expression_part = ftn1.aliase )
)
SELECT it.name item_name
,LISTAGG(readable_expression,' ') WITHIN GROUP(ORDER BY lvl) readable_expression
FROM item_table it
JOIN readable_tbl rt ON ( it.formula_id = rt.id )
GROUP BY it.name
CodePudding user response：
- With sample data create CTE (calc_data) for modeling
WITH
items (ITEM_ID, ITEM_NAME, FORMULA_ID) AS
(
Select 1, 'Item name 1', 'f_3' From Dual Union All
Select 2, 'Item name 2', 'f_26' From Dual
),
formulas (FORMULA_ID, EXPRESSION, ALIAS, ELEMENT_NAME) AS
(
Select 'f_1', null, 'var_100', 'Ticket' From Dual Union All
Select 'f_2', null, 'var_200', 'Amount' From Dual Union All
Select 'f_3', 'var_100 * var_200', 'var_300', 'Some description' From Dual
),
calc_data AS
( SELECT e.ITEM_NAME, e.FORMULA_ID, e.FORMULA, e.X, e.OPERAND, e.Y,
ROW_NUMBER() OVER(Partition By e.ITEM_NAME Order By e.FORMULA_ID) "RN", f.ELEMENT_NAME
FROM( Select CAST('.' as VARCHAR2(32)) "FORMULA", i.ITEM_NAME, f.FORMULA_ID,
SubStr(Replace(f.EXPRESSION, ' ', ''), 1, InStr(Replace(f.EXPRESSION, ' ', ''), '*') - 1) "X",
CASE
WHEN InStr(f.EXPRESSION, ' ') > 0 THEN ' '
WHEN InStr(f.EXPRESSION, '-') > 0 THEN '-'
WHEN InStr(f.EXPRESSION, '*') > 0 THEN '*'
WHEN InStr(f.EXPRESSION, '/') > 0 THEN '/'
END "OPERAND",
--
SubStr(Replace(f.EXPRESSION, ' ', ''), InStr(Replace(f.EXPRESSION, ' ', ''), '*') 1) "Y"
From formulas f
Inner Join items i ON(f.FORMULA_ID = i.FORMULA_ID)
) e
Inner Join formulas f ON(f.FORMULA_ID <> e.FORMULA_ID)
)
- Main SQL with MODEL clause
SELECT ITEM_NAME, FORMULA
FROM ( SELECT *
FROM calc_data
MODEL
PARTITION BY (ITEM_NAME)
DIMENSION BY (RN)
MEASURES (X, OPERAND, Y, FORMULA, ELEMENT_NAME)
RULES ( FORMULA[1] = ELEMENT_NAME[1] || ' ' || OPERAND[1] || ' ' || ELEMENT_NAME[2] )
)
WHERE RN = 1
R e s u l t :
| ITEM_NAME | FORMULA |
|---|---|
| Item name 1 | Amount * Ticket |
Just as an option...
The same result without any analytic functions, pseudo columns, unions, etc... - just selecting over and over and over. Not readable, though...
Select
i.ITEM_NAME,
REPLACE( REPLACE( (Select EXPRESSION From formulas Where FORMULA_ID = f.FORMULA_ID),
(Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID),
(Select ELEMENT_NAME From formulas Where FORMULA_ID <> f.FORMULA_ID And ALIAS = (Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) )
) ||
REPLACE( (Select EXPRESSION From formulas Where FORMULA_ID = f.FORMULA_ID),
(Select Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID),
(Select ELEMENT_NAME From formulas Where FORMULA_ID <> f.FORMULA_ID And ALIAS = (Select Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) )
),
(SELECT Max(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID ) || (Select Min(ALIAS) From formulas Where FORMULA_ID <> f.FORMULA_ID) ||
SubStr(f.EXPRESSION, InStr(f.EXPRESSION, ' ', 1, 1), (InStr(f.EXPRESSION, ' ', 1, 2) - InStr(f.EXPRESSION, ' ', 1, 1)) 1 ), ''
) "FORMULA"
From
formulas f
Left Join
items i ON(i.FORMULA_ID = f.FORMULA_ID)
Where i.ITEM_NAME Is Not Null