-2

I want to show the results of both tables on the same querys, the tables are identical but one stores historic information and the other one has today's info (online), my tables look something like this.

Historic Table: TBL1

T_ID            Resolve             Date
----------      ------------        ------------
AD_1234         GOOD                12/18/16
VF_4569         BAD                 12/19/16
RT_5436         GOOD                12/17/16

Online Table: TBL2

T_ID            Resolve             Date
----------      ------------        ------------
AR_2334         BAD                 12/20/16
FT_1362         BAD                 12/20/16
GH_5676         GOOD                12/20/16

Expected Result: TBL3

T_ID            Resolve             Date
----------      ------------        ------------
AD_1234         GOOD                12/18/16
VF_4569         BAD                 12/19/16
RT_5436         GOOD                12/17/16
AR_2334         BAD                 12/20/16
FT_1362         BAD                 12/20/16
GH_5676         GOOD                12/20/16

Can't create new tables, views or any kind of objects it has to be done by query.

I Keep getting duplicate records and I know I dont have the same data on either table.

select o.transaction_id, o.ts_0002
from data_headers1 o
where 
virtual_table_id = '5237260000000002621'
union all
select h.transaction_id, h.ts_0002
from data_headers1_hist h
where virtual_table_id = '5237260000000002621'
 and TO_CHAR(h.ts_0002, 'YYYY/MM/DD HH24:MI:SS') >= '2016/12/01 00:00:00'
 and TO_CHAR(h.ts_0002, 'YYYY/MM/DD HH24:MI:SS') <= '2016/12/30 23:59:59';
Zombraz
  • 152
  • 1
  • 9
  • 2
    you know you have to use `union`..so have you tried it? – Vamsi Prabhala Dec 20 '16 at 23:29
  • Please at least make an attempt and post your code. – John Wu Dec 20 '16 at 23:30
  • **UNION ALL** for god sake – David דודו Markovitz Dec 20 '16 at 23:31
  • You are not going to learn anything from this requested result format. Can you use a better sample to demonstrate what you are looking for? – David דודו Markovitz Dec 20 '16 at 23:33
  • I've tried the Union ALL but it gives me duplicate records, and I know for a fact that both tables dont have the same data. Here is my code.... – Zombraz Dec 20 '16 at 23:38
  • Can the same transaction_id appear in both tables? more than once in the same table? – David דודו Markovitz Dec 20 '16 at 23:44
  • No, Im sure they dont have the same Id on both tables – Zombraz Dec 20 '16 at 23:50
  • And in the same table? – David דודו Markovitz Dec 20 '16 at 23:55
  • No, they are actually Primary keys on both tables, that's why im so confused – Zombraz Dec 20 '16 at 23:58
  • 1
    Right at the end of your query, add `group by transaction_id, ts_002 having count(*) > 1`. This will show which rows are duplicates. Then hunt them down in the two tables - see if they are, in fact, duplicates in one of the tables, or if they are unique in each table but they appear in both tables. Then you'll have to see why a PK constraint is violated (perhaps it is not ENABLED?) or why you may have overlaps between the tables, etc. Try to solve this one small step at a time. –  Dec 21 '16 at 16:43
  • @Mathguy Thank you! It seems that a constraint was not enabled on the historic table, had to change those records and and a simple union all worked for the result i was looking for... One for the noob book - "Do not trust the user". – Zombraz Dec 21 '16 at 21:09
  • 1
    @Zombraz - lol, that is the TITLE of the noob book! As they say, "computers are too dumb to lie." Whenever there is a discrepancy between what the user says and what the computer says, the prime suspect is always the user. –  Dec 21 '16 at 21:24

2 Answers2

0

In the meantime:

You'll have to know the source (TBL1/TBL2) for your data

           select 1 as tab,T_ID,Resolve,"Date" from TBL1 t
union all  select 2       ,T_ID,Resolve,"Date" from TBL2 t

P.s.

Change the condition on h.ts_0002
1. If there are partitions / indexes on h.ts_0002, you are preventing them from being used.
2. Converting h.ts_0002 of all rows to char is a waste of resources.

    h.ts_0002 >= timestamp '2016-12-01 00:00:00' 
and h.ts_0002 <  timestamp '2017-01-01 00:00:00'

P.s. 2 

select      transaction_id
           ,count(*)                            as total
           ,count(case tab when 1 then 1 end)   as tab1
           ,count(case tab when 2 then 1 end)   as tab2

from       (select      1 as tab
                       ,o.transaction_id 
            from        data_headers1 o
            where       virtual_table_id = '5237260000000002621'

            union all

            select      2
                       ,h.transaction_id
            from        data_headers1_hist h
            where       virtual_table_id = '5237260000000002621'
                    and h.ts_0002 >= timestamp '2016-12-01 00:00:00' 
                    and h.ts_0002 <  timestamp '2017-01-01 00:00:00'
            )

group by    transaction_id    

having      count(*) > 1

order by    transaction_id
;
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • Since things do not adds up, please run the query in **P.s. 2** to verify – David דודו Markovitz Dec 21 '16 at 00:03
  • Thank you! Let me give it a try. – Zombraz Dec 21 '16 at 00:07
  • Now it doesnt show me any information, I tried it with the single UNION and it worked, but it took around 45 mins to get 34k records. – Zombraz Dec 21 '16 at 00:32
  • @Zombraz - that doesn't make sense - why did you try with UNION when you want to understand duplicates? UNION removes duplicates so running Dudu's diagnostic query with UNION instead of UNION ALL is just a waste of time. –  Dec 21 '16 at 16:47
0

SQL is a declarative language, simply it means you want the union of some data, just use the term UNION (SQL Server example):

SELECT T_ID, Resolve, Date From Tbl1 
UNION 
SELECT T_ID, Resolve, Date From Tbl2

*the query is not tested.

Note: If you need to show the duplicated results you can replace UNION with UNION ALL.

Mohsen Kamrani
  • 7,177
  • 5
  • 42
  • 66