January 2, 2019 at 11:40 am
Hi All,
I'm trying to investigate an issue where a date field, PolicyCancelDt, of type datetime from an extract table is changing to NULL after loading into a dimension table, where the corresponding field is of type int. There is a conversion in the query for the extract:SELECT DISTINCT
po.sPolicyID AS PolicyAgreementNumber
--, 'Unknown'
, mv.[Market Vertical] as MarketVerticalName
, c.sCustomerID
, p.sProductID
, c.sCustomerID + p.sProductID as CombinedClientProductID
, CONVERT(Int,Convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
, CONVERT(Int,Convert(varchar(8), po.PolicyEfftvDt, 112)) as PolicyEfftvDt
, CONVERT(Int,Convert(varchar(8), po.PolicyExpirDt, 112)) as PolicyExpirDt
, CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as PolicyCancelDt
--, -1
--, -1
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
WHERE jw.sProductID is not null OR p.sProductID = '9095'
ORDER BY PolicyAgreementNumber
I'm not sure why this is happening for some records. There's a slowly changing dimension data flow as part of this package, but this date field has no part in it. Could someone please advise on why this might be happening?
January 2, 2019 at 11:51 am
daniness - Wednesday, January 2, 2019 11:40 AMHi All,I'm trying to investigate an issue where a date field, PolicyCancelDt, of type datetime from an extract table is changing to NULL after loading into a dimension table, where the corresponding field is of type int. There is a conversion in the query for the extract:
SELECT DISTINCT
po.sPolicyID AS PolicyAgreementNumber
--, 'Unknown'
, mv.[Market Vertical] as MarketVerticalName
, c.sCustomerID
, p.sProductID
, c.sCustomerID + p.sProductID as CombinedClientProductID
, CONVERT(Int,Convert(varchar(8), po.PolicyIssueDt, 112)) as PolicyIssueDt
, CONVERT(Int,Convert(varchar(8), po.PolicyEfftvDt, 112)) as PolicyEfftvDt
, CONVERT(Int,Convert(varchar(8), po.PolicyExpirDt, 112)) as PolicyExpirDt
, CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as PolicyCancelDt
--, -1
--, -1
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
WHERE jw.sProductID is not null OR p.sProductID = '9095'
ORDER BY PolicyAgreementNumberI'm not sure why this is happening for some records. There's a slowly changing dimension data flow as part of this package, but this date field has no part in it. Could someone please advise on why this might be happening?
if this query is what is used to load onto the target dimension then the source (dbo.ExtPRSDEPolicy.PolicyCancelDt) is null on those records - those 2 converts as they are would result in an error if the data was "bad". As that is not what you are reporting then only option is that they are null already.
you can check this by testing select *
from (
SELECT DISTINCT
po.sPolicyID AS PolicyAgreementNumber
, mv.[Market Vertical] as MarketVerticalName
, c.sCustomerID
, p.sProductID
, c.sCustomerID + p.sProductID as CombinedClientProductID
, CONVERT(Int,Convert(varchar(8), po.PolicyCancelDt, 112)) as PolicyCancelDt_int
, po.PolicyCancelDt
FROM dbo.ExtPRSDEPolicy po
join dbo.ExtPRSDECustomer c on c.iCustomerKey = po.iCustomerKey
Join dbo.ExtPRSDEProduct p on p.iProductKey = po.iProductKey
LEFT JOIN ExtJWProductSegment jw ON p.sProductID = jw.sProductID
LEFT JOIN ExtMarketVertical mv ON LEFT(po.sPolicyID, 7) = mv.Policy_Number
WHERE jw.sProductID is not null OR p.sProductID = '9095'
) t
where PolicyCancelDt_int is null
Edit: Another option is that there is a trigger on that table that changes that column to null on certain cases.
If this is not the query that is loading onto the dimension then please post the correct query.
January 2, 2019 at 11:58 am
Have you tried casting the date fields as datetime or datetime2 ?
January 2, 2019 at 12:21 pm
Thank you, @frederico_fonseca. I tried your test query but it's not returning the record with the PolicyAgreementNumber in question. As for triggers, my department doesn't utilize any in the DataMart. Any other suggestions are welcome. Thanks!
January 2, 2019 at 12:23 pm
kn_murthy - Wednesday, January 2, 2019 11:58 AMHave you tried casting the date fields as datetime or datetime2 ?
@kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.
January 2, 2019 at 1:48 pm
daniness - Wednesday, January 2, 2019 12:23 PMkn_murthy - Wednesday, January 2, 2019 11:58 AMHave you tried casting the date fields as datetime or datetime2 ?@kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.
Well are there any post updates that run after the initial data load that might be changing the values?
January 3, 2019 at 6:49 am
Try looking at the data for the product ID for each side of the OR ctriteria.
If they are different your distinct might be not so distinct.
January 3, 2019 at 10:27 am
ZZartin - Wednesday, January 2, 2019 1:48 PMdaniness - Wednesday, January 2, 2019 12:23 PMkn_murthy - Wednesday, January 2, 2019 11:58 AMHave you tried casting the date fields as datetime or datetime2 ?@kn_murthy, I don't think I need to do this, as the field is changing to NULL for random records.
Well are there any post updates that run after the initial data load that might be changing the values?
@ZZartin, there are only error row count and an error rows raw file data flow components...I don't think these would be causing the issue though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply