MS SQL Server
  1. MS SQL Server
  3. Erreur lors de la conversion en float en ignorant le filter WHERE
Intereting Posts
Comment puis-je surveiller le matériel de la database Azure SQL? Row_number () Partition par – Pour les valeurs répétées Trouver des combinaisons valides basées sur la masortingce T-SQL Two Where Clauses sur la même colonne Convertir deux colonnes en 5 lignes dans le server sql Que se passe-t-il si vous modifiez une procédure stockée pendant son exécution? Connecter les tables dans une database fonctionne par Apache et le port 80 Une façon rapide de faire un globe oculaire possible dupliquer des lignes dans un tableau? Sql Server 2008 Requête pour mettre null pour les valeurs non existantes La conversion a échoué lors de la conversion de la valeur nvarchar en type de données int Mise à jour dans une fonction SQL Server Problème de format PHP MS SQL ID Comment démarrer un SQL Server LocalDB via IIS Est-il possible d'get le text analysé d'un SqlCommand avec SqlParameters?

Erreur lors de la conversion en float en ignorant le filter WHERE

J'ai une table avec une colonne varchar , PaymentRef, contenant une variété de text et de données numériques. En outre, il y a une colonne, CurrencyAmount du type de données float : 

 PaymentRef CurrencyAmount ---------- -------------- EUR 100,00 100 EUR 50,00 50 USD 25,00 25.2 Auth#: 98103 NULL Auth#: 98104 NULL Transferred from 2356 NULL Transferred to 1356 NULL

Maintenant, chaque fois qu'un logging contient un PaymentRef sur le formulaire "EUR ##, ##", "USD ##, ##", etc. Je dois comparer la valeur numérique après le code de devise, à la valeur de la colonne CurrencyAmount.

La requête suivante fonctionne très bien: 

 SELECT CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) AS PaymentRefNumeric, CurrencyAmount FROM MyTable WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK')

Toutefois, si j'essaie de comparer la valeur casted à la colonne CurrencyAmount, comme dans: 

 WITH CTE AS ( SELECT CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) AS PaymentRefNumeric, CurrencyAmount FROM MyTable WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK') ) SELECT * FROM CTE WHERE PaymentRefNumeric <> CurrencyAmount

J'ai l'erreur suivante: 

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

Je sais que je peux facilement mettre le résultat de la première requête dans une table temporaire, puis effectuer ma comparaison, mais je me request quelle est la cause de cette erreur. Je crois que cela a quelque chose à voir avec l'optimiseur de requête essayant de CAST les valeurs PaymentRef à FLOAT, avant d'appliquer le premier filter WHERE. Y a-t-il un moyen de s'assurer que le filter est appliqué avant que les valeurs ne soient lancées? (C'est pourquoi j'ai essayé d'utiliser CTE, mais même sans CTE, j'ai toujours la même erreur).

voici une solution de contournement comme décrit dans mon commentaire 

 WITH CTE AS ( SELECT case when LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK') then CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) else null end AS PaymentRefNumeric, CurrencyAmount FROM #t WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK') ) SELECT * FROM CTE WHERE PaymentRefNumeric <> CurrencyAmount

ne pouvez-vous pas changer la condition où le CTE à 

 WHERE CurrencyAmount IS NOT NULL

? votre exemple de données ressemble à cela serait ok … la requête fonctionne avec la condition modifiée …

vous pouvez également changer l'autre où la condition à 

 WHERE currencyamount is not null and PaymentRefNumeric <> CurrencyAmount

Utilisez la fonction IsNull: 

 WITH CTE AS ( SELECT CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) AS PaymentRefNumeric, CurrencyAmount FROM MyTable WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK') ) SELECT * FROM CTE WHERE PaymentRefNumeric <> IsNull(CurrencyAmount, -1) -- Check if is null so replce it

Utilisez ISNUMERIC pour vérifier si la conversion a réussi sinon returnner NULL. Cela couvrira toutes les bases.

Pour les users de SQL2012, la fonction TRYCAST peut être utilisée. Si la dissortingbution échoue, la valeur NULL est renvoyée. Remplacez simplement l'expression CASE par:

SQL2012 TRY_CAST(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.')) 

 WITH CTE AS ( SELECT CASE WHEN ISNUMERIC(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.'))=1 THEN(REPLACE(SUBSTRING(PaymentRef, 5, 100), ',', '.') AS FLOAT) ELSE NULL END PaymentRefNumeric ,CurrencyAmount FROM MyTable WHERE LEFT(PaymentRef, 3) IN ('EUR', 'USD', 'SEK') ) SELECT * FROM CTE WHERE PaymentRefNumeric <> CurrencyAmount