Ошибка при преобразовании типа данных varchar к bigint

У меня есть таблица с данными, и один из столбцов содержит число, хранящееся как текст. Когда приложение обновляет его, оно записывает _BAK + datetime за номером.

Теперь я пытаюсь очистить базу данных, удалив все записи, которые имеют _BAK в столбце номера, где последний не должен быть удален.

id     sitenummer
28376  1441_BAK20130213151952032
28377  1441_BAK20130214142314705

В этом случае строка с ID 28376 является самой старой и должна быть удалена.

Я создал запрос, который должен сделать именно это:

;with sel1 AS (
select t1.ID,t1.sitenummer, CONVERT(BIGint,SUBSTRING(t1.sitenummer,CHARINDEX('_',t1.sitenummer,0)+4,50)) as Stamp1
from vdfkraan as t1
where t1.sitenummer like '%_BAK%' and (SELECT COUNT(SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) FROM vdfkraan as t2
where SUBSTRING(t1.sitenummer,0,CHARINDEX('_',t1.sitenummer,0))=SUBSTRING(t2.sitenummer,0,CHARINDEX('_',t2.sitenummer,0))) > 1
group by t1.id,t1.sitenummer)

, sel2 AS (
select t3.id, t3.sitenummer, t3.stamp1,
(select TOP(1) t4.stamp1 from sel1 as t4
WHERE SUBSTRING(t4.sitenummer,0,CHARINDEX('_',t4.sitenummer,0)) =SUBSTRING(t3.sitenummer,0,CHARINDEX('_',t3.sitenummer,0))
order by t3.Stamp1 DESC) AS stamp2 from sel1 as t3)

, sel3 AS (select id from sel2 where Stamp1=stamp2)

--delete FROM vdfkraan
--where id IN (SELECT t1.id FROM sel3 as t1)

--select * from sel2

Если я раскомментирую последнюю строку (выберите * из sel2), она создаст следующую таблицу:

id     sitenummer                 stamp1             stamp2
28376  1441_BAK20130213151952032  20130213151952032  20130213151952032
28377  1441_BAK20130214142314705  20130214142314705  20130213151952032

Таблица sel3 содержит одну запись с одним столбцом id = 28376.

Так что это работает так, как я хочу.

Теперь я прокомментирую строку select и раскомментирую строки Delete.

Теперь я получаю следующую ошибку:

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to bigint.

Таким образом, без строк удаления все в порядке, никаких ошибок, но с этим я получаю эту ошибку. Я проверил данные, не должно быть никаких проблем.

Что здесь происходит?

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

What I have tried:

farmerregistration table as follows

farmerid datatype Varchar(50) in farmerregistration table

farmerid Firstname Region Zone Section Village

1055662 Lacina OUNGAlo Diawala Nord Diwala

transaction table as follows

transactionid datatype Bigint in transaction table

transactionid Qty Price Paid Due

1055662 1 200 200 100

from the above i want output as follows

Firstname Region Zone Section Qty Price Paid

Lacina OUNGALo Diawala Nord 1 200 200

My query as follows

select a.firstname,a.Region,a.Zone,a.Section,b.Qty,b.Price,b.paid
from farmerregistration a,
transaction b where a.transactionid = b.farmerid

Note in farmerregistration table farmerid datatype is varchar(50)
in transaction table transactionid datatype is bigint

when i run the above code shows error as follows

Error converting data type varchar to bigint.

how to solve this error. from my above query what changes i have to made.

SqlZim already gave you a good method to avoid the error in his answer. However, in the question and in comments you seem curious as to why one query throws an error and the other does not. I am able to reproduce your issue:

CREATE TABLE dbo.X_BIGINT_TABLE (ID BIGINT NOT NULL);

INSERT INTO dbo.X_BIGINT_TABLE WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE TABLE dbo.X_NVARCHAR_TABLE (ID_NV NVARCHAR(10) NOT NULL);

INSERT INTO dbo.X_NVARCHAR_TABLE WITH (TABLOCK)
SELECT TOP (999) CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10))
FROM master..spt_values

UNION ALL

SELECT 'ZOLTAN';

This query works fine:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE ISNUMERIC(NV.ID_NV) = 1;

This query throws an error:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Msg 8114, Level 16, State 5, Line 25

Error converting data type nvarchar to bigint.

The SQL Server query optimizer can reorder elements of a query as it sees fit to try to find a query plan with a good enough estimated cost, as long as the changes do not affect the final results of the query. To illustrate the concept lets walk through one possible way the second query can be refactored. To be clear, this is not the actual step-by-step process that the query optimizer goes through for this example. Start with the query:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
) ZZ 
WHERE ZZ.ID = 500;

Push down the predicate:

SELECT *
FROM (
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1
) ZZ;

The derived table is no longer needed so get rid of that:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1

We know that BI.ID = NV.ID_NV so we can apply the filter on Z.ID to NV.ID_NV as well:

SELECT *
FROM dbo.X_BIGINT_TABLE BI 
INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
WHERE BI.ID = 500 AND ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500

The join no longer needs to be implemented as INNER JOIN because we are filtering down to a single value for both join columns. We can rewrite as a CROSS JOIN:

SELECT * 
FROM 
(
    SELECT *
    FROM dbo.X_BIGINT_TABLE BI 
    WHERE BI.ID = 500
) 
CROSS JOIN 
(
    SELECT *
    FROM dbo.X_NVARCHAR_TABLE NV
    WHERE ISNUMERIC(NV.ID_NV) = 1 AND NV.ID_NV = 500
);

If we look at the query plan for the second query we can tell that the end result is very similar to the final transformed query:

Here is the text of the filter predicate for reference:

CONVERT_IMPLICIT(bigint,[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0)=(500) 
AND isnumeric(CONVERT_IMPLICIT(varchar(20),[SE_DB].[dbo].[X_NVARCHAR_TABLE].[ID_NV] as [NV].[ID_NV],0))=(1)

If SQL Server evaluates the CONVERT_IMPLICIT part of the predicate before the isnumeric part then we get an error.

As a general rule, avoid relying on implied order of operations when writing SQL queries. You may have a query that works well today but starts to throw errors if data is added to the table or if a different query plan is chosen. There are, of course, exceptions (kind of). In practice, you will usually see the different parts of a CASE statement to evaluate in the order that you’ve written them, but even then it’s possible to get errors that you weren’t expecting. You can also add a superfluous TOP to parts of your query to encourage a certain order of operations. Consider the following query:

SELECT *
FROM (
    SELECT TOP (9223372036854775807) *
    FROM dbo.X_BIGINT_TABLE BI 
    INNER JOIN dbo.X_NVARCHAR_TABLE NV ON BI.ID = NV.ID_NV
    WHERE ISNUMERIC(NV.ID_NV) = 1
    ) ZZ 
WHERE ZZ.ID = 500;

You and I know that the TOP will not change the results of the query, However, there is not a guarantee to the optimizer that the derived table won’t return more than 9223372036854775807 rows so it must evaluate the TOP. Technically, in that query we ask for the first 9223372036854775807 rows and then we want to filter out rows with an ID different from 500. Pushing the ID = 500 predicate down to the derived table could change the results so SQL Server will not do that. In this example, the query executes without an error and the filtering is done at the very end: