How to Convert Oracle SQL to T-SQL

  • 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'probatebackoffice.functional+cw1@gmail.com',N'726469',N'probatebackoffice.functional+cw1@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'probatebackoffice.functional+cw1@gmail.com',N'726469',N'probatebackoffice.functional+cw1@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'probatebackoffice.functional+cw1@gmail.com',N'726469',N'probatebackoffice.functional+cw1@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.

  • Given that most of that code should run just fine on SQL Server, rather than posting huge wads of code, are you able to narrow things down & identify only those parts of the code which are giving you trouble? That would make it easier for people to help you.

    On first viewing, it looks like those COMMITs will cause issues, as there is no corresponding BEGIN TRAN for them.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hi Phil,

    The parts of the code that is giving me trouble is as follows:

    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
    ) ;

    Thanks

  • I can see nothing in that code which should create a problem. What is the error you get when running that section of code?

     

    --Edit: you will need to change sysdate to GetDate() or GetUTCDate() (depending on your requirements)

    • This reply was modified 3 weeks, 3 days ago by  Phil Parkin.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • And what part of that is giving you the issue.

    The only thing I can see different in that PSQL vs TSQL is sysdate which needs to be one of the many varying ways to get the current datetime from T-SQL instead.

  • I'm getting the following error:

    Invalid object name 'stg_ccd_probategrant'.

  • carlton 84646 wrote:

    I'm getting the following error:

    Invalid object name 'stg_ccd_probategrant'.

    OK, does it exist? What happens if you execute the following?

    SELECT TOP (10) * FROM stg_ccd_probategrant

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Well that error is obvious, the object doesn't exist in the database.

    What other red lines do you have in the query as intellisence should be prompting you for those sorts of errors if you are using SSMS.

  • ok, point taken. The object doesn't exists. I will get the object - I will be back, as I have lots of questions.

    In the meantime, thanks.

  • ok, without the missing object, when I run the following code I get the error:

    The multi-part identifier "p.ce_app_type" could not be bound.

    Any thoughts

  • Does the below yield any results

    select ce_app_type from stg_ccd_probategrant

    If not your missing the column from that table by the sounds of it.

  • @Ant-Green,

     

    'ce_app_type' exists in 'v_ccd_probate_metadata'

    Regards

  • carlton 84646 wrote:

    @Ant-Green,

    'ce_app_type' exists in 'v_ccd_probate_metadata'

    Regards

    It needs to exist on stg_ccd_probategrant. That is what the reference is pointing to.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • I'm not sure I'm seeing that.

    What if I simply had the following:

      SELECT m.ce_case_data_id,
    m.cd_reference ,
    m.ce_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,
    m.ce_state_name,
    sysdate,
    sysdate,
    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
  • This worked

    SELECT m.ce_case_data_id, 
    m.cd_reference ,
    m.ce_created_date,
    1 AS created ,
    0 AS submitted ,
    0 AS examined ,
    0 AS stopped,
    0 AS issued ,
    0 AS 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 ,
    m.ce_state_id,
    m.ce_state_name,
    GETDATE(),
    GETDATE(),
    ce_gor_case_type,
    ce_paperform_ind,
    0 AS issued_in_7wdays,
    ce_leg_record_id,
    ce_grantissued_date
    FROM v_ccd_probate_metadata m

    So I'm not sure why I need to reference stg_ccd_probategrant ?

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply