December 5, 2017 at 2:26 pm
Hi - I've got a development server where my T-SQL code is working fine, but when I port it to my production server it's breaking. Here's the code:
DECLARE @ModelNumber AS int = 67787
;WITH UsedNumbers (clnum, clname1)
AS
(
SELECT clnum COLLATE SQL_Latin1_General_CP1_CI_AS AS clnum, clname1 COLLATE SQL_Latin1_General_CP1_CI_AS AS clname1
FROM ELITE.son_db.dbo.client
WHERE clnum NOT LIKE '%[A-Z]%'
),
OrderedNumbers (clnum, clnum_int, clname1)
AS
(
SELECT TOP 500000 clnum, CAST(clnum AS int) AS clnum_int, clname1
FROM UsedNumbers
WHERE CAST(clnum AS int) >= @ModelNumber
ORDER BY clnum
)
SELECT TOP 1 REPLACE(STR(previd+1,6),' ','0') AS previd
FROM (SELECT clnum_int, LAG(clnum_int) OVER (ORDER BY clnum) previd
FROM OrderedNumbers) q
WHERE previd <> clnum_int - 1
ORDER BY clnum_int
When I run this code on the development server, it returns results without a problem. When I run it on the production server, I get this error:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '57235A' to data type int.
Conversion failed when converting the varchar value '57235A' to data type int.
On both servers, if I replace the last SELECT statement with:
SELECT * FROM UsedNumbers WHERE clnum LIKE '57%'
orSELECT * FROM OrderedNumbers WHERE clnum LIKE '57%'
I get no results on the development server or the production server, so I'm totally confused by the error message.
Side points: ELITE in the initial SELECT statement refers to linked servers found on both servers. Both linked servers point to the same database, i.e., there are not development and production versions pointed to by the linked servers. 67787 is a seed value that was given to me by the end users; it has no significance to the program other than they want to find the next number after that number.
Can anyone suggest to me what could be going on here?
December 5, 2017 at 2:44 pm
My guess the value is in the prev_id field.
The real issue is that you are switching back and forth between varchar/int. Pick one data type and stick with it depending on what the values represent. For instance, US Zip Codes are CHARACTER even though they only ever contain numbers.
If you want to find the previous id, you should use LAG/LEAD not arithmetic, because ids are not necessarily sequential.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2017 at 2:50 pm
drew.allen - Tuesday, December 5, 2017 2:44 PMMy guess the value is in the prev_id field.
The real issue is that you are switching back and forth between varchar/int. Pick one data type and stick with it depending on what the values represent. For instance, US Zip Codes are CHARACTER even though they only ever contain numbers.If you want to find the previous id, you should use LAG/LEAD not arithmetic, because ids are not necessarily sequential.
Drew
I'm sure you're right. Unfortunately, clnum is a varchar field and I can't change that. But, excluding the very few clnum's that have letters in them, I need to find the next clnum after my Model Number. Can you suggest another way to do this, and more importantly, why it's working on one server but not another? Thanks!
December 5, 2017 at 2:58 pm
Melanie Peterson - Tuesday, December 5, 2017 2:50 PMdrew.allen - Tuesday, December 5, 2017 2:44 PMMy guess the value is in the prev_id field.
The real issue is that you are switching back and forth between varchar/int. Pick one data type and stick with it depending on what the values represent. For instance, US Zip Codes are CHARACTER even though they only ever contain numbers.If you want to find the previous id, you should use LAG/LEAD not arithmetic, because ids are not necessarily sequential.
Drew
I'm sure you're right. Unfortunately, clnum is a varchar field and I can't change that. But, excluding the very few clnum's that have letters in them, I need to find the next clnum after my Model Number. Can you suggest another way to do this, and more importantly, why it's working on one server but not another? Thanks!
It's because your data is not the same on both servers. One server has an alphanumeric value. Since we don't have access to your data, we cannot tell you where that alphanumeric value is.
It's also not clear what you are trying to accomplish with your query, so it's difficult to tell you how to rewrite it. Perhaps if you tried to explain what you're trying to do, we could offer a better approach.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2017 at 3:05 pm
drew.allen - Tuesday, December 5, 2017 2:58 PMMelanie Peterson - Tuesday, December 5, 2017 2:50 PMdrew.allen - Tuesday, December 5, 2017 2:44 PMMy guess the value is in the prev_id field.
The real issue is that you are switching back and forth between varchar/int. Pick one data type and stick with it depending on what the values represent. For instance, US Zip Codes are CHARACTER even though they only ever contain numbers.If you want to find the previous id, you should use LAG/LEAD not arithmetic, because ids are not necessarily sequential.
Drew
I'm sure you're right. Unfortunately, clnum is a varchar field and I can't change that. But, excluding the very few clnum's that have letters in them, I need to find the next clnum after my Model Number. Can you suggest another way to do this, and more importantly, why it's working on one server but not another? Thanks!
It's because your data is not the same on both servers. One server has an alphanumeric value. Since we don't have access to your data, we cannot tell you where that alphanumeric value is.
It's also not clear what you are trying to accomplish with your query, so it's difficult to tell you how to rewrite it. Perhaps if you tried to explain what you're trying to do, we could offer a better approach.
Drew
The initial data I'm pulling is through the ELITE linked server and this linked server points to the same database (our Accounting system), whether I'm in my production or development environment, so I'm not getting different data in the two environments. The query actually pulls in data from a local table as well (I've taken that out to simplify things), but I realize that it makes the purpose of the query harder to understand. I'm trying to combine data from our Accounting system and this local table and then find then next clnum after my @ModelNumber that does NOT exist in either table. And no, 57235A does not exist in the local table, either in development or production; I checked. Besides, even when I take that part out and run the query as I showed in my question above (i.e., just pulling only from my ELITE table/the Accounting system), I still get the same error message.
December 5, 2017 at 3:53 pm
Fix appropriately for the local database, what does it return?WITH UsedNumbersLS AS (
SELECT
clnum
, clname1
FROM
OPENQUERY([ELITE],'SELECT CAST(clnum as INT) as clnum, clname1 COLLATE SQL_Latin1_General_CP1_CI_AS AS clname1 FROM [son_db].[dbo].[client] WHERE clnum NOT LIKE ''%[^0-9]%''')oq
),
UsedNumbersLocal (
SELECT
CAST(clnum as INT) as clnum
, clname1 COLLATE SQL_Latin1_General_CP1_CI_AS AS clname1
FROM
dbo.localdb
WHERE clnum NOT LIKE '%[^0-9]%'
), OrderedNumbers (
SELECT
clnum
, clname1
FROM
UsedNumbersLS
UNION
SELECT
clnum
, clname1
FROM
UsedNumbersLocal
)
SELECT * FROM OrderedNumbers WHERE clnum >= @ModelNumber;
December 7, 2017 at 3:31 am
You may use this approach:
WHERE CAST(CASE WHEN clnum like '%[^0-9]%' then NULL ELSE clnum END AS int) >= @ModelNumber
Case statement will eliminate all values containing at least 1 non-numerical character
_____________
Code for TallyGenerator
June 18, 2018 at 11:45 pm
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply