Question 239602 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to select only those columns which have some changes ( more than one distinct value)

I have a transactional database with more than 100 columns. Now if I have to select all the rows but only those columns which have changed down the table (more than one distinct value for a particular column). how can I do it in ORACLE?

With my query, I have to manually write 100 lines to check if there is more than 1 distinct count for a particular column. It was tedious. I need some expertise here and if we have any easy method.

SELECT id,
       CASE WHEN COUNT(DISTINCT column1) > 1 THEN column1 END AS column1,
       CASE WHEN COUNT(DISTINCT column2) > 1 THEN column2 END AS column2,
.
.
.
FROM your_table_name
GROUP BY id
Tags:

2

Answers


  1. 0

    The way I understood it, one option is to create a function which returns refcursor, composed from columns whose values have changed. In order to find them, gather table statistics and check user_tab_columns view’s num_distinct column.

    Sample data: only phone column has changed:

    SQL> select * from test;

        ID NAME   PHONE
---------- ------ ------
         1 Little 123456
         1 Little 555666

    Function has to be an autonomous transaction because DDL (gathering table stats) implicitly commits.

    SQL> create or replace function f_test (par_table_name in varchar2)
  2    return sys_refcursor
  3  is
  4    pragma autonomous_transaction;
  5    rc     sys_refcursor;
  6    l_str  varchar2(10000);
  7  begin
  8    dbms_stats.gather_table_stats(user, par_table_name);
  9
 10    execute immediate
 11      q'[select listagg(column_name, ',') within group (order by column_id)
 12         from user_tab_columns
 13         where table_name = ]' || chr(39) ||
 14           dbms_assert.sql_object_name(par_table_name) || chr(39) ||
 15       '  and num_distinct > 1'
 16    into l_str;
 17
 18    if l_str is not null then
 19       l_str := 'select ' || l_str || ' from ' || par_table_name;
 20       open rc for l_str;
 21       return rc;
 22    else
 23       raise_application_error(-20000, 'No changes in that table''s columns');
 24    end if;
 25  end;
 26  /

Function created.

    Testing:

    SQL> select f_test('TEST') from dual;

F_TEST('TEST')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PHONE
------
123456
555666


SQL>
    Login or Signup to reply.
  2. 0

    I think you can take a look into dynamicSQL queries for this – check out Oracle’s DynamicSQL, something along the lines:

    DECLARE
    v_query VARCHAR2(32767);
BEGIN
    SELECT 'SELECT id, ' || LISTAGG(
        'CASE WHEN COUNT(DISTINCT ' || column_name || ') > 1 THEN ' || column_name || ' END AS ' || column_name,
        ', '
    ) WITHIN GROUP (ORDER BY column_id) || ' FROM your_table_name GROUP BY id'
    INTO v_query
    FROM all_tab_columns
    WHERE table_name = 'YOUR_TABLE_NAME'
    AND owner = 'YOUR_SCHEMA_NAME';

    EXECUTE IMMEDIATE v_query;
END;
/

    Will help your usecase, this generates the dynamic SQL query by querying all tab columns to retrieve the column names for the specified table, it then constructs a query that includes a CASE expression for each column, checking if the distinct count is greater than 1. The generated query is stored in the v_query variable and is executed using EXECUTE IMMEDIATE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search