question

CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 asked CarltonPatterson-0401 commented

Not Getting Any Results from Query

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)


sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Pituach,

For some reason your answer has disappeared

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered CarltonPatterson-0401 commented

Good day Carlton Patterson @CarltonPatterson-0401

Welcome to the QnA forum, and well done for providing DDL+DML for sample tables/data 136684-image.png 136684-image.png 136684-image.png

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



image.png (460 B)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Pituach,

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

0 Votes 0 ·
CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered

Actually, please DONT forget this question. I have noticed that I'm still not getting any results even after there is a match with the Inner Joins...You're help much appreciated.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered pituach commented

Hi Pituach,

Before I fully examine your response to my question let me first let me say thank you and provide you with the table tbl_probate_case;

 CREATE TABLE tbl_probate_case (
         case_data_id nvarchar(50),
         cd_reference nvarchar(50),
         case_created_date nvarchar(50),
         created nvarchar(50),
         submitted nvarchar(50),
         examined nvarchar(50),
         stopped nvarchar(50),
         issued nvarchar(50),
         issued_in_20days nvarchar(50),
         ce_app_type nvarchar(50),
         ce_app_sub_date nvarchar(50),
         ce_reg_location nvarchar(50),
         ce_will_exists nvarchar(50),
         ce_iht_gross_value nvarchar(50),
         ce_iht_net_value nvarchar(50),
         ce_deceased_dod nvarchar(50),
         ce_deceased_other_names nvarchar(50),
         latest_state_id nvarchar(50),
         latest_state_name nvarchar(50),
         bi_last_updated_date nvarchar(50),
         bi_created_date nvarchar(50),
         ce_gor_case_type nvarchar(50),
         ce_paperform_ind nvarchar(50),
         issued_in_7wdays nvarchar(50),
         legacy_case_reference_id nvarchar(50),
         grant_issued_date nvarchar(50))
        
     INSERT tbl_probate_case VALUES
     (N'3290751',N'1.63049e+015',N'2021-09-01 08:31:56.0000000',N'1',N'0',N'0',N'0',N'0',N'0',N'Personal',N'2020-09-07',N'ctsc',N'',N'200000',N'100000',N'2020-01-01',N'No',N'PAAppCreated',N'PA application created',N'Sep 28 2021 12:11PM',N'Sep 28 2021 12:11PM',NULL,N'',N'0',NULL,N'--')

And I confirm that is the query I wish to discuss

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Awesome :-)

I added answer... please check

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered CarltonPatterson-0401 commented

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

Thanks for providing the missing information.

The reason that the select query does not return any row is the filter ON p.case_metadata_event_id = m.ce_id

You simply do not have any row in stg_ccd_probategrant table which has the same value in the column case_metadata_event_id as the column ce_id in the table v_ccd_probate_metadata

Even if you remove the first INNER JOIN then you will not get any row that returns

 SELECT *
 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

Check your data again and confirm that this is the right relation between the tables

As step one, confirm that when you join these two table you get the expected result SET

Note! If this not solve your issue, then please describe what is your expected result SET and we can try to find the right condition for the JOIN. At this time the answer is simple as above, the condition ON p.case_metadata_event_id = m.ce_id filter all rows

Note: The issue is probably related to the type of JOIN. The most common case is that people do not fully know the meaning of each type of JOIN and using the wrong one. If this is the case and you are not 100% sure how JOIN behave then please check the following short tutorial (their images give some understanding but better to check the explanation in the tutorial):
https://www.w3schools.com/sql/sql_join.asp
136618-image.png



image.png (53.1 KiB)
· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Doh! Pituach I realised that after I sent the table Arrrgggghhh..

Anyway, I have given a value of 3 in both m.ce_id and case_metadata_event_id. I therefore got back one single result. Which is fine for testing at the moment. However, I want to build on this code with the following MERGE STATEMENT

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

But I keep on getting the error:

Invalid column name 'case_submitted_date'.

0 Votes 0 ·
pituach avatar image pituach CarltonPatterson-0401 ·

The error is not clear?

It says all :-)

You do not have a column "case_submitted_date" in your target table (or in any of the tables in the sample DDL for that matter)

you have this name only in the src but not in trg.case_submitted_date

Your target table is tbl_probate_case

0 Votes 0 ·

Hi Pituach,

I'm having a problem replying to you.
I truncated the tbl_probate_case table and when I attempt to do another insert I get the error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

0 Votes 0 ·
Show more comments

But doesn't the following create the field column case_submitted_date

, MIN(m.ce_created_date) AS case_submitted_date

0 Votes 0 ·
CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered CarltonPatterson-0401 published

Here are the tables:

 CREATE TABLE v2_ccd_probate.dbo.stg_ccd_probategrant (
   CE_APP_TYPE VARCHAR(50) NULL
  ,CE_APP_SUB_DATE DATE NULL
  ,CE_REG_LOCATION VARCHAR(50) NULL
  ,CE_WILL_EXISTS VARCHAR(50) NULL
  ,CE_IHT_NET_VALUE INT NULL
  ,CE_IHT_GROSS_VALUE INT NULL
  ,CE_DECEASED_DOD DATE NULL
  ,CE_DECEASED_OTHER_NAMES VARCHAR(50) NULL
  ,CE_GOR_CASE_TYPE VARCHAR(50) NULL
  ,CE_PAPERFORM_IND VARCHAR(50) NULL
  ,CE_LEG_RECORD_ID VARCHAR(255) NULL
  ,CE_LATEST_GRANT_REISSUE_DATE VARCHAR(255) NULL
  ,CE_GRANTISSUED_DATE VARCHAR(50) NULL
  ,CE_REISSUE_REASON VARCHAR(255) NULL
  ,CE_WELSH_LANG_PREF VARCHAR(50) NULL
  ,CE_PRIMARY_APPLICANT_ADDRESS VARCHAR(255) NULL
  ,CE_CASE_DATA_ID INT NULL
  ,CASE_METADATA_EVENT_ID NUMERIC(38) IDENTITY
  ,ADTCLMN_INSERTED_BY_PROCESS_NAME VARCHAR(MAX) NULL
  ,CE_CASE_STOP_REASON VARCHAR(4000) NULL
  ,ADTCLMN_FIRST_CREATED_DATETIME DATETIME NULL
  ,ADTCLMN_EXTRACTED_DATETIME DATETIME NULL
  ,ADTCLMN_LAST_MODIFIED_DATETIME DATETIME NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
    
 CREATE TABLE v2_ccd_probate.dbo.stg_ccd_probategrant (
   CE_APP_TYPE VARCHAR(50) NULL
  ,CE_APP_SUB_DATE DATE NULL
  ,CE_REG_LOCATION VARCHAR(50) NULL
  ,CE_WILL_EXISTS VARCHAR(50) NULL
  ,CE_IHT_NET_VALUE INT NULL
  ,CE_IHT_GROSS_VALUE INT NULL
  ,CE_DECEASED_DOD DATE NULL
  ,CE_DECEASED_OTHER_NAMES VARCHAR(50) NULL
  ,CE_GOR_CASE_TYPE VARCHAR(50) NULL
  ,CE_PAPERFORM_IND VARCHAR(50) NULL
  ,CE_LEG_RECORD_ID VARCHAR(255) NULL
  ,CE_LATEST_GRANT_REISSUE_DATE VARCHAR(255) NULL
  ,CE_GRANTISSUED_DATE VARCHAR(50) NULL
  ,CE_REISSUE_REASON VARCHAR(255) NULL
  ,CE_WELSH_LANG_PREF VARCHAR(50) NULL
  ,CE_PRIMARY_APPLICANT_ADDRESS VARCHAR(255) NULL
  ,CE_CASE_DATA_ID INT NULL
  ,CASE_METADATA_EVENT_ID NUMERIC(38) IDENTITY
  ,ADTCLMN_INSERTED_BY_PROCESS_NAME VARCHAR(MAX) NULL
  ,CE_CASE_STOP_REASON VARCHAR(4000) NULL
  ,ADTCLMN_FIRST_CREATED_DATETIME DATETIME NULL
  ,ADTCLMN_EXTRACTED_DATETIME DATETIME NULL
  ,ADTCLMN_LAST_MODIFIED_DATETIME DATETIME NULL
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 GO
 CREATE TABLE v2_ccd_probate.dbo.v_ccd_probate_metadata (
   CD_CREATED_DATE DATETIME2 NULL
  ,CD_JURISDICTION NVARCHAR(50) NULL
  ,CD_LAST_MODIFIED DATETIME2 NULL
  ,CD_LAST_STATE_MODIFIED_DATE DATETIME2 NULL
  ,CD_LATEST_STATE NVARCHAR(50) NULL
  ,CD_REFERENCE FLOAT NULL
  ,CD_SECURITY_CLASSIFICATION NVARCHAR(50) NULL
  ,CD_VERSION INT NULL
  ,CE_CASE_DATA_ID INT NULL
  ,CE_CASE_TYPE_ID NVARCHAR(50) NULL
  ,CE_CASE_TYPE_VERSION INT NULL
  ,CE_CREATED_DATE DATETIME2 NULL
  ,CE_DESCRIPTION NVARCHAR(100) NULL
  ,CE_EVENT_ID NVARCHAR(50) NULL
  ,CE_EVENT_NAME NVARCHAR(50) NULL
  ,CE_ID INT NULL
  ,CE_SECURITY_CLASSIFICATION NVARCHAR(50) NULL
  ,CE_STATE_ID NVARCHAR(50) NULL
  ,CE_STATE_NAME NVARCHAR(50) NULL
  ,CE_SUMMARY NVARCHAR(100) NULL
  ,CE_USER_FIRST_NAME NVARCHAR(100) NULL
  ,CE_USER_ID NVARCHAR(50) NULL
  ,CE_USER_LAST_NAME NVARCHAR(100) NULL
  ,EXTRACTION_DATE DATETIME2 NULL
 ) ON [PRIMARY]
 GO
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered CarltonPatterson-0401 commented

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

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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





0 Votes 0 ·

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

0 Votes 0 ·
pituach avatar image pituach CarltonPatterson-0401 ·

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

0 Votes 0 ·
Show more comments

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

0 Votes 0 ·
pituach avatar image pituach CarltonPatterson-0401 ·



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://docs.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 136757-image.png

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



1 Vote 1 ·
image.png (677 B)
Show more comments
CarltonPatterson-0401 avatar image
0 Votes"
CarltonPatterson-0401 answered CarltonPatterson-0401 commented
       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;
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·