Erreur de conversion du type de données varchar

Question

J'ai actuellement une table avec une colonne comme varchar . Cette colonne peut contenir des chiffres ou du texte. Lors de certaines requêtes, je la traite comme une bigint (je fais une jointure entre cette colonne et une colonne dans une autre table qui est bigint )

Tant qu'il n'y avait que des chiffres dans ce champ, je n'avais pas de problème, mais dès qu'une seule ligne contenait du texte et non des chiffres dans ce champ, j'obtenais le message "Error converting data type". varchar a bigint ." même si, dans la partie WHERE, je me suis assuré qu'aucun des champs de texte n'apparaissait.

Pour résoudre ce problème, j'ai créé une vue comme suit :

SELECT     TOP (100) PERCENT ID, CAST(MyCol AS bigint) AS MyCol
FROM         MyTable
WHERE     (isnumeric(MyCol) = 1)

Mais même si la vue n'affiche que les lignes avec des valeurs numériques et convertit Mycol en bigint, j'obtiens toujours un message d'erreur. Erreur de conversion du type de données varchar en bigint lors de l'exécution de la requête suivante :

SELECT * FROM MyView where mycol=1

Lorsque l'on fait des requêtes sur la vue, on ne doit pas savoir ce qui se passe derrière ! on doit simplement voir deux champs de type bigint ! ( voir l'image ci-jointe même mssql management studio montre que les champs de la vue sont des bigint).

Answer 1

OK. J'ai finalement créé une vue qui fonctionne :

SELECT TOP (100) PERCENT id, CAST(CASE WHEN IsNumeric(MyCol) = 1 THEN MyCol ELSE NULL END AS bigint) AS MyCol
FROM         dbo.MyTable
WHERE     (MyCol NOT LIKE '%[^0-9]%')

Merci à AdaTheDev y CodeByMoonlight . J'ai utilisé vos deux réponses pour arriver à ce résultat. (Merci aux autres rapporteurs aussi bien sûr)

Maintenant, lorsque je fais des jointures avec d'autres colonnes bigint ou que je fais quelque chose comme "SELECT * FROM MyView where mycol=1", le résultat est correct et ne présente aucune erreur. Je pense que le CAST dans la requête elle-même fait que l'optimiseur de requête ne regarde pas la table d'origine, comme Christian Hayter l'a dit, ce qui peut se produire avec les autres vues.

Answer 2

Pour répondre à votre question sur le message d'erreur : lorsque vous faites référence à un nom de vue dans une autre requête (en supposant qu'il s'agit d'une vue traditionnelle et non d'une vue matérialisée), le serveur SQL effectue effectivement un remplacement macro de la définition de la vue dans la requête consommatrice et l'exécute ensuite.

L'avantage de cette méthode est que l'optimiseur de requêtes peut faire un bien meilleur travail s'il voit la requête dans son ensemble, plutôt que d'optimiser la vue séparément comme une "boîte noire".

En conséquence, si une erreur se produit, les descriptions d'erreur peuvent paraître confuses car le moteur d'exécution accède aux tables sous-jacentes des données, et non à la vue.

Je ne sais pas comment sont traitées les vues matérialisées, mais j'imagine qu'elles sont traitées comme des tables, puisque les données de la vue sont mises en cache dans la base de données.

Ceci dit, je suis d'accord avec les réponses précédentes - vous devriez repenser la conception de votre tableau et séparer le texte et les valeurs de données entières dans des colonnes distinctes.

Answer 3

Essayez de changer votre vue en ceci :

SELECT TOP 100 PERCENT ID, 
Cast(Case When IsNumeric(MyCol) = 1 Then MyCol Else null End AS bigint) AS MyCol
FROM MyTable
WHERE (IsNumeric(MyCol) = 1)

Answer 4

Idéalement, vous devriez éviter de stocker les données sous cette forme - il serait intéressant de séparer les données BIGINT dans une colonne distincte pour des raisons de performance et de facilité d'interrogation.

Cependant, vous pouvez faire un JOIN comme dans cet exemple. Notez que je ne pas utiliser ISNUMERIC() pour déterminer s'il s'agit d'un BIGINT valide, car cela validerait des valeurs incorrectes qui provoqueraient une erreur de conversion (par exemple, des nombres décimaux).

DECLARE @MyTable TABLE (MyCol VARCHAR(20))
DECLARE @OtherTable TABLE (Id BIGINT)

INSERT @MyTable VALUES ('1')
INSERT @MyTable VALUES ('Text')
INSERT @MyTable VALUES ('1 and some text')
INSERT @MyTable VALUES ('1.34')
INSERT @MyTable VALUES ('2')
INSERT @OtherTable VALUES (1)
INSERT @OtherTable VALUES (2)
INSERT @OtherTable VALUES (3)

SELECT *
FROM @MyTable m
    JOIN @OtherTable o ON CAST(m.MyCol AS BIGINT) = o.Id
WHERE m.MyCol NOT LIKE '%[^0-9]%'

Mise à jour : Le seul moyen que j'ai trouvé pour faire fonctionner une clause WHERE pour une valeur entière spécifique sans faire un autre CAST() sur la colonne supposée bigint dans la clause where également, est d'utiliser une fonction définie par l'utilisateur :

CREATE  FUNCTION [dbo].[fnBigIntRecordsOnly]()
RETURNS @Results TABLE (BigIntCol BIGINT)
AS
BEGIN
INSERT @Results
SELECT CAST(MyCol AS BIGINT)
FROM MyTable
WHERE MyCol NOT LIKE '%[^0-9]%'
RETURN
END

SELECT * FROM [dbo].[fnBigIntRecordsOnly]() WHERE BigIntCol = 1

Je ne pense pas que ce soit une bonne idée en termes de performances, mais c'est une solution.

Answer 5

Le lien suivant donne quelques détails à ce sujet : sql-server-peculiar-issue-error-converting-varchar-to-bigint