2

I Have a table tab_1 with below values.

ID Calculation value
1                10
2                10
3    1+2         
4                 5
5    3-2          
6    5+1

Need help writing the query for following logic. I have a table where the records contain either calculation strings or values to be used in calculations. I need to parse the calculation like this:

ID 3 is the sum of ID 1 and 2.
ID 5 is the minus of ID 3 and 2.
ID 6 is the sum of ID 5 and 1.

Then I need to select the records for the referenced IDs and perform the calculations. My expected output:

ID Calculation value
3    1+2         20
5    3-2         10
6    5+1         20

Thanks -- nani

Improve this question
7
  • use case statement Commented Jan 10, 2018 at 3:39
  • Can you provide the expected result. I could help to find the query Commented Jan 10, 2018 at 4:04
  • @ViKiNG - and exactly how would a CASE statement help solve this problem? Commented Jan 12, 2018 at 7:19
  • @APC.. sorry for late reply. Case means as superuser had already suggested in his answer below? I was thinking on these lines too. Commented Jan 12, 2018 at 8:11
  • 1
    Is it always exactly two two operands? Then it would be way better to store id1, operator, and id2 in separate columns rather than in one string. Commented Jan 12, 2018 at 9:45

2 Answers 2

Reset to default
4

"Need help writing the query for below logic."

This is not a problem which can be solved in pure SQL, because:

  • executing the calculation string requires dynamic SQL
  • you need recursion to look up records and evaluate the results

Here is a recursive function which produces the answers you expect. It has three private procs so that the main body of the function is simple to understand. In pseudo-code:

  1. look up record
  2. if record is value then return it and exit
  3. else explode calculation
  4. recurse 1, 3, 4 for each part of exploded calculation until 2

Apologies for the need to scroll:

create or replace function dyn_calc
     (p_id in number)
      return number
is 
    result number;
    n1 number;
    n2 number;
    l_rec t23%rowtype;
    l_val number;

    type split_calc_r is record (
        val1 number
        , operator varchar2(1)
        , val2 number
    );
    l_calc_rec split_calc_r;

    function get_rec
        (p_id in number)
        return t23%rowtype
    is 
        rv t23%rowtype;
    begin
        select *
        into rv   
        from t23
        where id = p_id;
        return rv;
    end get_rec;    

    procedure split_calc
       (p_calc in varchar2
        , p_n1 out number
        , p_n2 out number
        , p_operator out varchar2)
    is
    begin
         p_n1 := regexp_substr(p_calc, '[0-9]+', 1, 1);
         p_n2 := regexp_substr(p_calc, '[0-9]+', 1, 2);
         p_operator := translate(p_calc, '-+*%01923456789','-+*%'); --regexp_substr(p_calc, '[\-\+\*\%]', 1, 1);
    end split_calc;

    function exec_calc 
        (p_n1 in number
          , p_n2 in number
          , p_operator in varchar2)
        return number
    is 
        rv number;
    begin
        execute immediate
            'select :n1 ' ||  p_operator ||  ' :n2 from dual'
            into rv
            using p_n1, p_n2; 
        return rv;
    end exec_calc;    

begin
    l_rec := get_rec(p_id);

    if l_rec.value is not null then
        result := l_rec.value;
    else        
         split_calc(l_rec.calculation
                     , l_calc_rec.val1
                     , l_calc_rec.val2
                     , l_calc_rec.operator);
         n1 := dyn_calc (l_calc_rec.val1);
         n2 := dyn_calc (l_calc_rec.val2);
         result := exec_calc(n1, n2, l_calc_rec.operator);
    end if;

    return result;
end;
/

Run like this:

SQL>  select dyn_calc(6) from dual;

DYN_CALC(6)
-----------
20

SQL>

or, to get the output exactly as you require:

select id, calculation, dyn_calc(id) as value
from t23
where calculation is not null;

Notes

  • There is no exception handling. If the data is invalid the function will just blow up
  • the split_calc() proc uses translate() to extract the operator rather than regex. This is because regexp_substr(p_calc, '[\-\+\*\%]', 1, 1) mysteriously swallows the -. This appears to be an environment-related bug. Consequently extending this function to process 1+4+2 will be awkward.

Here is a LiveSQL demo.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@viking Thanks. It was one of those things that once I'd wrestled with it for fifteen minutes I had to see it through to the end. I don't get much chance to use recursion in my day job, so this was a welcome exercise.
1

In SQL:

select 'ID ' +ID+ ' is the ' + case when calculation like '%-%' then ' minus '
when calculation like '%+%' then ' sum ' END +' of  
ID'+replace(replace(calculation,'+',' and '),'-',' and ')
from tab_1
where calculation is not null

In Oracle:

select 'ID ' ||ID|| ' is the ' || case when calculation like '%-%' then ' minus '
when calculation like '%+%' then ' sum ' END|| ' of 
ID'||replace(replace(calculation,'+',' and '),'-',' and ')
from tab_1
where calculation is not null
Improve this answer

6 Comments

Thank you sqluser. Incase of ID 5 and 6 this sql is not working.
I just typed without checking it on my SQL . Let me try and paste the exact query for you
Query select 'ID ' +cast(ID as varchar(10))+ ' is the' + case when calculation like '%-%' then ' minus ' when calculation like '%+%' then ' sum ' END +' of ID '+replace(replace(calculation,'+',' and '),'-',' and ') value from test.tab1 where calculation is not null and calculation<>'' OUTPUT- value ID 3 is the sum of ID 1 and 2 ID 5 is the minus of ID 3 and 2 ID 6 is the sum of ID 5 and 1
Thank you, Sorry for the confusion. Added expected output.
Your "IN SQL" part is wrong. First because Oracle uses "SQL" and secondly because in "SQL" (the query language) the string concatenation operator is || not +
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.