x

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

more ▼

asked Oct 10, 2013 at 01:27 PM in Default

avatar image

villiec
60 3 3 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Oct 10, 2013 at 06:28 PM

avatar image

KenJ
25k 3 13 20

@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'?

Oct 10, 2013 at 06:39 PM villiec

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.

Oct 10, 2013 at 07:30 PM KenJ

@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!

Oct 10, 2013 at 07:47 PM villiec
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1096
x53

asked: Oct 10, 2013 at 01:27 PM

Seen: 1451 times

Last Updated: Oct 23, 2013 at 07:01 PM

Copyright 2018 Redgate Software. Privacy Policy