SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conversion Failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  04:59:26  Show Profile  Reply with Quote
Hello,

Can anybody please tell me why this code:

UPDATE dbo.tblNotes SET Comments = 'Cancelled', Type = 'GC', EntryDate = '30-Jul-2013', 
	DateDue = '30-Jul-2013', DateMet = '30-Jul-2013', UserName = 'Steven', closedBy = 'Steven', RaisedBy = 'Steven'
FROM dbo.tblPolicy AS p
JOIN dbo.tblNotes AS n
ON p.PolicyID = n.ParentKey
WHERE p.PolicyID = '7132694'


Gives this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'MS/ABB' to data type int.

This is an insurance database and I am trying to add a note to a policy. I have checked the data types for each column and the date entries are set to datetime and the text entries are set to varchar so I can work out why I am getting a conversion failure.

Thanks
Steven

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  05:07:26  Show Profile  Reply with Quote
check the datatypes of PolicyID and ParentKey.
I think ParentKey is int and PolicyID is varchar, There are some values in PolicyID which are non numeric which is causing this error on the join condition I guess.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  05:42:34  Show Profile  Reply with Quote
Yes that's it but PolicyID is int and ParentKey is varchar.

How do I get round that?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  05:46:35  Show Profile  Reply with Quote
need to add a filter on ParentKey to look only for numeric data

ie like

ParentKey NOT LIKE '%[^0-9]%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  06:32:23  Show Profile  Reply with Quote
Do I put that in the WHERE clause like this:

UPDATE dbo.tblNotes SET Comments = 'Cancelled', Type = 'GC', EntryDate = '30-Jul-2013', 
	DateDue = '30-Jul-2013', DateMet = '30-Jul-2013', UserName = 'Steven', closedBy = 'Steven', 
	RaisedBy = 'Steven'
FROM dbo.tblPolicy AS p
JOIN dbo.tblNotes AS n
ON p.PolicyID = n.ParentKey
WHERE p.PolicyID = '7132694' AND n.ParentKey NOT LIKE '%[^0-9]%'


Because that still gives the same error.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 07/31/2013 :  06:55:27  Show Profile  Visit webfred's Homepage  Reply with Quote
You are doing an update in tblNotes for an explicit value (ParentKy = '7132694').
Can you explain why you are joining tblPolicy?

If that join is really needed then do the join by converting PolicyID to varchar for example like this:
ON convert(varchar(30),p.PolicyID) = ltrim(rtrim(n.ParentKey))

Best would be to correct your database model to NOT have different datatypes in such columns...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  07:34:14  Show Profile  Reply with Quote
Thanks webfred that worked.

This query is just a test, I put the join in so that if in future I needed to add a comment to a batch of policies then I could use the batch number which appears in the policy table but not the notes table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  07:43:31  Show Profile  Reply with Quote
quote:
Originally posted by sql_monkey

Thanks webfred that worked.

This query is just a test, I put the join in so that if in future I needed to add a comment to a batch of policies then I could use the batch number which appears in the policy table but not the notes table.


I would definitely prompt you to do the change to data model as suggested by Fred. doing convertion on joins is not good idea

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  07:46:42  Show Profile  Reply with Quote
Where would you do the conversion if not on the join?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  07:53:25  Show Profile  Reply with Quote
quote:
Originally posted by sql_monkey

Where would you do the conversion if not on the join?


The suggestion was to change the data model to make datatypes the same so that there's no need of conversion at all!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  08:12:01  Show Profile  Reply with Quote
OK I see but I cant do that, I dont administer the database, I'm only running reports.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  08:18:15  Show Profile  Reply with Quote
then no other way than to do the conversion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_monkey
Starting Member

United Kingdom
19 Posts

Posted - 07/31/2013 :  08:41:34  Show Profile  Reply with Quote
Thanks for the help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000