sql server查询中不接受不同的整数值 [英] Different integer values are not accepted in sql server query

问题描述

我在执行查询时遇到了一个奇怪的情况。我对Sql Server执行查询的方式感到惊讶。

Hi,

I have come across a strange scenario while executing a query. I was surprised by the way Sql Server was executing the query.

引用：

SELECT TOP 10 MR.MerchantContactFirstName +''+ MR.MerchantContactLastName AS MerchantName，

AISLTrans.DeviceID，AISLTrans.TransactionID，AISLTrans.CardNumber，AISLTrans.TransactionDateTime，

AISLTrans .TransactionStatus，AISTRNSTYPE.TransactionType AS TransactionRequestType，AISLTrans.TransactionAmount，

（AISLTrans.BasePointEarn + AISLTrans.BonusPointEarn）AS'获奖点'

来自AISLoyaltyTransactions AISLTrans INNER JOIN

MerchantRegistration MR

ON AISLTrans.MerchantID = MR.MerchantID INNER JOIN

AISTransactionTypes AISTRNSTYPE

ON AISLTrans.ProcessingCode = AISTRNSTYPE .ProcessingCode AND

AISLTrans.TransactionType = AISTRNSTYPE.MessageType WHERE A ISLTrans.TransactionType = 0 200

AND AISLTrans.ProcessingCode = 071000 ORDER BY AISLTrans.TransactionDateTime DESC

SELECT TOP 10 MR.MerchantContactFirstName + ' ' + MR.MerchantContactLastName AS MerchantName,
AISLTrans.DeviceID,AISLTrans.TransactionID, AISLTrans.CardNumber, AISLTrans.TransactionDateTime,
AISLTrans.TransactionStatus, AISTRNSTYPE.TransactionType AS TransactionRequestType,AISLTrans.TransactionAmount,
(AISLTrans.BasePointEarn+AISLTrans.BonusPointEarn)AS 'Point Awarded'
FROM AISLoyaltyTransactions AISLTrans INNER JOIN
MerchantRegistration MR
ON AISLTrans.MerchantID = MR.MerchantID INNER JOIN
AISTransactionTypes AISTRNSTYPE
ON AISLTrans.ProcessingCode = AISTRNSTYPE.ProcessingCode AND
AISLTrans.TransactionType = AISTRNSTYPE.MessageType WHERE AISLTrans.TransactionType = 0200
AND AISLTrans.ProcessingCode = 071000 ORDER BY AISLTrans.TransactionDateTime DESC

In上面查询字段ISLTrans，TransactionType适用于所有值，除了0200它接受所有整数值虽然字段类型是varchar。



但是当我给出值时如0200它抛出异常，说不能将varchar转换为int。



我正在寻找为什么查询仍然在执行，虽然我提供整数值而不是varchar，如果它正在执行为什么它不接受我提供的所有整数值。



谢谢，

Praveen Nelge

In above query the field ISLTrans,TransactionType works fine for all the values except 0200 it accepts all interger values though the field type is of varchar.

But when I give the value as 0200 it throws exception saying cannot convert varchar to int.

I am looking for why the query is still executing though I am providing integer value instead of varchar and if it is executing why its not accepting all the integer values which I provide.

Thanks,
Praveen Nelge

推荐答案

因为它试图将AISLTrans.TransactionType转换为in t，并且在某些情况下失败。

可能不是导致问题的0200而是其他信息导致缺少非数字值进行比较。

尝试t他的：

Because it it trying to convert AISLTrans.TransactionType to an in t, and failing in some cases.
Probably, it's not the 0200 that causes the problem but that the other information causes a lack of non-numeric values to compare.
Try this:

...
AISLTrans.TransactionType = AISTRNSTYPE.MessageType WHERE AISLTrans.TransactionType = '0200' 
AND AISLTrans.ProcessingCode = '071000' ORDER BY AISLTrans.TransactionDateTime DESC

这篇关于sql server查询中不接受不同的整数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！