Hello community,
I'm trying to convert Oracle PL / SQL to regular T-SQL
The Oracle Code is as follows:
SELECT m.ce_case_data_id, -- AS case_data_id
m.cd_reference ,
m.ce_created_date, -- AS CASE_CREATED_DATE
1 AS created ,
0 AS submitted ,
0 AS examined ,
0 AS stopped,
0 AS issued ,
0 AS issued_in_20days ,
p.ce_app_type ,
p.ce_app_sub_date ,
p.ce_reg_location ,
p.ce_will_exists ,
p.ce_iht_gross_value ,
p.ce_iht_net_value ,
p.ce_deceased_dod ,
p.ce_deceased_other_names ,
m.ce_state_id, -- AS latest_state_id
m.ce_state_name, -- AS latest_state_name
sysdate, -- AS bi_last_updated_date
sysdate, -- AS bi_created_date
p.ce_gor_case_type,
p.ce_paperform_ind,
0 AS issued_in_7wdays,
p.ce_leg_record_id,
p.ce_grantissued_date
FROM v_ccd_probate_metadata m
INNER JOIN
(SELECT ce_case_data_id ,
MIN(ce_id) AS first_event_id
FROM v_ccd_probate_metadata
WHERE ce_case_type_id = 'GrantOfRepresentation'
GROUP BY ce_case_data_id
) f
ON f.first_event_id = m.ce_id
INNER JOIN stg_ccd_probategrant p
ON p.case_metadata_event_id = m.ce_id
WHERE m.ce_case_type_id = 'GrantOfRepresentation'
AND NOT EXISTS
(SELECT 1 FROM tbl_probate_case t WHERE t.case_data_id = m.ce_case_data_id
) ;
COMMIT;
-- Derive/Apply Transformations and Update TBL_PROBATE_CASE after each Incremental Load -
-- Update Submitted Flag and Case Submitted Date columns -
MERGE INTO tbl_probate_case trg
USING ( SELECT m.ce_case_data_id
, MIN(m.ce_created_date) AS case_submitted_date
FROM v_ccd_probate_metadata m
WHERE m.ce_case_type_id = 'GrantOfRepresentation'
AND m.ce_event_id IN ('applyforGrantPaperApplication', 'paymentSuccessApp', 'createCase', 'paymentSuccessCase', 'createCasePaymentSuccess', 'boImportGrant', 'applyForGrant')
And m.ce_state_id In ('CaseCreated', 'BOCaseImported', 'PAAppCreated')
GROUP BY m.ce_case_data_id
) Src
ON ( src.ce_case_data_id = trg.case_data_id )
WHEN MATCHED THEN UPDATE
SET trg.case_submitted_date = NVL(trg.ce_app_sub_date, src.case_submitted_date)
, Trg.Submitted = 1
, trg.bi_last_updated_date = SYSDATE;
My Attempt at converting the code to T-SQL is as follows:
SELECT m.ce_case_data_id, -- AS case_data_id
m.cd_reference ,
m.ce_created_date, -- AS CASE_CREATED_DATE
1 AS created ,
0 AS submitted ,
0 AS examined ,
0 AS stopped,
0 AS issued ,
0 AS issued_in_20days ,
p.ce_app_type ,
p.ce_app_sub_date ,
p.ce_reg_location ,
p.ce_will_exists ,
p.ce_iht_gross_value ,
p.ce_iht_net_value ,
p.ce_deceased_dod ,
p.ce_deceased_other_names ,
m.ce_state_id, -- AS latest_state_id
m.ce_state_name, -- AS latest_state_name
getutcdate(), -- AS bi_last_updated_date
getutcdate(), -- AS bi_created_date
p.ce_gor_case_type,
p.ce_paperform_ind,
0 AS issued_in_7wdays,
p.ce_leg_record_id,
p.ce_grantissued_date
FROM v_ccd_probate_metadata m
INNER JOIN
(SELECT ce_case_data_id ,
MIN(ce_id) AS first_event_id
FROM v_ccd_probate_metadata
WHERE ce_case_type_id = 'GrantOfRepresentation'
GROUP BY ce_case_data_id
) f
ON f.first_event_id = m.ce_id
INNER JOIN stg_ccd_probategrant p
ON p.case_metadata_event_id = m.ce_id
WHERE m.ce_case_type_id = 'GrantOfRepresentation'
-- AND NOT EXISTS
-- (SELECT 1 FROM tbl_probate_case t WHERE t.case_data_id = m.ce_case_data_id
-- ) ;
MERGE INTO tbl_probate_case trg
USING ( SELECT m.ce_case_data_id
, MIN(m.ce_created_date) AS case_submitted_date
FROM v_ccd_probate_metadata m
WHERE m.ce_case_type_id = 'GrantOfRepresentation'
AND m.ce_event_id IN ('applyforGrantPaperApplication', 'paymentSuccessApp', 'createCase', 'paymentSuccessCase', 'createCasePaymentSuccess', 'boImportGrant', 'applyForGrant')
And m.ce_state_id In ('CaseCreated', 'BOCaseImported', 'PAAppCreated')
GROUP BY m.ce_case_data_id
) Src
ON ( src.ce_case_data_id = trg.case_data_id )
WHEN MATCHED THEN UPDATE
SET trg.case_submitted_date=CASE WHEN trg.ce_app_sub_date IS NULL THEN src.case_submitted_date ELSE trg.ce_app_sub_date END
, Trg.Submitted = 1
, trg.bi_last_updated_date = GETUTCDATE();
The problem is I'm getting the following error with T-SQL
Invalid column name 'case_submitted_date'.
I know why I'm getting the error with T-SQL, but I wanted to know why I'm not getting the error with PL /SQL