Help Convert / Translate Oracle SQL to T-SQL

Question

Hello Community,

I have been given a challenge of converting the following SQL code written in Oracle to T-SQL.

INSERT INTO tbl_probate_case  
        (  
          case_data_id ,  
          cd_reference ,  
          case_created_date ,  
          created ,  
          submitted ,  
          examined ,  
          stopped,  
          issued ,  
          issued_in_20days,  
          ce_app_type ,  
          ce_app_sub_date ,  
          ce_reg_location ,  
          ce_will_exists ,  
          ce_iht_gross_value ,  
          ce_iht_net_value ,  
          ce_deceased_dod ,  
          ce_deceased_other_names ,  
          latest_state_id ,  
          latest_state_name ,  
          bi_last_updated_date ,  
          bi_created_date,  
          ce_gor_case_type,  
          ce_paperform_ind,  
          issued_in_7wdays,  
          legacy_case_reference_id,  
          grant_issued_date  
        )  
      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;  
        
      COMMIT;  
              
      -- Update Examined Flag column --  
      MERGE INTO tbl_probate_case trg  
      Using   
        (     
            SELECT m.ce_case_data_id ,  
              MIN(m.ce_created_date) AS examined_date  
            FROM v_ccd_probate_metadata m  
            WHERE m.ce_case_type_id = 'GrantOfRepresentation'  
            AND m.ce_event_id      IN ('boMarkAsReadyForExamination')  
            AND m.ce_state_id      IN ('BOReadyForExamination')  
            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_examined_date = src.examined_date  
      , Trg.Examined = 1  
      , trg.bi_last_updated_date = SYSDATE;    
          
      COMMIT;       
        
      -- Update STOPPED Flag column -  
      --  
      -- create a temp table giving the first STOPPED date for each case:  
      MERGE INTO tbl_probate_case trg  
      Using   
        (     
            SELECT m.ce_case_data_id ,  
              MIN(m.ce_created_date) AS stopped_date  
            FROM v_ccd_probate_metadata m  
            WHERE m.ce_case_type_id = 'GrantOfRepresentation'  
            AND m.ce_event_id IN ('boStopCase', 'boStopCaseForCaseMatchingForExamining', 'boStopCaseForRegistrarEscalations', 'boFailQA', 'boStopCaseForCaseMatching')  
            AND m.ce_state_id IN ('BOCaseStopped')  
            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_stopped_date = src.stopped_date  
      , Trg.stopped = 1  
      , trg.bi_last_updated_date = SYSDATE;  

  COMMIT;  

The following is the table structure that the above code needs to work with and some sample data.

CREATE TABLE v_ccd_probate_metadata (  
    CD_CREATED_DATE datetime2,  
    CD_JURISDICTION nvarchar(50),  
    CD_LAST_MODIFIED datetime2,  
    CD_LAST_STATE_MODIFIED_DATE datetime2,  
    CD_LATEST_STATE nvarchar(50),  
    CD_REFERENCE float,  
    CD_SECURITY_CLASSIFICATION nvarchar(50),  
    CD_VERSION int,  
    CE_CASE_DATA_ID int,  
    CE_CASE_TYPE_ID nvarchar(50),  
    CE_CASE_TYPE_VERSION int,  
    CE_CREATED_DATE datetime2,  
    CE_DESCRIPTION nvarchar(100),  
    CE_EVENT_ID nvarchar(50),  
    CE_EVENT_NAME nvarchar(50),  
    CE_ID int,  
    CE_SECURITY_CLASSIFICATION nvarchar(50),  
    CE_STATE_ID nvarchar(50),  
    CE_STATE_NAME nvarchar(50),  
    CE_SUMMARY nvarchar(100),  
    CE_USER_FIRST_NAME nvarchar(100),  
    CE_USER_ID nvarchar(50),  
    CE_USER_LAST_NAME nvarchar(100),  
    EXTRACTION_DATE datetime2,  
    DL_LOADED_DATETIME datetime2,  
    CE_APP_TYPE nvarchar(50),  
    CE_APP_SUB_DATE date,  
    CE_REG_LOCATION nvarchar(50),  
    CE_WILL_EXISTS nvarchar(50),  
    CE_IHT_NET_VALUE int,  
    CE_IHT_GROSS_VALUE int,  
    CE_DECEASED_DOD date,  
    CE_DECEASED_OTHER_NAMES nvarchar(50),  
    CE_GOR_CASE_TYPE nvarchar(50),  
    CE_PAPERFORM_IND nvarchar(50),  
    CE_GRANTISSUED_DATE nvarchar(50),  
    CE_LEG_RECORD_ID nvarchar(255),  
    CE_LATEST_GRANT_REISSUE_DATE nvarchar(255),  
    CE_REISSUE_REASON nvarchar(255),  
    CE_WELSH_LANG_PREF nvarchar(50),  
    CE_PRIMARY_APPLICANT_ADDRESS nvarchar(255),  
    DW_LOADED_DATETIME nvarchar(255),  
    DW_MODIFIED_DATETIME nvarchar(255),  
    SourceFile date)  
  
INSERT v_ccd_probate_metadata VALUES  
(CONVERT(DATETIME2, '2021-09-01 08:31:56.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-01 08:31:58.0000000', 121),CONVERT(DATETIME2, '2021-09-01 08:31:58.0000000', 121),N'CaseCreated',1630485116072959,N'PUBLIC',1,3290751,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-01 08:31:56.0000000', 121),N'Probate Application created by FT',N'applyForGrant',N'PA application created',22632815,N'PUBLIC',N'PAAppCreated',N'PA application created',N'Probate application',N'******@gmail.com',N'726469',N'******@gmail.com',CONVERT(DATETIME2, '2021-09-02 01:17:26.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Personal',CONVERT(DATETIME, '2020-09-07', 120),N'ctsc',N'',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),N'No',NULL,N'',N'--',NULL,NULL,NULL,N'No',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120)),  
(CONVERT(DATETIME2, '2021-09-07 14:26:29.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-07 14:28:17.0000000', 121),CONVERT(DATETIME2, '2021-09-07 14:28:17.0000000', 121),N'CaseCreated',1631024789519746,N'PUBLIC',3,3323282,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-07 14:27:39.0000000', 121),N'',N'solicitorUpdateAdmon',N'Admon will details',24401673,N'PUBLIC',N'SolAppUpdated',N'Application updated',N'',N'ProbateSolicitor',N'da3fad06-6408-4402-bfcd-dbdc24696b12',N'OrgTest1',CONVERT(DATETIME2, '2021-09-08 01:14:58.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Solicitor',NULL,N'ctsc',N'Yes',10000000,10000100,CONVERT(DATETIME, '2020-01-01', 120),N'No',N'admonWill',N'No',N'--',NULL,NULL,NULL,N'',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120)),  
(CONVERT(DATETIME2, '2021-09-05 21:14:09.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-05 21:17:41.0000000', 121),CONVERT(DATETIME2, '2021-09-05 21:17:41.0000000', 121),N'CaseCreated',1630876449861359,N'PUBLIC',20,3313558,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-05 21:17:41.0000000', 121),N'Probate application',N'createCase',N'Case created',23915160,N'PUBLIC',N'CaseCreated',N'Case created',N'Probate application',N'PerfTest',N'ca24b815-f956-4ddc-96c8-cf86cf93d7d6',N'Citizen',CONVERT(DATETIME2, '2021-09-06 01:10:26.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Personal',CONVERT(DATETIME, '2021-09-05', 120),N'ctsc',N'No',800000,800000,CONVERT(DATETIME, '2019-12-23', 120),N'No',N'intestacy',N'No',N'--',NULL,NULL,NULL,N'No',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120)),  
(CONVERT(DATETIME2, '2021-09-01 15:23:09.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-01 15:24:48.0000000', 121),CONVERT(DATETIME2, '2021-09-01 15:24:48.0000000', 121),N'CaseCreated',1630509789203151,N'PUBLIC',25,3292309,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-01 15:24:48.0000000', 121),N'Probate application',N'createCase',N'Case created',22717625,N'PUBLIC',N'CaseCreated',N'Case created',N'Probate application',N'iaROHkNAibyBvlEqKOYqgiamPAMoZpCtEjFy',N'a69c838a-1714-47eb-85c2-17926211a7de',N'iaROHkNAibyBvlEqKOYqgiamPAMoZpCtEjFy',CONVERT(DATETIME2, '2021-09-02 01:17:26.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Personal',CONVERT(DATETIME, '2021-09-01', 120),N'ctsc',N'No',20000000,30000000,CONVERT(DATETIME, '2017-01-01', 120),N'No',N'intestacy',N'No',N'--',NULL,NULL,NULL,N'No',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120)),  
(CONVERT(DATETIME2, '2021-09-06 17:08:40.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-06 17:08:43.0000000', 121),CONVERT(DATETIME2, '2021-09-06 17:08:43.0000000', 121),N'CaseCreated',1630948120721697,N'PUBLIC',1,3318450,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-06 17:08:40.0000000', 121),N'Probate Application created by FT',N'applyForGrant',N'PA application created',24145928,N'PUBLIC',N'PAAppCreated',N'PA application created',N'Probate application',N'******@gmail.com',N'726469',N'******@gmail.com',CONVERT(DATETIME2, '2021-09-07 01:10:55.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Personal',CONVERT(DATETIME, '2020-09-07', 120),N'ctsc',N'',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),N'No',NULL,N'',N'--',NULL,NULL,NULL,N'No',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120)),  
(CONVERT(DATETIME2, '2021-09-03 15:10:43.0000000', 121),N'PROBATE',CONVERT(DATETIME2, '2021-09-03 15:10:45.0000000', 121),CONVERT(DATETIME2, '2021-09-03 15:10:45.0000000', 121),N'CaseCreated',1630681843048425,N'PUBLIC',1,3303796,N'GrantOfRepresentation',592,CONVERT(DATETIME2, '2021-09-03 15:10:45.0000000', 121),N'Probate Application created by FT',N'createCase',N'Case created',23296451,N'PUBLIC',N'CaseCreated',N'Case created',N'Probate application',N'******@gmail.com',N'726469',N'******@gmail.com',CONVERT(DATETIME2, '2021-09-04 01:10:26.0000000', 121),CONVERT(DATETIME2, '2021-09-10 04:05:47.7000000', 121),N'Personal',CONVERT(DATETIME, '2020-09-07', 120),N'ctsc',N'',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),N'No',NULL,N'',N'--',NULL,NULL,NULL,N'No',NULL,NULL,NULL,CONVERT(DATETIME, '2021-09-01', 120))  

Could someone please help translate / convert the code to T-SQL. I appreciate there is a lot to translate, but if you could at least provide a guide that would be great.

Please let me know if you need any additional information.

Answer 1

There is no NVL function in TSQL：

NVL(trg.ce_app_sub_date, src.case_submitted_date)

Replace with:

CASE WHEN trg.ce_app_sub_date IS NULL THEN src.case_submitted_date
      ELSE trg.ce_app_sub_date END