Varchar to data type Int

[Cpreston]

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

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

 

[Olaf Doschke]

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.

 

[Andrzejek]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

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:

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

 

[Cpreston]

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?

 

[gmmastros]

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

 

[Olaf Doschke]

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.

 

[Cpreston]

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.

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 [COLOR=#FCE94F]Right JOIN
                      dbo.[148VW_MDF2014p3] ON dbo.Product.ProductID = CAST( substring(dbo.[148VW_MDF2014p3].[Finished Product],3, 3)as Int)
                      Where [Finished Product] = 'am015044cr'
[/color]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

 

[Olaf Doschke]

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

... 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 [tt]SELECT * FROM dbo.Product Where dbo.Product.ProductID = 15044[/tt] 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.