How do I combine multiple tables? (First has data from this month, second has all other previous data)

Question

I am looking to create a query that shows shipping number, the container ID, the tracking number, the location it was last moved to, what time it was moved, and who moved it.

Here's the issue. We recently backed up or transaction history onto another table for anything that's over 30 days old.

So I have the table transaction_history which gives me everything from today to 30 days ago, and I have the table AR_transaction_history, which gives me everything else (starting from 31 days ago.)

I need to be able to create prompts for the user to input either the container ID, tracking number, or shipping ID.

I need help joining the two tables to create 1 table with all the records. I tried union all and it does not work with my prompts. I tried an isnull statement and that didn't work either. Here is the code.

select 
  th.reference_id,
  th.container_id 'Container ID', 
  sc.tracking_number 'Tracking Number',
  max(th.DATE_TIME_STAMP) 'Time of Last Touch', 
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END AS 'User Name',
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.location
  END AS 'Location'
from TRANSACTION_HISTORY th
inner join TRANSACTION_HISTORY th1 on th1.CONTAINER_ID = th.CONTAINER_ID
inner join SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
group by th.container_id, sc.tracking_number, th1.DATE_TIME_STAMP, th1.USER_NAME, th1.LOCATION, th.REFERENCE_ID
Having
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END is not null

UNION ALL

select 
  th.reference_id,
  th.container_id 'Container ID',
  sc.tracking_number 'Tracking Number',
  max(th.DATE_TIME_STAMP) 'Time of Last Touch', 
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END AS 'User Name',
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.location
  END AS 'Location'
from AR_TRANSACTION_HISTORY th
inner join AR_TRANSACTION_HISTORY th1 on th1.CONTAINER_ID = th.CONTAINER_ID
inner join AR_SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
group by th.container_id, sc.tracking_number, th1.DATE_TIME_STAMP, th1.USER_NAME, th1.LOCATION, th.REFERENCE_ID
Having
  CASE
    WHEN th1.date_time_stamp = max(th.DATE_TIME_STAMP) then th1.user_name
  END is not null

What do you mean union all does not work with your prompts... this sql looks fine. What error are you getting? — Hogan, Sep 23 '19 at 20:51
It's not the error that I'm getting, but I have to set up my SQL in IBM COGNOS with a prompt that basically allows someone to input a WHERE statement for either shipmentID, Container ID, or Tracking Number. The problem with the UNION ALL is it will not populate results that are in the second query. The tables are completely identical with respect to their fields-- but the UNION just didn't want to cooperate with the prompt since I can only prompt a field and the UNION just adds the second query to the same field. — Gary Goldsmith, Sep 24 '19 at 11:51

GMB · Accepted Answer · 2019-09-23T20:58:18.103

0

Do UNION ALL in a subquery, and leave the rest of your original query untouched. This is the simplest way to proceed, without reviewing the whole logic of your (aggregated) query.

SELECT
....
FROM
    (
        SELECT * FROM TRANSACTION_HISTORY
        UNION ALL SELECT * FROM AR_TRANSACTION_HISTORY
    ) as th
    INNER JOIN SHIPPING_CONTAINER sc on sc.CONTAINER_ID = th.CONTAINER_ID
GROUP BY ...

Note: in general, SELECT * and UNION ALL do not get along well. This answer assumes that tables TRANSACTION_HISTORY and AR_TRANSACTION_HISTORY have exactly the same structure (columns and data types).

edited Sep 23 '19 at 20:58

answered Sep 23 '19 at 20:53

GMB

216,147
25
84
135

this works but as I look at the query he is only pulling the items with a max(date_time_stamp) so doesn't it make sense to move this filter to the sub-query for optimization? – Hogan Sep 23 '19 at 20:56
@Hogan: yes possibly, and other optimizations might be possible too. I deliberately went for the simplest solution instead of reviewing the whole query logic... – GMB Sep 23 '19 at 21:00
Th union in the from statement worked great. Took 7 seconds so not terrible for the purpose of what I need it for (Operations Managers looking up missing containers.) Hogan, can you further explain where you would put the union? I tried it in the subquerries but didn't think it would perform better. – Gary Goldsmith Sep 24 '19 at 11:41
@GaryGoldsmith -- I looked closer and I don't think it will help if your sql is correct. I will note your sql seems wrong -- you are looking for max on the date_time_stamp field but then also have that field in your group by -- so the max function should have no effect. – Hogan Sep 24 '19 at 12:48
@Hogan, I am looking for the max on th.date_time stamp but am grouping th1.date_time_stamp. I didn't know another way to select the user who touched the container at the last date_time_stamp. Do you have another idea that would perform better than a CASE statement? – Gary Goldsmith Sep 25 '19 at 11:53
@GaryGoldsmith -- don't group on the th1 timestamp -- that is not included in your output. Yes there is a way -- use windowing functions on that field and then select with value 1. Then you don't need a group by at all. This is faster on most platforms and data models. – Hogan Sep 25 '19 at 14:25

How do I combine multiple tables? (First has data from this month, second has all other previous data)

1 Answers1