SQLTeam.com | Weblogs | Forums

Translate Oracle PL / SQL to T-SQL

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