无法将字符值转换为货币.char值的语法不正确

Question

当我认为有错误时，我收到一个错误"无法将字符值转换为货币。字符值语法不正确。"。我们正在执行主表中的select语句，连接到另一个表，然后尝试将这些值插入到另一表中。查询如下：

*--CREATE TABLE [ETL_Test_Source].[ETL].[Master_BAR_INS_XFER_TXN_FACT_NPR_0_ETL_Test_Source_No_valid_subdivisions]
--   (
--     Appl_Database_Id VARCHAR(256) ,
--     Automatic_Transfer_Amount MONEY ,
--     Batch_Date_Id DATETIME ,
--     Bill_Number VARCHAR(256) ,
--     Collector_Id VARCHAR(256) ,
--     Crp_Bot_Comp_Val_Id VARCHAR(256) ,
--     Facility_Id VARCHAR(256) ,
--     Insurance_Id VARCHAR(256) ,
--     Manual_Transfer_Amount MONEY ,
--     Net_Transfer_Amount MONEY ,
--     Patient_Account_Id VARCHAR(256) ,
--     Transaction_Urn VARCHAR(256)
--   )  
DECLARE @Database VARCHAR(256) = 'ETL_Test_Source' ,
@RunDateTime DATETIME = 'Apr 19 2015  4:56PM' ,
@ScheduleDateTime DATETIME = 'Apr 19 2015  4:56PM' ,
@Subdivision VARCHAR(256) = 'No_valid_subdivisions' ,
@DateAwareFromDateTime DATETIME = 'Jan  1 2000 12:00AM' ,
@DateAwareThruDateTime DATETIME = 'Jan  1 2101 12:00AM';
INSERT  INTO [ETL_Test_Source].[ETL].[Master_BAR_INS_XFER_TXN_FACT_NPR_0_ETL_Test_Source_No_valid_subdivisions]
( [Appl_Database_Id] ,
[Automatic_Transfer_Amount] ,
[Batch_Date_Id] ,
[Bill_Number] ,
[Collector_Id] ,
[Crp_Bot_Comp_Val_Id] ,
[Facility_Id] ,
[Insurance_Id] ,
[Manual_Transfer_Amount] ,
[Net_Transfer_Amount] ,
[Patient_Account_Id] ,
[Transaction_Urn]
)
SELECT  XFRTXN.SourceID ,
XFRTXN.AutoXferAmount ,
COLL.BatchDateTime ,
COLL.BillNumber ,
LEDG.Collector ,  
FINS.Corporation ,
VISIT.FacilityID ,
XFRTXN.InsuranceID ,
XFRTXN.ManualEdit ,
XFRTXN.AutoXferAmount ,
XFRTXN.BillingID ,
XFRTXN.TransactionID
FROM    [dbo].[BarTxnInsuranceDetail] AS XFRTXN
LEFT OUTER JOIN [dbo].[BarVisits] AS VISIT ON [VISIT].[BillingID] = [XFRTXN].[BillingID]
AND [VISIT].[SourceID] = [XFRTXN].[SourceID]
LEFT OUTER JOIN [dbo].[BarVisitFinancialData] AS FINS ON [FINS].[BillingID] = [XFRTXN].[BillingID]
AND [FINS].[SourceID] = [XFRTXN].[SourceID]
INNER JOIN [dbo].[BarInsuranceLedger] AS LEDG ON [LEDG].[BillingID] = [XFRTXN].[BillingID]
AND [LEDG].[InsuranceID] = [XFRTXN].[InsuranceID]
AND [LEDG].[SourceID] = [XFRTXN].[SourceID]
INNER JOIN [dbo].[BarCollectionTransactions] AS COLL ON [COLL].[BillingID] = [XFRTXN].[BillingID]
AND [COLL].[SourceID] = [XFRTXN].[SourceID]
AND [COLL].[TransactionID] = [XFRTXN].[TransactionID]
WHERE   COLL.Type IN ( 'p', 'A', 'R', 'P', 'q', 'm', 'v', 'b' )
AND COLL.BatchDateTime BETWEEN @DateAwareFromDateTime
AND     @DateAwareThruDateTime*

看起来COLL.SourceID，尽管在COLL中被定义为VARCHAR，以及我们将其插入的表，SQL似乎认为它是MONEY类型。事实证明，我们碰巧选择的所有值都是整数。此查询现在正在产生此错误，但以前没有。现在，当查询被编辑为以下内容时：

--CREATE TABLE [ETL_Test_Source].[ETL].[Master_BAR_INS_XFER_TXN_FACT_NPR_0_ETL_Test_Source_No_valid_subdivisions]
--   (
--     Appl_Database_Id VARCHAR(256) ,
--     Automatic_Transfer_Amount MONEY ,
--     Batch_Date_Id DATETIME ,
--     Bill_Number VARCHAR(256) ,
--     Collector_Id VARCHAR(256) ,
--     Crp_Bot_Comp_Val_Id VARCHAR(256) ,
--     Facility_Id VARCHAR(256) ,
--     Insurance_Id VARCHAR(256) ,
--     Manual_Transfer_Amount MONEY ,
--     Net_Transfer_Amount MONEY ,
--     Patient_Account_Id VARCHAR(256) ,
--     Transaction_Urn VARCHAR(256)
--   )  
DECLARE @Database VARCHAR(256) = 'ETL_Test_Source' ,
@RunDateTime DATETIME = 'Apr 19 2015  4:56PM' ,
@ScheduleDateTime DATETIME = 'Apr 19 2015  4:56PM' ,
@Subdivision VARCHAR(256) = 'No_valid_subdivisions' ,
@DateAwareFromDateTime DATETIME = 'Jan  1 2000 12:00AM' ,
@DateAwareThruDateTime DATETIME = 'Jan  1 2101 12:00AM';
INSERT  INTO [ETL_Test_Source].[ETL].[Master_BAR_INS_XFER_TXN_FACT_NPR_0_ETL_Test_Source_No_valid_subdivisions]
( [Appl_Database_Id] ,
[Automatic_Transfer_Amount] ,
[Batch_Date_Id] ,
[Bill_Number] ,
[Collector_Id] ,
[Crp_Bot_Comp_Val_Id] ,
[Facility_Id] ,
[Insurance_Id] ,
[Manual_Transfer_Amount] ,
[Net_Transfer_Amount] ,
[Patient_Account_Id] ,
[Transaction_Urn]
)
SELECT  XFRTXN.SourceID ,
XFRTXN.AutoXferAmount ,
COLL.BatchDateTime ,
COLL.BillNumber ,
LEDG.Collector ,  
FINS.Corporation ,
VISIT.FacilityID ,
XFRTXN.InsuranceID ,
XFRTXN.ManualEdit ,
XFRTXN.AutoXferAmount ,
XFRTXN.BillingID ,
XFRTXN.TransactionID
FROM    [dbo].[BarTxnInsuranceDetail] AS XFRTXN
LEFT OUTER JOIN [dbo].[BarVisits] AS VISIT ON [VISIT].[BillingID] = [XFRTXN].[BillingID]
AND [VISIT].[SourceID] = [XFRTXN].[SourceID]
LEFT OUTER JOIN [dbo].[BarVisitFinancialData] AS FINS ON [FINS].[BillingID] = [XFRTXN].[BillingID]
AND [FINS].[SourceID] = [XFRTXN].[SourceID]
INNER JOIN [dbo].[BarInsuranceLedger] AS LEDG ON [LEDG].[BillingID] = [XFRTXN].[BillingID]
AND [LEDG].[InsuranceID] = [XFRTXN].[InsuranceID]
AND [LEDG].[SourceID] = [XFRTXN].[SourceID]
INNER JOIN [dbo].[BarCollectionTransactions] AS COLL ON [COLL].[BillingID] = [XFRTXN].[BillingID]
AND [COLL].[SourceID] = [XFRTXN].[SourceID]
AND [COLL].[TransactionID] = [XFRTXN].[TransactionID]
WHERE   COLL.Type IN ( 'p', 'A', 'R', 'P', 'q', 'm', 'v', 'b' )
**AND (ISNUMERIC(COLL.BillingID) = 1
OR ISNUMERIC(COLL.BillingID) = 0)** 
AND COLL.BatchDateTime BETWEEN @DateAwareFromDateTime
AND     @DateAwareThruDateTime

查询成功运行。WHERE子句不过滤任何内容。同样奇怪的是，当它们在COLL中明确定义为VARCHAR值时，SQL会将它们解释为数值。值得注意的是，此修复程序仅适用于COLL表。如果我们使用XFERTXN.BillingID，则会导致相同的错误。

我相信这个错误可能是SQL错误，我想知道是否有人注意到类似的情况。我们插入的表中所有MONEY类型的列都有有效的货币值，但这些值不是问题所在。BillingID似乎是这里的问题，因为它包含整数值，但在流程中的每个表上都被定义为VARCHAR。SQL似乎以某种方式认为它们是货币值，但失败了，尽管该列中的每个值都是货币值。有人知道为什么这个错误会出现在第一个代码示例中吗？

谢谢！

Answer 1

您正试图将XFRTXN.ManualEdit(类型varchar)插入到Manual_Transfer_Mamount(类型money)中，因此得到"无法将char值转换为money"。

至于您的两个查询之间的差异，请仔细检查以确保这确实是唯一的差异：

AND (ISNUMERIC(COLL.BillingID) = 1 OR ISNUMERIC(COLL.BillingID) = 0)