Merge 2 tables and then Concatenate to a 3rd table

[KWHITEHURST]

can you pls help, i've got 3 load tables:

1.  System 1 load which details claims at transactional level i.e
   claim no.   Name     TransDate     TransType       Value
  123                AOther  01/01/2024   Reserve            £1,000
  123                AOther  03/01/2024    Paid                 £    500
  345                Test        07/01/2024    Reserve          £1,500
  345                 Test       08/01/204       Paid                 £1,000

2.  system 2 load - this has two tables:

                1.  Claim level i.e.
                         claim no.  Name
                         GW999       MrsP
                         GW777       MrG

                2.  Transactional level i.e
                      Claim No  TransDate   TransType   Amount
                      GW999       20/01/2024    Reserve    £1,750
                       GW999      24/01/2024    Paid           £1,750
                       GW777      16/01/2024    Reserve    £0
                       GW777      14/01/2024     Paid            £500
                       GW777      15/01/2024     Recover    £  75

what i am wanting is the merge system 2 load of two tables into one, which i have done, using right join

then concatenate my merged table onto system 1. load, this part i am struggling with 😞

all fields are named exactly the same

[Marco_Imperiale]

Hi, really don't understand your doubt; the only thing I see, the 3rd table has less fields... but, that's it:

//SYSTEM 1 LOAD EXAMPLE
TMP_LOAD:
LOAD
            AGENCY_NAME,
            CLAIM_NUMBER,
            CLAIM_STATUS,
            CLAIM_TYPE,      
            FINANCIAL_SERVICE_CATEGORY,
            AMOUNT,
            TRANSACTION_TYPE,
            TRANSACTION_DATE,
            POLICYHOLDER_NAME_FN_LN
FROM XXX;

//SYSTEM 2 LOAD EXAMPLE
TMP_GW_MASTERTRANS:
LOAD
    ClaimNumber as CLAIM_NUMBER,
    TransactionDate as TRANSACTION_DATE,
    TransactionType as TRANSACTION_TYPE,
    ClaimTransactionAmount as AMOUNT
FROM YTRANS
Right Join LOAD
    ClaimNumber   as CLAIM_NUMBER ,
    Policyholder as POLICYHOLDER_NAME_FN_LN,
FROM YCLAIMS;

FINAL_TAB:
LOAD
            AGENCY_NAME,
            CLAIM_NUMBER,
            CLAIM_STATUS,
            CLAIM_TYPE,      
            FINANCIAL_SERVICE_CATEGORY,
            AMOUNT,
            TRANSACTION_TYPE,
            TRANSACTION_DATE,
            POLICYHOLDER_NAME_FN_LN
RESIDENT TMP_LOAD;
CONCATENATE
LOAD
    CLAIM_NUMBER,
    TRANSACTION_DATE,
    TRANSACTION_TYPE,
    AMOUNT
    CLAIM_NUMBER ,
    POLICYHOLDER_NAME_FN_LN
RESIDENT TMP_GW_MASTERTRANS;

DROP TABLE TMP_GW_MASTERTRANS;
DROP TABLE TMP_LOAD;