September 28, 2021 at 11:37 am
We don't know the answer to that. But we can see in your code that that is what is being referenced.
It seems that this is not an Oracle/SQL Server thing, but rather a case of you needing to understand what is going on on the Oracle side before attempting to migrate to SQL Server.
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 3:44 pm
Hi Phil,
I guess I asked a silly question, as I haven't heard from you....
September 28, 2021 at 3:55 pm
Hi Phil,
I guess I asked a silly question, as I haven't heard from you....
Nothing silly about it, but I have responded.
If you cannot understand the code as it stands (ie, the use of the table rather than the (presumably) view in the Oracle version), you are going to have trouble migrating it to SQL Server.
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 6:33 pm
I'm betting that v_ccd_probate_metadata is a view. As such, a view consists of tables and the columns from those tables.
If these columns exist in the view but not in the underlying tables, you will receive these errors.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2021 at 9:47 pm
I get what you're saying Phil.
Anyway, just for my piece of mind, Michael are you saying that with the error occur with the following:
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
Because
m. refers to table v_ccd_probate_metadata
while
p. refers to stg_ccd_probategrant
Correct?
Which is what Phil was saying from the beginning.
September 28, 2021 at 10:11 pm
ok, can you help with the following conversion of Oracle PL SQL to T-SQL
It was mentioned that T-SQL doesn't use NVL, therefore I should change
SET trg.case_submitted_date = NVL(trg.ce_app_sub_date, src.case_submitted_date)
to
CASE WHEN trg.ce_app_sub_date IS NULL THEN src.case_submitted_date ELSE trg.ce_app_sub_date END
Is that correct?
September 29, 2021 at 6:07 am
Oracle NVL in TSQL is the ISNULL function
SET trg.case_submitted_date = ISNULL(trg.ce_app_sub_date, src.case_submitted_date)
September 30, 2021 at 11:59 am
Thanks Ant Green
September 30, 2021 at 12:08 pm
OK,
I'm not sure if I should start a new question or continue with this question. However, I have got the original query to work, see below, but I'm not getting back any results. Can someone 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)
September 30, 2021 at 1:22 pm
Please forget this question. I figured it out.. There aren't any matches in the inner-joins
September 30, 2021 at 1:26 pm
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.
September 30, 2021 at 1:41 pm
I just realised I didn't include the missing 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'--'),
(N'3323282',N'1.63102e+015',N'2021-09-07 14:27:39.0000000',N'1',N'0',N'0',N'0',N'0',N'0',N'Solicitor',NULL,N'ctsc',N'Yes',N'10000100',N'10000000',N'2020-01-01',N'No',N'SolAppUpdated',N'Application updated',N'Sep 28 2021 12:11PM',N'Sep 28 2021 12:11PM',N'admonWill',N'No',N'0',NULL,N'--')
September 30, 2021 at 2:45 pm
The answer to this question is the innerjoin does not have common value across tables.
Please disregard
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply