September 28, 2021 at 8:34 am
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.
September 28, 2021 at 8:41 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 8:56 am
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
September 28, 2021 at 9:01 am
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)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 9:02 am
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.
September 28, 2021 at 9:20 am
I'm getting the following error:
Invalid object name 'stg_ccd_probategrant'.
September 28, 2021 at 9:25 am
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 9:26 am
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.
September 28, 2021 at 9:33 am
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.
September 28, 2021 at 10:11 am
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
September 28, 2021 at 10:22 am
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.
September 28, 2021 at 10:41 am
@Ant-Green,
'ce_app_type' exists in 'v_ccd_probate_metadata'
Regards
September 28, 2021 at 10:42 am
@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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 28, 2021 at 11:06 am
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
September 28, 2021 at 11:23 am
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 27 total)
You must be logged in to reply to this topic. Login to reply