question

villiec avatar image
villiec asked

Conversion failed when converting the varchar value '5.00' to data type int.

I am trying to do a count based on certain criteria and I cannot get the criteria correct. - Exclude Account Types: (8) non-member bond redemption; (15) estate restricted; (16) estate unrestricted and (17) dealer direct. - Exclude Accounts with a Warning code 50 and Share ID 01s that have been closed 90 days or more - Excluded deceased - Exclude duplicate social security numbers ;with records as ( select AD.AccountNumber AS ACCOUNT_NUMBER, SD.ShareType AS TYPE, SD.SharePrimeNameSSN AS SSN, AD.AccountPrimeLongName AS NAME, SD.PROCESSDATE AS DATE, row_number() over (partition by SD.sharePrimeNameSSN order by SD.SHARETYPE ASC) N from arcu.ARCUAccountDetailed AD join arcu.ARCUShareDetailed SD on AD.accountnumber = sd.accountnumber AND AD.ProcessDate = SD.ProcessDate where AD.AccountPrimeNameDeathDate IS NULL AND AD.AccountStatus = 'OPEN' AND SD.ShareStatus = 'OPEN' AND (SD.ShareType != '8' AND SD.ShareType != '15' AND SD.ShareType != '16' AND SD.ShareType != '17') AND (((AccountWarningcode1 != '50' AND AccountWarningcode2 != '50' AND AccountWarningcode3 != '50' AND AccountWarningcode4 != '50' AND AccountWarningcode5 != '50' AND AccountWarningcode6 != '50' AND AccountWarningcode7 != '50' AND AccountWarningcode8 != '50' AND AccountWarningcode9 != '50' AND AccountWarningcode10 != '50' AND AccountWarningcode11 != '50' AND AccountWarningcode12 != '50' AND AccountWarningcode13 != '50' AND AccountWarningcode14 != '50' AND AccountWarningcode15 != '50' AND AccountWarningcode16 != '50' AND AccountWarningcode17 != '50' AND AccountWarningcode18 != '50' AND AccountWarningcode19 != '50' AND AccountWarningcode20 != '50') AND (ShareID = '001' AND ShareCloseDate >= (GetDate() - 90))) AND AccountPrimeNameDeathDate is null )) select Count(*) from records where N = 1
t-sqlcount
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
The only integer comparison I see outside of `N` is on the ShareID column (unless accountnumber doesn't have the same data type in each table). Is ShareID the column giving the error? When you identify the column with '5.00' (I've assumed ShareId in this snippet) you can use `convert(numeric, ShareId) = 001` which will have an implicit conversion or you can explicitly convert it all the way to int - `convert(int, convert(numeric, ShareId)) = 001` One other approach would be to use numeric for your constant - `ShareId = 001.0`
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

villiec avatar image villiec commented ·
@KenJ NVM, I should have had it coded as '001', my mistake. Although my count is coming back as '0' and it should be coming back with roughly 200,000... do you see anything that I could change that is causing it to come back as '0'?
0 Likes 0 ·
KenJ avatar image KenJ commented ·
I would have to know your data a little better to be sure but, you are filtering on `AccountStatus = 'Open' AND ShareStatus = 'Open'` then you come along several lines later and filter on `ShareCloseDate >= (GetDate() - 90)` Is it possible that `ShareClosedDate` should be `NULL` when an account is open? If that's not it, just comment out the entire `WHERE` clause in the CTE after `AD.AccountPrimeNameDeathDate IS NULL` (maybe the `N = 1` from the query, too) then run the query, adding back in one filter at a time until you find the one that filters out everything.
0 Likes 0 ·
villiec avatar image villiec commented ·
@KenJ yes, it is possible for it to be null, thats what I was actually just playing with. It wants anything that is null or not expired more than 90 days so i changed that part of the query as well. I will try your suggestion of commenting out the criteria, hopefully this works! :) thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.