Field change as caused issues to store procedure

[Cpreston]

Hi

We have a filed called Ouref that usually held information like this 110445. Our systems have now change this so it as a prefix like this Journey: 110445, the SP is included.

It is now not bringing in the new Orrref and also not supplying the PurchaseOrderNumnbers now. I have taken out the

--where IsNumeric(Ourref) = 1
--and LEN(Ourref) < 8

It then is coming up with
Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'FX05 DXT' to data type int.

I have tried to convert the Ourref from varchar to int but I am going round in circles. COuld someon please put me right on the coding please, so I can get it working again. Thanks in advance

USE [Main DB]
GO
/****** Object:  StoredProcedure [dbo].[148-POjourneyinfo]    Script Date: 08/02/2021 12:23:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[148-POjourneyinfo]
@Fromjourneydate datetime,
@Tojourneydate datetime

AS

select Ourref, max(PurchaseOrderID) as PurchaseOrderID,
PurchaseOrderNumber
into #iantab
from PurchaseOrderHeader
where IsNumeric (Ourref) = 1
and LEN(Ourref) < 8
group by OurRef, PurchaseOrderNumber


SELECT     
                     IRT.OurRef, IRT.PurchaseOrderNumber,
 dbo.JourneyHeader.JourneyDate, dbo.Vehicle.Name, dbo.JourneyHeader.DriverName, dbo.JourneyHeader.VehicleName, 
                     dbo.JourneyHeader.JourneyNumber, dbo.JourneyHeader.TotalVolume, dbo.JourneyLine.DropNumber, dbo.OrderHeader.DeliveryAddress, 
                     dbo.Customer.Name AS [Customer Name], dbo.Customer.County, dbo.Customer.PostCode, dbo.JourneyHeader.NoOfStops, dbo.OrderHeader.udfSalesOrderOTIF, 
                     dbo.JourneyHeader.NoOfPacks, dbo.JourneyHeader.udfLoader1, dbo.JourneyHeader.udfLoader2, dbo.JourneyHeader.TransportCost, dbo.JourneyHeader.TripMileage
FROM         dbo.JourneyHeader INNER JOIN
                     dbo.JourneyLine ON dbo.JourneyHeader.JourneyID = dbo.JourneyLine.JourneyID INNER JOIN
                     dbo.Vehicle ON dbo.JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                     dbo.OrderHeader ON dbo.JourneyLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                     dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID
 left join #iantab AS IRT on dbo.JourneyHeader.JourneyNumber = IRT.OurRef

 WHERE dbo.JourneyHeader.JourneyDate between @Fromjourneydate and @Tojourneydate

ORDER BY dbo.JourneyHeader.JourneyNumber

 

[pjw001]

Are you saying that the value Ourref contains the value 'FX05 DXT' and you want to convert it to an integer?

The convert functions will convert an integer held in a varchar field into an integer field, but you need to have an integer to begin with.

What integer did you want 'FX05 DXT' to be converted to?

Or have I misunderstood your problem?

 

[Cpreston]

hi

I believe the field is varchar so going by the error it needs to be converted to int

Conversion failed when converting the varchar value 'AFP/M 4141' to data type int.

I have tried the below and may other attempts using convert but not working, Thanks

SELECT
CAST(IRT.OurRef as int),

 

[pjw001]

So what integer do you want the varchar value 'AFP/M 4141' to become? Do you simply want to remove the first 6 characters and convert the '4141' part into the integer field? If you want to convert the 'AFP/M' part to be an integer you will need an algorithm or lookup table.

 

[Cpreston]

Any information in the field I want it to keep as it is, I don't want the information to change.

So I want to keep 'AFP/M 4141' as 'AFP/M 4141'

I think the issue is left join #iantab AS IRT on dbo.JourneyHeader.JourneyNumber = IRT.OurRef

They are not matching anymore. I have used the RIGHT function to just pull out the last 6,but stillg ettting varchar to int messages.

The algorithm or lookup is abit beyon my knowledge Thanks

 

[Andrzejek]

I have used the RIGHT function to just pull out the last 6" characters.

If you have [tt]RIGHT('AFP/[highlight #FCE94F]M 4141[/highlight]', 6)[/tt], you are going to get the last 6 characters. Even if you do Right 5, you still get the last 5 characters, not a number. You need to CONVERT what you got to a Number (Integer)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Yes that is correct, but how do I convert, that's the issue

I have tried to convert and use case on the IRT.Ourref in the select command. But cannot get it working

Many thanks

 

[Andrzejek]

You may want to check CAST and CONVERT functions

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

I am not sure on the syntax bit have tried numerous ways

SELECT CONVERT(varchar, (IRT.OurRef)), give me Conversion failed when converting the varchar value 'AFP/M 6959' to data type int.

SELECT CAST(IRT.OurRef as int), gives me Conversion failed when converting the varchar value 'AFP/M 6959' to data type int.

I have tied all sorts of combination but cant seem to get it right

Thanks

 

[Andrzejek]

How about:
[tt]
SELECT [blue]CONVERT(int,[/blue] TRIM(RIGHT('AFP/M 4141', 5))[blue])[/blue] AS MyOutput

SELECT CONVERT(int, TRIM(RIGHT('AFP/M 6959', 5))) AS MyOutput[/tt]
Gave me 6959

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

I tired

Select
CONVERT(int, TRIM(RIGHT(IRT.OurRef,5))) As NewRef,

TRIM is not a built in function

Took TRIM out

CONVERT(int,(RIGHT(IRT.OurRef,5))) As NewRef,

Bu then get

Conversion failed when converting the varchar value 'AFP/M 6959' to data type int.

 

[Andrzejek]

So, what do you get if you hard-code the value of [blue]IRT.OurRef[/blue] ?
[tt]
SELECT CONVERT(int, RIGHT([blue]'AFP/M 6959'[/blue], 5)) AS MyOutput[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

SELECT CONVERT(int, RIGHT('AFP/M 4141', 5)) AS MyOutput
I get 4141

SELECT CONVERT(int, RIGHT('AFP/M 6959', 5)) AS MyOutput

I get 6959

I tried to apply this to the select but obviously incorrectly

CONVERT(int, RIGHT(IRT.OurRef, 5)) AS MyOutput,

Conversion failed when converting the varchar value 'AFP/M 6959' to data type int.

Thanks

 

[Andrzejek]

Is that your Select statement?
[tt]
Select CONVERT(int, RIGHT(IRT.OurRef, 5)) AS MyOutput
From #iantab AS IRT[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

There is a lot more code to the select statement but I ran

Select CONVERT(int, RIGHT(IRT.OurRef, 5)) AS MyOutput
From #iantab AS IRT

It gave me some esults but then stopped on Conversion failed when converting the varchar value '8 EVK' to data type int

Full Code is as now (also included the full stored procedure at the beginning of the htread, many thannks

SELECT     
                    
					CONVERT(int, RIGHT(IRT.OurRef, 5)) AS MyOutput,
					--Convert(int,IRT.[OurRef],6),
					--IRT.OurRef, 
					IRT.PurchaseOrderNumber,
                     dbo.JourneyHeader.JourneyDate, dbo.Vehicle.Name, dbo.JourneyHeader.DriverName, dbo.JourneyHeader.VehicleName, 
                     dbo.JourneyHeader.JourneyNumber, dbo.JourneyHeader.TotalVolume, dbo.JourneyLine.DropNumber, dbo.OrderHeader.DeliveryAddress, 
                     dbo.Customer.Name AS [Customer Name], dbo.Customer.County, dbo.Customer.PostCode, dbo.JourneyHeader.NoOfStops, dbo.OrderHeader.udfSalesOrderOTIF, 
                     dbo.JourneyHeader.NoOfPacks, dbo.JourneyHeader.udfLoader1, dbo.JourneyHeader.udfLoader2, dbo.JourneyHeader.TransportCost, dbo.JourneyHeader.TripMileage
FROM         dbo.JourneyHeader INNER JOIN
                     dbo.JourneyLine ON dbo.JourneyHeader.JourneyID = dbo.JourneyLine.JourneyID INNER JOIN
                     dbo.Vehicle ON dbo.JourneyHeader.VehicleID = dbo.Vehicle.VehicleID INNER JOIN
                     dbo.OrderHeader ON dbo.JourneyLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                     dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID
 left join #iantab AS IRT on dbo.JourneyHeader.JourneyNumber = IRT.OurRef

 

[Andrzejek]

I know that you have a lot more in your Select statement, but when I want to find the issue, I start simple.
It is possible that you cannot CONVERT your OurRef field to an Integer because you don’t know what this field actually contains. Some values do work, some other do not.

In my opinion, you cannot change your Integer field in your table to a character field (which now can contain anything) and hope that your logic will work, especially when you use this field to connect to other source/table. Get back to your original table’s setup, with your OurRef field as an Integer, and add an additional (VARCHAR) field to your table which will contain all ‘AFP/M’ and such. You can always concatenate these 2 fields.

You may want to do:
[tt]
Select Distinct OurRef From #iantab Order By 1
[/tt]
To see which values will and will not Convert to Integer.
It would be tempting to introduce some logic to detect how to Convert other ‘issue’ values, but I don’t think you will be able to cover all existing and future issues with this field :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Many thanks for your reply's I may have to totally rethink the way this is working.

Out of interest, the below code creates the a temp table using data from the PurcahseOrderHeader. If I leave the code like this it leave out information like the Joueny: 123456 in Ourref.
If I mark out the where isnumeric and the len < 8 then they come through. Could someone explain what these 2 lines actually do, this was not my code originally but a colleagues who as left. Thanks
--where IsNumeric (Ourref) = 1
--and LEN(Ourref) < 8


select Ourref, max(PurchaseOrderID) as PurchaseOrderID,
PurchaseOrderNumber
into #iantab
from PurchaseOrderHeader
where IsNumeric (Ourref) = 1
and LEN(Ourref) < 8
group by OurRef, PurchaseOrderNumber

 

[Andrzejek]

You can read about it here: IsNumeric and Len

Is that your data in Ourref field?
[tt]
Ourref
the Joueny: 123456[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Thanks for the information on things

yes that is the data in ourref field. which I believe the ISNUmeric and Len does not like.

 

[Andrzejek]

The way I see it (and please, correct me of I am wrong):
You have the field Ourref (that used to be defined as Number? Even if not, it contained just ‘numbers’, or text that could be easily converted to a number, and nothing else) and you used this field as a Foreign Key to another (Primary Key?) field (defined as Number?) JourneyNumber in dbo.JourneyHeader table. And all of that worked OK.

You re-defined (?) Ourref field to contain any text (like ‘the Joueny: 123456’) and now the connection to dbo.JourneyHeader.JourneyNumber doesn’t work anymore.

So, you want to have this ‘any text’ field to be converted to something that will match again the values in dbo.JourneyHeader.JourneyNumber

Did I get it right?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson