SQL: How to check a value in column is equal to the sum of any combination of the other columns in a row

Question

I came across a scenario where I have a table structure as such:

Column0 Column1 Column2 Column3 Column4 Column5 
100     U       V       X       Y       Z

I need to find if:

• 100 = U, V, X, Y, or Z.
• 100 = X + Y + Z + V
• 100 = X + Y
• 100 = V + Z
• etc.

Any suggestions of how I can achieve this?

Case statements will take forever to write. I do not write PL/SQL code but I am familiar with it somewhat.

Answer 1

Writing the Case statement isn't as hard as you're implying. I generated the following Case statement using a text editor in about 20 seconds, which should handle all situations in your example:

Select  Case
            When Column0 = Column1  Then 1
            When Column0 = Column2  Then 1
            When Column0 = Column3  Then 1
            When Column0 = Column4  Then 1
            When Column0 = Column5  Then 1

            When Column0 = Column1 + Column2    Then 1
            When Column0 = Column1 + Column3    Then 1
            When Column0 = Column1 + Column4    Then 1
            When Column0 = Column1 + Column5    Then 1

            When Column0 = Column2 + Column3    Then 1
            When Column0 = Column2 + Column4    Then 1
            When Column0 = Column2 + Column5    Then 1

            When Column0 = Column3 + Column4    Then 1
            When Column0 = Column3 + Column5    Then 1

            When Column0 = Column4 + Column5    Then 1

            When Column0 = Column1 + Column2 + Column3  Then 1
            When Column0 = Column1 + Column2 + Column4  Then 1
            When Column0 = Column1 + Column2 + Column5  Then 1

            When Column0 = Column1 + Column3 + Column4  Then 1
            When Column0 = Column1 + Column3 + Column5  Then 1

            When Column0 = Column1 + Column4 + Column5  Then 1

            When Column0 = Column2 + Column3 + Column4  Then 1
            When Column0 = Column2 + Column3 + Column5  Then 1

            When Column0 = Column2 + Column4 + Column5  Then 1

            When Column0 = Column3 + Column4 + Column5  Then 1

            When Column0 = Column1 + Column2 + Column3 + Column4    Then 1
            When Column0 = Column1 + Column2 + Column3 + Column5    Then 1

            When Column0 = Column1 + Column3 + Column4 + Column5    Then 1

            When Column0 = Column2 + Column3 + Column4 + Column5    Then 1

            When Column0 = Column1 + Column2 + Column3 + Column4 + Column5  Then 1

            Else 0
        End As SumOfOtherColumns
From    YourTable

Answer 2

Here I wrote some codes that you can use it dynamically

1-Firstly I create table and generate one row for it

create table my_table ( column0 number, column1 number, column2 number, 
                        column3 number, column4 number, column5 number );

INSERT INTO my_table values (100,20,20,10,40,10);

2-create type as following lines

create or replace type Key_Value_Typ as object
(

  column_name   varchar2(100),
  column_val    varchar2(100)  
)

3-you can see an example here

declare
  lv_count number;
  lv_row   my_table%rowtype;

  type my_list is table of Key_Value_Typ;
  list1 my_list;

  ------------------------------------------------------------

  -- function for control column condition

  function check_fun(
    lv_list my_list, 
      -- the function input is array of key value include column name and column value
      -- you can implement your code here, for example as input parameter declare your own parameters 
    -- for example
    --   lv_column0 my_table.column0%type;
    lv_where varchar2
    -- the condition that you want check it
    ) return number is

    lv_str   varchar2(200);
    lv_count number;
  begin

    lv_str := lv_where;

    for i in 1 .. lv_list.count Loop

      lv_str := replace(lv_str,
                        lv_list(i).column_name,
                        lv_list(i).column_val);

    ENd Loop;

    execute immediate 'select count(*) from dual where ' || lv_str
      into lv_count;

    -- if the function return 1 result is true and o means result is false
    return lv_count;

  end check_fun;

  ------------------------------------------------------------

begin
  -- fetch data from my_table and get one of them 
  select * into lv_row from my_table where column0 = 100;

  -- create list of data include columns and values
  list1 := my_list(Key_Value_Typ('column0', lv_row.column0),
                   Key_Value_Typ('column1', lv_row.column1),
                   Key_Value_Typ('column2', lv_row.column2),
                   Key_Value_Typ('column3', lv_row.column3),
                   Key_Value_Typ('column4', lv_row.column4),
                   Key_Value_Typ('column5', lv_row.column5));


  lv_count := check_fun(list1, 'column0=column1+column2+column3+column4+column5');

  dbms_output.put_line('result ()' || lv_count);

end;

Answer 3

Even if the answer has already been accepted, here is my alternative solution. It is, most probably way worse in terms of performance ( to be tested ) But I thought it was worth showing for the following reasons :

As you can see, with 5 columns it's already easy to miss some cases and there are only 31 cases to check. this goes to 63checks if you add a column, 127 for the next one ... you dont have to worry about that here because it will generate all the cases dynamically

The other interesting point is that, if you are interested in seeing the detail of each row and why it matched, it comes free with the query. you just have to select the subview

The last point is, I think it's academically interesting. The solution contains UNPIVOTing the data, self joining recursively, on the fly dynamic expression evaluation. Well of course, i'm not objective but hell that was fun to do :)

--Table and data to test the query
create table my_table ( column0 number, column1 number, column2 number, 
                        column3 number, column4 number, column5 number );

INSERT INTO my_table values (100,20,20,10,40,10); -- must match on the sum of 5 columns
INSERT INTO my_table values (100,50,200,300,150,250); -- must not match
INSERT INTO my_table values (100,50,50,100,150,250); -- must match twice ( on col1+col2 and col3 )

-- If your table has a unique key, you can remove the datas_with_id and put
-- your table directly in the unpivoted_data subquery
with datas_with_id  as ( select rowid as row_id, t.* from my_table t),
     unpivoted_data as ( select row_id, column0 as sum_to_check, column_name, column_value 
                         from datas_with_id 
                         unpivot ( column_value for column_name in (column1,column2,column3,column4,column5))),
     calculated_sum as ( select row_id, xmlquery(sys_connect_by_path(u.column_value,'+')||' = '|| sum_to_check
                         returning content).getStringVal() result
                         from unpivoted_data u connect by nocycle prior column_name>column_name 
                         and prior row_id=row_id and level < 6)
select * from my_table where rowid in ( select row_id from calculated_sum where result = 'true' )

If you want to add another column, add it in the unpivot clause, add 1 to the level and you are good

and if you add sys_connect_by_path(u.column_name,'+')||' = '|| sum_to_check in the calculated_sum, you can see every formula that matched