September 30, 2021 at 8:39 pm
Hi, I have posted a similar question which has been resolved. However, this follow on question is intended to both improve my understanding of the MERGE statement in our production environment as well as establish if there is workaround to produce a result.
I'm getting the following error
Invalid column name 'case_submitted_date'.
when I run the following query
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();
Now, a very clever person on another forum has already told me that the problem is that 'case_submitted_date' does not exist in the target table tbl_probate_case (and I hope he doesn't read this forum, as I don't want him to think I'm undermining him as he as been a tremendous help), but the only reason why I'm still working on this is because the code is currently being used in production.
I should point out the code is being used in Oracle PL /SQL
I just want to get your thoughts
September 30, 2021 at 8:49 pm
I should mention the following is the code it is RAW PL / SQL format that works in production
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;
September 30, 2021 at 10:35 pm
First thing, posting an Oracle question in the SQL Server 2016 forum section is likely not going to get many results (if any).
Next, I am confused why you posted here when a different user told you what was wrong - the column doesn't exist.
My approach to troubleshooting this though would be to first determine WHICH column it thinks is missing. As a GUESS I would say it is trg.case_submitted_date, so I would check to make sure the column exists in the database. It could be something like case sensitivity that is causing it to think it is missing.
But the BEST way to get help on this forum is to submit DDL and some sample data so we can reproduce your problem. I won't be much help as I am a SQL Server guy and this is MOSTLY a SQL Server forum.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 30, 2021 at 10:52 pm
Hi Brian,
I hope the person that originally told me what the problem doesn't read this post, as he was extremely helpful and I don't want to undermine him. But I would just like to know what makes this code work in Oracle - I seriously hope he doesn't get affended.
Anyway, here you go.
CREATE TABLE stg_ccd_probategrant (
CE_APP_TYPE varchar(50),
CE_APP_SUB_DATE date,
CE_REG_LOCATION varchar(50),
CE_WILL_EXISTS varchar(50),
CE_IHT_NET_VALUE int,
CE_IHT_GROSS_VALUE int,
CE_DECEASED_DOD date,
CE_DECEASED_OTHER_NAMES varchar(50),
CE_GOR_CASE_TYPE varchar(50),
CE_PAPERFORM_IND varchar(50),
CE_LEG_RECORD_ID varchar(255),
CE_LATEST_GRANT_REISSUE_DATE varchar(255),
CE_GRANTISSUED_DATE varchar(50),
CE_REISSUE_REASON varchar(255),
CE_WELSH_LANG_PREF varchar(50),
CE_PRIMARY_APPLICANT_ADDRESS varchar(255),
CE_CASE_DATA_ID int,
CASE_METADATA_EVENT_ID decimal(38),
ADTCLMN_INSERTED_BY_PROCESS_NAME varchar(max),
CE_CASE_STOP_REASON varchar(4000),
ADTCLMN_FIRST_CREATED_DATETIME datetime,
ADTCLMN_EXTRACTED_DATETIME datetime,
ADTCLMN_LAST_MODIFIED_DATETIME datetime)
INSERT stg_ccd_probategrant VALUES
('Personal',CONVERT(DATETIME, '2020-09-07', 120),'ctsc','',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),'No',NULL,'',NULL,NULL,'--',NULL,'No',NULL,3290751,1,NULL,NULL,NULL,NULL,NULL),
('Solicitor',NULL,'ctsc','Yes',10000000,10000100,CONVERT(DATETIME, '2020-01-01', 120),'No','admonWill','No',NULL,NULL,'--',NULL,'',NULL,3323282,2,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2021-09-05', 120),'ctsc','No',800000,800000,CONVERT(DATETIME, '2019-12-23', 120),'No','intestacy','No',NULL,NULL,'--',NULL,'No',NULL,3313558,3,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2021-09-01', 120),'ctsc','No',20000000,30000000,CONVERT(DATETIME, '2017-01-01', 120),'No','intestacy','No',NULL,NULL,'--',NULL,'No',NULL,3292309,4,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2020-09-07', 120),'ctsc','',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),'No',NULL,'',NULL,NULL,'--',NULL,'No',NULL,3318450,5,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2020-09-07', 120),'ctsc','',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),'No',NULL,'',NULL,NULL,'--',NULL,'No',NULL,3303796,6,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2021-09-01', 120),'ctsc','No',20000000,30000000,CONVERT(DATETIME, '2017-01-01', 120),'No','intestacy','No',NULL,NULL,'--',NULL,'No',NULL,3292368,7,NULL,NULL,NULL,NULL,NULL),
('Solicitor',CONVERT(DATETIME, '2021-09-02', 120),'ctsc','Yes',1000000,1000000,CONVERT(DATETIME, '2007-07-06', 120),'No','gop','No',NULL,NULL,'--',NULL,'',NULL,3299331,8,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2021-09-01', 120),'ctsc','Yes',30000000,60000000,CONVERT(DATETIME, '2017-01-01', 120),'No','gop','No',NULL,NULL,'--',NULL,'No',NULL,3292199,9,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2021-09-01', 120),'ctsc','Yes',30000000,60000000,CONVERT(DATETIME, '2017-01-01', 120),'No','gop','No',NULL,NULL,'--',NULL,'No',NULL,3292181,10,NULL,NULL,NULL,NULL,NULL),
('Personal',CONVERT(DATETIME, '2020-09-07', 120),'ctsc','',100000,200000,CONVERT(DATETIME, '2020-01-01', 120),'No',NULL,'',NULL,NULL,'--',NULL,'No',NULL,3323067,11,NULL,NULL,NULL,NULL,NULL),
('Solicitor',NULL,'ctsc','Yes',1000000,1000000,CONVERT(DATETIME, '2008-04-18', 120),'No','gop','No',NULL,NULL,'--',NULL,'',NULL,3313526,12,NULL,NULL,NULL,NULL,NULL),
(NULL,CONVERT(DATETIME, '2021-09-08', 120),NULL,'',NULL,NULL,NULL,'',NULL,'',NULL,NULL,'--',NULL,'',NULL,3329571,13,NULL,NULL,NULL,NULL,NULL),
('Solicitor',NULL,'ctsc','Yes',100,1200,CONVERT(DATETIME, '2020-02-01', 120),'No','gop','No',NULL,NULL,'--',NULL,'',NULL,3328413,14,NULL,NULL,NULL,NULL,NULL)
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)
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',3,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-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-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-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-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))
October 1, 2021 at 9:29 am
Can you also post DDL and sample INSERT statements (if any) for tbl_probate_case?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 1, 2021 at 11:41 am
Has table tbl_probate_case got a column named case_submitted_date in the environment you are getting the error?
October 1, 2021 at 1:38 pm
Hi Jonathon,
tbl_probate_case does not have column named case_submitted_date.
I very clever person on another forum has already advised me that the problem was the missing column. The thrust behind this question is to determine why the query works with Oracle PL / SQL and not T-SQL.
The DDL for tbl_probate_case is as follows:
CREATE TABLE v2_ccd_probate.dbo.tbl_probate_case (
case_data_id NVARCHAR(50) NULL
,cd_reference NVARCHAR(50) NULL
,case_created_date DATETIME2 NULL
,created VARCHAR(50) NULL
,submitted NVARCHAR(50) NULL
,examined NVARCHAR(50) NULL
,stopped NVARCHAR(50) NULL
,issued NVARCHAR(50) NULL
,issued_in_20days NVARCHAR(50) NULL
,ce_app_type NVARCHAR(50) NULL
,ce_app_sub_date DATE NULL
,ce_reg_location NVARCHAR(50) NULL
,ce_will_exists NVARCHAR(50) NULL
,ce_iht_gross_value NVARCHAR(50) NULL
,ce_iht_net_value NVARCHAR(50) NULL
,ce_deceased_dod DATE NULL
,ce_deceased_other_names NVARCHAR(50) NULL
,latest_state_id NVARCHAR(50) NULL
,latest_state_name NVARCHAR(50) NULL
,bi_last_updated_date NVARCHAR(50) NULL
,bi_created_date DATETIME NULL
,ce_gor_case_type NVARCHAR(50) NULL
,ce_paperform_ind NVARCHAR(50) NULL
,issued_in_7wdays NVARCHAR(50) NULL
,legacy_case_reference_id NVARCHAR(50) NULL
,grant_issued_date VARCHAR(50) NULL
) ON [PRIMARY]
GO
The INSERT statement
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;
October 1, 2021 at 2:35 pm
I think you need to ask this question in an Oracle forum, as it makes no sense to me.
In SQL Server terms, I think you'll agree that it's very clear:
"The MERGE statement is attempting to modify a column which does not exist in the table being updated."
Why does this work in Oracle? No idea whatsoever.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 6, 2021 at 9:34 pm
Thanks for responding...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply