Home » SQL & PL/SQL » SQL & PL/SQL » Using Minus operator to get the changed data info between two tables (11)
Using Minus operator to get the changed data info between two tables [message #577395] Thu, 14 February 2013 13:17 Go to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
I am using the below query to show the difference of data between two tables using minus operator.

teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.

select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
CURRENT_STG_GLACCTS.GLSUBACCT,
CURRENT_STG_GLACCTS.GLACCTDESC,
CURRENT_STG_GLACCTS.GLACCT5,
CURRENT_STG_GLACCTS.GLACCT6
FROM CURRENT_Day_data_GLACCTS
minus
select PREVIOUS_STG_GLACCTS.TABLE_NAME,
PREVIOUS_STG_GLACCTS.ACTIVE,
PREVIOUS_STG_GLACCTS.BUSINESSUNITID,
PREVIOUS_STG_GLACCTS.COST_CENTER,
PREVIOUS_STG_GLACCTS.GLACCT,
PREVIOUS_STG_GLACCTS.GLSUBACCT,
PREVIOUS_STG_GLACCTS.GLACCTDESC,
PREVIOUS_STG_GLACCTS.GLACCT5,
PREVIOUS_STG_GLACCTS.GLACCT6
FROM PREVIOUS_Day_Data_GLACCTS

Thanks a lot for teh helpful info.
Re: Using Minus operator to get the changed data info between two tables [message #577396 is a reply to message #577395] Thu, 14 February 2013 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.

how to you differentiate between New & Updated rows?
post SQL & results
Re: Using Minus operator to get the changed data info between two tables [message #577399 is a reply to message #577396] Thu, 14 February 2013 13:40 Go to previous messageGo to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
By checking in the Previous_Day_data_GLACCTS table, if a row already exiting and the data is changed, then want to have a flag showing "U"

If a row didn't exist in Previous_Day_data_GLACCTS table, and now it is in current_day_data_GLAccts, then it can be assumed as new row with flag "N"

Thank you.
Re: Using Minus operator to get the changed data info between two tables [message #577400 is a reply to message #577399] Thu, 14 February 2013 13:47 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
Oh My!
It appears that the Data Architect did not believe in Normalizing the data.
Re: Using Minus operator to get the changed data info between two tables [message #577402 is a reply to message #577400] Thu, 14 February 2013 14:02 Go to previous messageGo to next message
cplusplus1
Messages: 40
Registered: October 2012
Location: usa
Member
Wow....
Re: Using Minus operator to get the changed data info between two tables [message #577454 is a reply to message #577402] Fri, 15 February 2013 16:05 Go to previous messageGo to next message
Bill B
Messages: 1083
Registered: December 2004
Senior Member
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
CURRENT_STG_GLACCTS.GLSUBACCT,
CURRENT_STG_GLACCTS.GLACCTDESC,
CURRENT_STG_GLACCTS.GLACCT5,
CURRENT_STG_GLACCTS.GLACCT6,'N' flag
FROM CURRENT_Day_data_GLACCTS
minus
select PREVIOUS_STG_GLACCTS.TABLE_NAME,
PREVIOUS_STG_GLACCTS.ACTIVE,
PREVIOUS_STG_GLACCTS.BUSINESSUNITID,
PREVIOUS_STG_GLACCTS.COST_CENTER,
PREVIOUS_STG_GLACCTS.GLACCT,
PREVIOUS_STG_GLACCTS.GLSUBACCT,
PREVIOUS_STG_GLACCTS.GLACCTDESC,
PREVIOUS_STG_GLACCTS.GLACCT5,
PREVIOUS_STG_GLACCTS.GLACCT6,'N' flag
FROM PREVIOUS_Day_Data_GLACCTS
union all
select PREVIOUS_STG_GLACCTS.TABLE_NAME,
PREVIOUS_STG_GLACCTS.ACTIVE,
PREVIOUS_STG_GLACCTS.BUSINESSUNITID,
PREVIOUS_STG_GLACCTS.COST_CENTER,
PREVIOUS_STG_GLACCTS.GLACCT,
PREVIOUS_STG_GLACCTS.GLSUBACCT,
PREVIOUS_STG_GLACCTS.GLACCTDESC,
PREVIOUS_STG_GLACCTS.GLACCT5,
PREVIOUS_STG_GLACCTS.GLACCT6,'U' flag
FROM PREVIOUS_Day_Data_GLACCTS
minus
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
CURRENT_STG_GLACCTS.GLSUBACCT,
CURRENT_STG_GLACCTS.GLACCTDESC,
CURRENT_STG_GLACCTS.GLACCT5,
CURRENT_STG_GLACCTS.GLACCT6,'U' flag
FROM CURRENT_Day_data_GLACCTS
Re: Using Minus operator to get the changed data info between two tables [message #577462 is a reply to message #577454] Sat, 16 February 2013 06:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1994
Registered: January 2010
Senior Member
Wrong! Set operations have same precedence. Therefore your code will take table 1 and minus table 2. Then it will union all table 2 to result and only then minus table1. What you want is:

 select  *
   from  table1
minus
 select  *
   from  table2
union all
(
  select  *
    from  table2
 minus
  select  *
    from  table1
)
/


Or better:

with t1 as (
             select  *
               from  table1
            minus
             select  *
               from  table2
           ),
     t2 as (
             select  *
               from  table2
            minus
             select  *
               from  table1
           )
select  t1.*
        'Not present in table2'
  from  t1
union all
select  t2.*
        'Not present in table1'
  from  t1
/


Also, since MINUS returns unique rows the above doesn't account for same row being in both tables but different number of times. If this is important:

with t1 as (
             select  a.*,
                     row_number() over(partition by col1,...,coln order by 1) rn
               from  table1 a
            minus
             select  a.*,
                     row_number() over(partition by col1,...,coln order by 1) rn
               from  table2 a
           ),
     t2 as (
             select  a.*,
                     row_number() over(partition by col1,...,coln order by 1) rn
               from  table2 a
            minus
             select  a.*,
                     row_number() over(partition by col1,...,coln order by 1) rn
               from  table1 a
           )
select  t1.*
        'Not present in table2'
  from  t1
union all
select  t2.*
        'Not present in table1'
  from  t1
/



Or without MINUS:

with t1 as (
             select  a.*,
                     1 weight
               from  table1 a
            union all
             select  a.*,
                     -1 weight
               from  table2 a
           ),
     t2 as (
            select  a.*,
                    sum(weight) over(partition by col1,...,coln) weight
              from  t1
           )
select  distinct col1,
                 col2,
                 coln,
                 case sign(weight)
                   when 1 then 'Not present in table2 ' || weight || ' times.'
                   else 'Not present in table1 ' || abs(weight) || ' times.'
                 end diff
  from  t2
  where weight != 0
/


SY.
P.S. I whipped up the above code without testing, so there could be errors. But it provides general idea.

[Updated on: Sat, 16 February 2013 06:26]

Report message to a moderator

Re: Using Minus operator to get the changed data info between two tables [message #577468 is a reply to message #577462] Sat, 16 February 2013 07:47 Go to previous message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hello,

What is the primary key of the tables involved ?

Regards,

Emmanuel
Previous Topic: CLOB Column Append
Next Topic: How to delete data without disable constraints
Goto Forum:
  


Current Time: Fri Aug 22 05:51:10 CDT 2014

Total time taken to generate the page: 0.10439 seconds