Hi
I have an issue with the synthetic key in data loader.The synthetic key is creating a joining depends on the Field names in the tables & joining those data's.I have used the qualify function to avoid the issue.but its not working out.I have already used the qualify function to create a alias names to the fields.but still its creating a joining, Please suggest?
Below is my query:
qualify AL3.SPONSOR_ID, AL3.SPONSOR_NM, AL4.ROLLED_TO_AMT,AL1.IP,AL4.PROCESSED_DT;
Query1:
SELECT AL3.SPONSOR_ID, AL3.SPONSOR_NM, AL4.ROLLED_TO_AMT,AL1.IP,AL4.PROCESSED_DT, CASE WHEN
AL2.STANDARD_ORGANIZATION_NM = 'FIDELITY INVESTMENTS' OR
AL2.STANDARD_ORGANIZATION_NM = 'FIDELITY INVESTMENTS - PLAN' THEN 'FIDELITY INVESTMENTS' ELSE
AL2.STANDARD_ORGANIZATION_NM END FROM
IDMDBO.V_DIM_RDM_PARTICIPANTS AL1, IDMDBO.V_DIM_DESTINATIONS AL2, IDMDBO.V_DIM_PLANS_ALL AL3,
IDMDBO.V_FCT_DISTRIBUTIONS_ALL AL4 WHERE (AL3.PLAN_ID=AL4.PLAN_ID AND AL1.PARTICIPANT_ID=AL4.PARTICIPANT_ID AND
AL2.DESTINATION_ID=AL4.DESTINATION_ID) AND ((AL4.PROCESSED_DT BETWEEN TIMESTAMP '2017-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND AL4.ROLLED_TO_AMT>0 AND AL3.SPONSOR_ID='753532'))
GROUP BY
AL3.SPONSOR_ID,AL4.ROLLED_TO_AMT,AL1.IP, AL3.SPONSOR_NM,AL4.PROCESSED_DT, CASE WHEN AL2.STANDARD_ORGANIZATION_NM = 'FIDELITY INVESTMENTS'
OR AL2.STANDARD_ORGANIZATION_NM = 'FIDELITY INVESTMENTS - PLAN' THEN 'FIDELITY INVESTMENTS' ELSE
AL2.STANDARD_ORGANIZATION_NM END
;
qualify AL4.SPONSOR_ID,AL4.SPONSOR_NM, AL4.SPONSOR_NM,AL5.ROLLED_TO_AMT,AL5.CASH_OUT_AMT,AL3.ROLLOVER_DESTINATION_TXT, AL5.PROCESSED_MONTH_NM;
Query_2:
SELECT AL4.SPONSOR_ID, AL4.SPONSOR_NM,Sum(
CASE WHEN (AL3.ROLLOVER_DESTINATION_TXT = 'RTC') THEN
( AL5.ROLLED_TO_AMT) ELSE 0 END) , Sum(
CASE WHEN (AL3.ROLLOVER_DESTINATION_TXT = 'RTF') THEN
( AL5.ROLLED_TO_AMT) ELSE 0 END) ,
AL5.ROLLED_TO_AMT,AL5.CASH_OUT_AMT,
AL1.IP, AL2.TRANSACTION_TYPE_CD, AL2.TRANSACTION_TYPE_DSC, AL2.FULL_DISTRIBUTION_IND,
AL3.ROLLOVER_DESTINATION_TXT, AL5.PROCESSED_MONTH_NM, AL5.PROCESSED_DT
FROM
IDMDBO.V_DIM_RDM_PARTICIPANTS AL1,
IDMDBO.V_DIM_TRANSACTION_TYPES AL2, IDMDBO.V_DIM_DESTINATIONS AL3, IDMDBO.V_DIM_PLANS_ALL AL4,
IDMDBO.V_FCT_DISTRIBUTIONS_ALL AL5
WHERE
(AL4.PLAN_ID=AL5.PLAN_ID AND AL5.PARTICIPANT_ID=AL1.PARTICIPANT_ID AND
AL5.TRANSACTION_TYPE_ID=AL2.TRANSACTION_TYPE_ID AND AL5.DESTINATION_ID=AL3.DESTINATION_ID) AND
((AL5.PROCESSED_DT BETWEEN TIMESTAMP '2017-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000'
AND AL4.SPONSOR_ID='753532'))
GROUP BY
AL4.SPONSOR_ID, AL4.SPONSOR_NM, AL1.IP, AL2.TRANSACTION_TYPE_CD,
AL2.TRANSACTION_TYPE_DSC, AL2.FULL_DISTRIBUTION_IND, AL3.ROLLOVER_DESTINATION_TXT,
AL5.PROCESSED_MONTH_NM,AL5.CASH_OUT_AMT,AL5.ROLLED_TO_AMT,
AL5.PROCESSED_DT;
qualify AL5.SPONSOR_ID, AL1.IP, AL1.TERMINATION_DT, AL1.FPRS_STATUS_CD,AL2.ROLLED_TO_AMT;
Query_3:
SELECT AL5.SPONSOR_ID, AL1.IP, AL1.TERMINATION_DT, AL1.FPRS_STATUS_CD,
AL2.PROCESSED_DT, AL2.ROLLED_TO_AMT, Case
when AL4.TRANSACTION_TYPE_ID = 44 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 53 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 54 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 58 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 59 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 62 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 71 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 72 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 74 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 78 then 'Y' else ''
end, Case
when AL4.TRANSACTION_TYPE_ID = 44 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 53 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 54 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 58 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 59 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 62 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 71 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 72 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 74 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 78 then AL4.TRANSACTION_TYPE_DSC
end, AL3.STANDARD_ORGANIZATION_NM, AL1.PLAN_NUM, AL6.EE FROM IDMDBO.V_DIM_RDM_PARTICIPANTS AL1, IDMDBO.V_FCT_DISTRIBUTIONS_ALL AL2, IDMDBO.V_DIM_DESTINATIONS AL3, IDMDBO.V_DIM_TRANSACTION_TYPES AL4, IDMDBO.V_DIM_PLANS_ALL AL5, ADHOCDBO.A401079_DC_ACCT AL6
WHERE
( AL2.TRANSACTION_TYPE_ID = AL4.TRANSACTION_TYPE_ID AND AL2.PARTICIPANT_ID=AL1.PARTICIPANT_ID AND AL3.DESTINATION_ID=AL2.DESTINATION_ID AND AL5.PLAN_ID=AL2.PLAN_ID AND AL6.ACCT_ID=AL1.ACCT_ID)
AND ((AL2.PROCESSED_DT BETWEEN TIMESTAMP '2017-01-01 00:00:00.000' AND TIMESTAMP '2050-12-31 00:00:00.000' AND AL2.ROLLED_TO_AMT>0 AND AL5.SPONSOR_ID='753532'))
GROUP BY
AL5.SPONSOR_ID, AL1.IP, AL1.TERMINATION_DT, AL1.FPRS_STATUS_CD, AL2.PROCESSED_DT,AL2.ROLLED_TO_AMT,
Case
when AL4.TRANSACTION_TYPE_ID = 44 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 53 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 54 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 58 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 59 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 62 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 71 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 72 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 74 then 'Y'
when AL4.TRANSACTION_TYPE_ID = 78 then 'Y' else ''
end, Case
when AL4.TRANSACTION_TYPE_ID = 44 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 53 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 54 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 58 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 59 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 62 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 71 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 72 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 74 then AL4.TRANSACTION_TYPE_DSC
when AL4.TRANSACTION_TYPE_ID = 78 then AL4.TRANSACTION_TYPE_DSC
end, AL3.STANDARD_ORGANIZATION_NM, AL1.PLAN_NUM, AL6.EE;
