INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Varchar to data type Int

Varchar to data type Int

(OP)
Hi

I have a query which is having an issues with the message of

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

Any ideas please, I have googled and tried cast as you can see, this is the code field which where the 'AM015044CR' would be located in the result. Not hot on coding, I also tried the convert but got the same message. Any ideas please, thanks

The SQL query is as below

CODE --> sql

SELECT      WorksOrderHeader.TotalVolumeIn, CAST(dbo.[148VW_MDF2014p3].[Finished Product] as Int), dbo.[148VW_MDF2014p3].[Raw Board], dbo.[148VW_MDF2014p3].[RB Actual], dbo.[148VW_MDF2014p3].[RB Available], 
                      dbo.[148VW_MDF2014p3].[RB Allocated], dbo.[148VW_MDF2014p3].[RB OnOrder], dbo.[148VW_MDF2014p3].[FP Actual], dbo.[148VW_MDF2014p3].[FP Available], 
                      dbo.[148VW_MDF2014p3].[FP Allocated], dbo.[148VW_MDF2014p3].[FP AwaitingProcessing], dbo.[148VW_MDF2014p3].[FP Sold Last Month], 
                      dbo.[148VW_MDF2014p3].[FP Sold 2 Months Ago], dbo.[148VW_MDF2014p3].[FP Sold 3 Months Ago], SUM(dbo.[148VW_MDF2014p3].[FP Into Loose Last Month]) 
                      AS [FP Into Loose Last Month], SUM(dbo.[148VW_MDF2014p3].[FP Into Loose 2 Months Ago]) AS [FP Into Loose 2 Months Ago], 
                      SUM(dbo.[148VW_MDF2014p3].[FP Into Loose 3 Months Ago]) AS [FP Into Loose 3 Months Ago]
FROM         dbo.WorksOrderLine INNER JOIN
                      dbo.WorksOrderHeader ON dbo.WorksOrderLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID INNER JOIN
                      dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID INNER JOIN
                      dbo.[148VW_MDF2014p3] ON dbo.Product.ProductID = dbo.[148VW_MDF2014p3].[Finished Product]
                      Where [Finished Product] = 'am015044cr'
GROUP BY dbo.[148VW_MDF2014p3].[Finished Product], dbo.[148VW_MDF2014p3].[Raw Board], dbo.[148VW_MDF2014p3].[RB Actual], 
                      dbo.[148VW_MDF2014p3].[RB Available], dbo.[148VW_MDF2014p3].[RB Allocated], dbo.[148VW_MDF2014p3].[RB OnOrder], dbo.[148VW_MDF2014p3].[FP Actual], 
                      dbo.[148VW_MDF2014p3].[FP Available], dbo.[148VW_MDF2014p3].[FP Allocated], dbo.[148VW_MDF2014p3].[FP AwaitingProcessing], 
                      dbo.[148VW_MDF2014p3].[FP Sold Last Month], dbo.[148VW_MDF2014p3].[FP Sold 2 Months Ago], dbo.[148VW_MDF2014p3].[FP Sold 3 Months Ago],
                      dbo.WorksOrderHeader.TotalVolumeIn 

RE: Varchar to data type Int

To convert a varchar to int, the only allowed characers in the varchar are digits and whitespace (tabs & spaces), so no, you can't convert 'AM015044CR' to an int. Also not with CAST.

Bye, Olaf.

RE: Varchar to data type Int

If you could convert 'AM015044CR' to int, what would you expect as a result? 15044 ?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Varchar to data type Int

You have your cast in the select statement, where it doesn't matter. You would only get the error "Conversion failed" when you are converting. Don't get me wrong, your value cannot be converted to an integer, but you also don't need the conversion in the select clause. The only time you would put a convert/cast in the select clause is when you want to change the data type of the returned column.

I think your problem is here:

CODE

INNER JOIN dbo.[148VW_MDF2014p3] 
    ON dbo.Product.ProductID = dbo.[148VW_MDF2014p3].[Finished Product] 

If I was a betting man, I would say that Product.ProductID is an integer. Based on your where clause, it's obvious that [148VW_MDF2014p3].[Finished Product] is a varchar. My guess is that after fixing the join (and removing the cast/convert from the select clause), that your query will work.

** I make no guarantees



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Varchar to data type Int

(OP)
Hi

Thanks for the replies and understand about the varchar to int. Gmmastros how would I change the join, you mentiuoned fixing it, in what way?

RE: Varchar to data type Int

I think you are joining on the wrong columns. Bear in mind that this is just a guess because I don't know your data. I suspect that you want to use a different column in the product table or a different column in the 148VW_MDF2014p3 table.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Varchar to data type Int

Indeed, if two comumns you join are different type, it's a strong hint if not proof, that you don't want to join by these columns.

If [Finished Product] is some alphanumerical product code that probably will be defined in the products table aside of the int product id, so you could make two joins. Like George, I can only make guesses, you will need to know your data and columns and foreign key and primary key definitions to get correct joins. You don't get a working query by guessing something similarlynamed will work as join condition.

Bye, Olaf.

RE: Varchar to data type Int

(OP)
Hi

Again thanks for the replys. I have played a little with the code and join and now have this (changed code in yellow. I am expecting a value in Totalvoluemin but I am getting NULL. Appreciate you guys don't know the data so cannot advise as good as normal.

CODE --> sql

SELECT      WorksOrderHeader.TotalVolumeIn,dbo.[148VW_MDF2014p3].[Finished Product], dbo.[148VW_MDF2014p3].[Raw Board], dbo.[148VW_MDF2014p3].[RB Actual], dbo.[148VW_MDF2014p3].[RB Available], 
                      dbo.[148VW_MDF2014p3].[RB Allocated], dbo.[148VW_MDF2014p3].[RB OnOrder], dbo.[148VW_MDF2014p3].[FP Actual], dbo.[148VW_MDF2014p3].[FP Available], 
                      dbo.[148VW_MDF2014p3].[FP Allocated], dbo.[148VW_MDF2014p3].[FP AwaitingProcessing], dbo.[148VW_MDF2014p3].[FP Sold Last Month], 
                      dbo.[148VW_MDF2014p3].[FP Sold 2 Months Ago], dbo.[148VW_MDF2014p3].[FP Sold 3 Months Ago], SUM(dbo.[148VW_MDF2014p3].[FP Into Loose Last Month]) 
                      AS [FP Into Loose Last Month], SUM(dbo.[148VW_MDF2014p3].[FP Into Loose 2 Months Ago]) AS [FP Into Loose 2 Months Ago], 
                      SUM(dbo.[148VW_MDF2014p3].[FP Into Loose 3 Months Ago]) AS [FP Into Loose 3 Months Ago]
FROM         dbo.WorksOrderLine INNER JOIN
                      dbo.WorksOrderHeader ON dbo.WorksOrderLine.WorksOrderID = dbo.WorksOrderHeader.WorksOrderID LEFT JOIN
                      dbo.Product ON dbo.WorksOrderLine.ProductID = dbo.Product.ProductID Right JOIN
                      dbo.[148VW_MDF2014p3] ON dbo.Product.ProductID = CAST( substring(dbo.[148VW_MDF2014p3].[Finished Product],3, 3)as Int)
                      Where [Finished Product] = 'am015044cr'
GROUP BY dbo.[148VW_MDF2014p3].[Finished Product], dbo.[148VW_MDF2014p3].[Raw Board], dbo.[148VW_MDF2014p3].[RB Actual], 
                      dbo.[148VW_MDF2014p3].[RB Available], dbo.[148VW_MDF2014p3].[RB Allocated], dbo.[148VW_MDF2014p3].[RB OnOrder], dbo.[148VW_MDF2014p3].[FP Actual], 
                      dbo.[148VW_MDF2014p3].[FP Available], dbo.[148VW_MDF2014p3].[FP Allocated], dbo.[148VW_MDF2014p3].[FP AwaitingProcessing], 
                      dbo.[148VW_MDF2014p3].[FP Sold Last Month], dbo.[148VW_MDF2014p3].[FP Sold 2 Months Ago], dbo.[148VW_MDF2014p3].[FP Sold 3 Months Ago],
                      dbo.WorksOrderHeader.TotalVolumeIn 

RE: Varchar to data type Int

If you limit to just one code anyway, do yourself a favor and simply make it

CODE

... Right JOIN dbo.[148VW_MDF2014p3] ON [Finished Product]='am015044cr'
Where dbo.Product.ProductID = 15044 

That will make it only join the one record.

I have the strong feeling if you query SELECT * FROM dbo.Product Where dbo.Product.ProductID = 15044 you will find a column having the am015044cr value and then that should be used for the joining, or you find a field in [148VW_MDF2014p3] that has the normal integer product id 15044, then that should be used for joining. You should not need to use a cast/convert or any function to join data.

For the generalisation of this your approach is weak and will easily break, if the numeric part is not at the same position. Your data should have the separate single values you need for your joining.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close