Not Getting Any Results from Query

Carlton Patterson 741

Hello Community,

The following query won't return any results and I'm not entirely sure why..
I wonder if someone could take a quick look and let me know where I'm going wrong.

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

Sample Data is As Follows:

CREATE TABLE v_ccd_probate_metadata (  
    CD_CREATED_DATE nvarchar(50),  
    CD_JURISDICTION nvarchar(50),  
    CD_LAST_MODIFIED nvarchar(50),  
    CD_LAST_STATE_MODIFIED_DATE nvarchar(50),  
    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 nvarchar(50),  
    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 nvarchar(50))  
  
INSERT v_ccd_probate_metadata VALUES  
(N'31:56.0',N'PROBATE',N'31:58.0',N'31:58.0',N'CaseCreated',1.63049E+15,N'PUBLIC',1,3290751,N'GrantOfRepresentation',592,N'31:56.0',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',N'17:26.0'),  
(N'26:29.0',N'PROBATE',N'28:17.0',N'28:17.0',N'CaseCreated',1.63102E+15,N'PUBLIC',3,3323282,N'GrantOfRepresentation',592,N'27:39.0',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',N'14:58.0'),  
(N'14:09.0',N'PROBATE',N'17:41.0',N'17:41.0',N'CaseCreated',1.63088E+15,N'PUBLIC',20,3313558,N'GrantOfRepresentation',592,N'17:41.0',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',N'10:26.0'),  
(N'23:09.0',N'PROBATE',N'24:48.0',N'24:48.0',N'CaseCreated',1.63051E+15,N'PUBLIC',25,3292309,N'GrantOfRepresentation',592,N'24:48.0',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',N'17:26.0'),  
(N'08:40.0',N'PROBATE',N'08:43.0',N'08:43.0',N'CaseCreated',1.63095E+15,N'PUBLIC',1,3318450,N'GrantOfRepresentation',592,N'08:40.0',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',N'10:55.0'),  
(N'10:43.0',N'PROBATE',N'10:45.0',N'10:45.0',N'CaseCreated',1.63068E+15,N'PUBLIC',1,3303796,N'GrantOfRepresentation',592,N'10:45.0',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',N'10:26.0'),  
(N'00:03.0',N'PROBATE',N'01:43.0',N'01:43.0',N'CaseCreated',1.63051E+15,N'PUBLIC',25,3292368,N'GrantOfRepresentation',592,N'01:43.0',N'Probate application',N'createCase',N'Case created',22724964,N'PUBLIC',N'CaseCreated',N'Case created',N'Probate application',N'ZGOjdQaRgXMFTtawRispZuDbmfuasLmoVcwa',N'4749a8a7-7cca-4d4c-a2dd-ab910d811231',N'ZGOjdQaRgXMFTtawRispZuDbmfuasLmoVcwa',N'17:26.0'),  
(N'12:29.0',N'PROBATE',N'14:36.0',N'14:36.0',N'CaseCreated',1.63062E+15,N'PUBLIC',3,3299331,N'GrantOfRepresentation',592,N'14:36.0',N'',N'solicitorReviewAndConfirm',N'Complete application',23078794,N'PUBLIC',N'CaseCreated',N'Case created',N'',N'VUser',N'1c0ed225-1ff1-4ee9-b064-b321dbd87549',N'VykUser',N'10:10.0')  
  



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)

Carlton Patterson 741 Reputation points

2021-09-30T13:47:01.953+00:00

Pituach,

For some reason your answer has disappeared

Accepted answer

Ronen Ariely 15,096 Reputation points

2021-09-30T13:21:01.807+00:00
Good day Carlton Patterson @Carlton Patterson

Welcome to the QnA forum, and well done for providing DDL+DML for sample tables/data

With that said, the DDL+DML does not fully fits your query

I tried to give it a look but when execute your query I got error and noticed that your query uses the table tbl_probate_case which we do not have in the sample.

The following query won't return any results and I'm not entirely sure why..

Note: This is an INSERT query which mean it does not expected to return any row, but only inform you if it succeed (using SSMS you should get the number of rows inserted)

I assume that you mean to discuss only the SELECT query which you use for the INSERT

If this is the case then we need to clean wrapper INSERT part and this will mean that we cannot also use part of the filter:

AND NOT EXISTS (SELECT 1 FROM tbl_probate_case t WHERE t.case_data_id = m.ce_case_data_id ) ;

Even if the query with this filter will return rows (which is not right now) then once you add a filter then it might not return any row since this filter remove these from the result SET.

The clean SELECT query which I got after this is the following, Please confirm that this is the query which you want to discuss or provide the missing DDL+DML for the third table used in your 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' GO
Please sign in to rate this answer.
Carlton Patterson 741 Reputation points

2021-09-30T13:50:02.17+00:00

Hi Pituach,

did you see my response? There seems to be something odd going on with this forum at the moment
Sign in to comment

6 additional answers

Carlton Patterson 741 Reputation points

2021-09-30T16:35:29.777+00:00

Pituach, for some reason I'm unable to submit the query that causing the problem in a reply, so hopefully it will show here:

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 = ISNULL(trg.ce_app_sub_date, src.case_submitted_date)
, Trg.Submitted = 1
, trg.bi_last_updated_date = GETUTCDATE();
Please sign in to rate this answer.
Ronen Ariely 15,096 Reputation points

2021-09-30T17:17:54.177+00:00

Invalid column name 'case_submitted_date

This one I already answered above in the comment

The MERGE query raises error since the the column case_submitted_date does not exists in your target virtual table trg. This is the code which you use: MERGE INTO tbl_probate_case trg but the table tbl_probate_case does not have this column => therefor, when you use SET trg.case_submitted_date = then you get error.

You cannot SET a column which does not exists in the table and this trg.case_submitted_date not exists, since trg is the table tbl_probate_case which does not have this column

Carlton Patterson 741 Reputation points

2021-09-30T18:24:10.117+00:00

OK, I have just re-read your answer. I feel like a numbty. I will try and fix this.
Thanks

Carlton Patterson 741 Reputation points

2021-09-30T18:30:52.203+00:00

Just one thought pituach,
Doesn't the code actually create the field case_submitted_date witht he code:

MERGE INTO tbl_probate_case trg
USING ( SELECT m.ce_case_data_id
, MIN(m.ce_created_date) AS case_submitted_date

Ronen Ariely 15,096 Reputation points

2021-09-30T18:33:42.333+00:00

No problem :-)

You can close the thread by marking the first answer which cover the original question, and you feel free o ask if you have more questions (just try to keep each discussion (thread) focused on specific issue/question

Carlton Patterson 741 Reputation points

2021-09-30T18:36:08.857+00:00

Thanks Pituach, shall I open a new question?

Ronen Ariely 15,096 Reputation points

2021-09-30T19:13:37.573+00:00

The part after the USING .. is not related to trg but to the virtual table src. You should really learn the basic of what you are using before you use it. Seems like you use MERGE query without even understand the format and this is bad. The forum can help you cover specific question but you waste you time if you think to learn the basic here.

I recommend to go over the following document from start to end :-)
https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

----------

As someone who used to teach in High school before I moved to work in the University, I can give you some numbers: Learning the SQL language takes only 3 full days! People in the forums usually continue to ask question thinking that they learn for more then 10 years sometimes (I am speaking now about SQL and not TSQL as example of real numbers). The issue is that people are lazy so they do millions time more work

Someone who is not lazy and read for example a post which he got, could learn a lot faster, and someone who is not lazy and will spend a few days to learn SQL will not need to waste 10 years of questions, BUT THIS IS NUTURAL TO BA LAZY AND TO WANT TO JUMP DIRECTLY TO SOLVe THE CURRENT NEEDS instead of doing work which seems not relevant for this second :-)

Carlton Patterson 741 Reputation points

2021-09-30T20:50:22.24+00:00

Hi, please forgive me for keep talking about this issue, but there is one thing I forgot to mention (which probably won't mean a lot), but the code is actually working in its current format in our Production environment, but it is running on an Oracle server. So, I'm wondering if it would make any difference that the code. So, the codes raw format in Oracle would look like the following:

You will notice Oracle functions like NVL or sysdate. I was wondering if that would make a difference in the way the code would work? My guess is not, but I needed to ask the question
Sign in to comment

Carlton Patterson 741

      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;

Carlton Patterson 741 Reputation points

2021-09-30T20:56:50.883+00:00

The above is the Oracle code that appears to work in Production without any issues