synthetic key issue

Anonymous · ‎2017-11-17

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