Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;