我正在运行以下存储的优先级。结果集绑定在 gridView 控件上。
@EmbossLine varchar(20),
@TransactionTypeID int,
@DateFrom datetime,
@DateTo datetime
AS
Select
pt.TransactionDate,
m.MerchantName1,
pt.TerminalID,
pt.SequenceNumber,
tt.TransactionType,
case TT.TransactionTypeID when 0 then PT.TotalAmount else 'null' end 'PointsEarned',
case TT.TransactionTypeID when 2 then PT.TotalAmount else 'null' end 'PointsRedemeed'
from POS_Transactions PT
inner join TransactionType TT on TT.TransactionTypeID = PT.TransactionTypeID
inner join CardBalance CB on CB.PAN = PT.PAN
inner join Terminal T on T.TerminalID = PT.TerminalID
inner join Merchant M on M.MerchantID = T.MerchantID
where
(PT.TransactionDate>=@DateFrom and PT.TransactionDate<=@DateTo)
and (PT.TransactionTypeID = @TransactionTypeID or @TransactionTypeID ='-999')
and(PT.PAN=@EmbossLine or PT.PAN='-999')
order by PT.TransactionDate desc
实际上我想做的是,每当我的TransactionTypeID
不等于0
或2
时,我想打印null
。 但它会引发运行时异常
Cannot convert a char value to money. The char value has incorrect syntax.
我知道这是一个转换问题,想知道当我的条件计算结果为 false 时如何打印一些值
更改以下两行:
case TT.TransactionTypeID when 0 then PT.TotalAmount else 'null' end 'PointsEarned',
case TT.TransactionTypeID when 2 then PT.TotalAmount else 'null' end 'PointsRedemeed'
对此:
case TT.TransactionTypeID when 0 then PT.TotalAmount else null end 'PointsEarned',
case TT.TransactionTypeID when 2 then PT.TotalAmount else null end 'PointsRedemeed'
您希望在else
条件下生成null
值。您当前正在生成单词 'null'
这是转换失败的地方 - 您无法将单词(char
数据类型)'null'
转换为货币数据类型。
编辑:
为此,您需要将值强制转换为字符串。但这意味着您将返回一个字符串,而不是将货币数据类型返回给客户端。
这种事情实际上应该在客户端中处理,而不是在SQL Server中处理。无论如何,请尝试将所有内容转换为字符串:
case TT.TransactionTypeID when 0 then Cast(PT.TotalAmount as varchar(50)) else 'null' end 'PointsEarned',
case TT.TransactionTypeID when 2 then Cast(PT.TotalAmount as varchar(50)) else 'null' end 'PointsRedemeed'
这样做。
case TT.TransactionTypeID when 0 then cast(PT.TotalAmount as varchar) else 'null' end 'PointsEarned',
case TT.TransactionTypeID when 2 then cast(PT.TotalAmount as varchar) else 'null' end 'PointsRedemeed'
只需将TotalAmount
投给瓦尔查尔,它应该可以工作。