SQL Server 2012：将数据类型nvarchar转换为float时出错

问题描述：

我正在使用SQL Server 2012.我正在运行将数据插入表的查询。我能够成功运行查询时我注释掉的INSERT INTO线，但是当我与INSERT INTO代码运行它，我得到这个错误：SQL Server 2012：将数据类型nvarchar转换为float时出错

Msg 8114, Level 16, State 5, Line 27
Error converting data type nvarchar to float.

代码：

DECLARE 
@CurrentYear smallint, 
@PriorMonth date, 
@CurrentMonth date, 
@NextMonth date, 
@CompanyCode nvarchar(255), 
@Delegate varchar(25), 
@NAM varchar(2), 
@Division varchar(2), 
@ReportingPeriod varchar(3) 

SELECT 
@CurrentYear = DATEPART(YY, GETDATE()), 
@PriorMonth = EOMONTH(GETDATE(), -2), 
@CurrentMonth = EOMONTH(GETDATE(), -1), 
@NextMonth = GETDATE(), 
@CompanyCode = 'JDE', 
@Delegate = 'Tony Scott', 
@NAM = 'US', 
@Division = 'MO' 

SELECT @ReportingPeriod = dd.FiscalPeriod 
FROM dbo.DateDimension dd (NOLOCK) 
WHERE dd.LastDayOfMonth = @CurrentMonth 

INSERT INTO dbo.GRIR_Main 

SELECT 
    a.CompanyCode, 
    PriceBlk = '', 
    QtyBlk = '', 
    DateBlk = '', 
    TrPrt = NULL, 
    POrg = '', 
    PGr = '', 
    [Description] = '', 
    Plant = '', 
    PlantDescription = '', 
    PurchReq = '', 
    RequistionerName = '', 
    a.GLAcct, 
    WBSElement = '', 
    SalesOrder = '', 
    Network = '', 
    CostCenter = '', 
    Reference = '', 
    a.DocumentNo, 
    a.ProfitCenter, 
    a.[Concatenate], 
    a.Assignment, 
    a.PurchDoc, 
    a.VendorType, 
    Vendor = '', 
    a.Name1, 
    Material = '', 
    ShortText = '', 
    a.ABSVALAmtinLC, 
    GRIRUpdated = NULL, 
    a.AmountinLC, 
    [Type] = '', 
    PostingDate = NULL, 
    a.DaysAged, 
    a.EYAging, 
    a.DaysAgedatMonthEnd, 
    a.EYAgingatMonthEnd, 
    a.RestatedPriorMonth, 
    a.EYAgingPriorMonth, 
    Slippage = 
     CASE 
      WHEN a.EYAging = a.EYAgingPriorMonth THEN '0' 
      ELSE a.EYAging 
     END, 
    a.Category1, 
    a.Analyst, 
    a.ReportingPeriod, 
    a.FY, 
    a.Delegate, 
    a.NAM, 
    a.Supplier, 
    a.Division, 
    a.Company, 
    a.CompanyFilter, 
    a.BU, 
    a.BusinessUnitDescription, 
    a.Buyer, 
    a.OrderNumber, 
    a.OrderType, 
    a.Line, 
    a.ReceiptDate, 
    a.ReceiptDoc, 
    a.QuantityReceived, 
    a.AmountReceived, 
    a.QuantityVouchered, 
    a.AmountVouchered, 
    a.QuantityOpen, 
    a.AmountOpen, 
    a.CurCod, 
    a.BUName, 
    a.Item, 
    a.GRIRPeriod, 
    a.Div 
FROM 
(SELECT 
    Delegate = @Delegate, 
    NAM = @NAM, 
    Assignment = CAST(j.[Order #] AS VARCHAR) + ' - ' + j.[Order Type] + ' - ' + CAST(j.[Line] AS VARCHAR), 
    DocumentNo = CAST(j.[Order #] AS VARCHAR), 
    AmountinLC = j.[Amount Open], 
    PurchDoc = j.[Order #], 
    ReportingPeriod = @ReportingPeriod, 
    FY = @CurrentYear, 
    Analyst = 
     CASE 
      WHEN @CompanyCode IN ('4433', 'PC1') THEN 'Karen Bastek' 
      WHEN @CompanyCode = '5620' THEN 'Alla O''Hara' 
      WHEN @CompanyCode = 'JDE' THEN 'Sharon Dooley' 
      WHEN @CompanyCode IN ('9461', '9462', '9463', '435E', '439Z') THEN 'Irina Shapshevich' 
     END, 
    CompanyCode = @CompanyCode, 
    j.Supplier, 
    Name1 = j.Name, 
    VendorType = 
     CASE 
      WHEN LEFT(j.Name, 7) = 'Siemens' THEN 'ICC' 
      ELSE '3rd Party' 
     END, 
    Div = j.[DIV #], 
    j.Company, 
    CompanyFilter = 
     CASE 
      WHEN j.Company IN ('31100','40021') THEN j.Company 
      ELSE 'All Others' 
     END, 
    BU = j.[BU #], 
    BusinessUnitDescription = j.[Business Unit Description], 
    j.Buyer, 
    OrderNumber = j.[Order #], 
    OrderType = j.[Order Type], 
    j.Line, 
    ReceiptDate = CONVERT(varchar(10), j.[Receipt Date], 101), 
    ReceiptDoc = j.[Receipt Doc], 
    QuantityReceived = j.[Qty Recvd], 
    AmountReceived = j.[Amount Rcvd], 
    QuantityVouchered = j.[Qty Vouchered], 
    AmountVouchered = j.[Amount Vouchered], 
    QuantityOpen = j.[Qty Open], 
    AmountOpen = j.[Amount Open], 
    CurCod = j.[Curr Code], 
    ABSVALAmtinLC = ABS(j.[Amount Open]), 
    Concatenate = CAST(j.Supplier AS VARCHAR) + CAST(j.[Order #] AS VARCHAR) + j.[Order Type] + CAST(CONVERT(varchar(10), j.[Receipt Date], 101) AS VARCHAR) + CAST(j.[Receipt Doc] AS VARCHAR), 
    DaysAged = DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth), 
    EYAging = 
     CASE 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 365 THEN 'h 365 + Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 180 THEN 'g 180 - 365 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 150 THEN 'f 150 - 180 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 120 THEN 'e 120 - 150 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 90 THEN 'd 90 - 120 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 60 THEN 'c 60 - 90 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @CurrentMonth) > 30 THEN 'b 30 - 60 Days' 
      ELSE 'a Current' 
     END, 
    DaysAgedatMonthEnd = DATEDIFF(DAY, j.[Receipt Date], @NextMonth), 
    EYAgingatMonthEnd = 
     CASE 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 365 THEN 'h 365 + Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 180 THEN 'g 180 - 365 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 150 THEN 'f 150 - 180 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 120 THEN 'e 120 - 150 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 90 THEN 'd 90 - 120 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 60 THEN 'c 60 - 90 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @NextMonth) > 30 THEN 'b 30 - 60 Days' 
      ELSE 'a Current' 
     END, 
    RestatedPriorMonth = DATEDIFF(DAY, j.[Receipt Date], @PriorMonth), 
    EYAgingPriorMonth = 
     CASE 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) < 0 THEN 'False' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 365 THEN 'h 365 + Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 180 THEN 'g 180 - 365 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 150 THEN 'f 150 - 180 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 120 THEN 'e 120 - 150 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 90 THEN 'd 90 - 120 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 60 THEN 'c 60 - 90 Days' 
      WHEN DATEDIFF(DAY, j.[Receipt Date], @PriorMonth) > 30 THEN 'b 30 - 60 Days' 
      ELSE 'a Current' 
     END, 
    Category1 = j.[Category 1], 
    GLAcct = CAST(j.[GL Account] AS float), 
    ProfitCenter = j.[GL Account], 
    Division = @Division, 
    BUName = m.[BU Name], 
    j.Item, 
    GRIRPeriod = @ReportingPeriod 

FROM 
    dbo.GRIR_JDE j (NOLOCK) 
    INNER JOIN dbo.MOBusinessSegment m (NOLOCK) ON j.[BU #] = m.PRCTR 
) AS a

这里表格定义，我试图插入数据：

CREATE TABLE [dbo].[GRIR_Main](
    [CompanyCode] [varchar](25) NULL, 
    [PriceBlk] [nvarchar](255) NULL, 
    [QtyBlk] [nvarchar](255) NULL, 
    [DateBlk] [nvarchar](255) NULL, 
    [TrPrt] [float] NULL, 
    [POrg] [nvarchar](255) NULL, 
    [PGr] [nvarchar](255) NULL, 
    [Description] [nvarchar](255) NULL, 
    [Plant] [nvarchar](255) NULL, 
    [PlantDescription] [nvarchar](255) NULL, 
    [PurchReq] [varchar](255) NULL, 
    [RequistionerName] [nvarchar](255) NULL, 
    [GLAcct] [float] NULL, 
    [WBSElement] [nvarchar](255) NULL, 
    [SalesOrder] [nvarchar](255) NULL, 
    [Network] [nvarchar](255) NULL, 
    [CostCenter] [nvarchar](255) NULL, 
    [Reference] [nvarchar](255) NULL, 
    [DocumentNo] [nvarchar](255) NULL, 
    [ProfitCenter] [nvarchar](255) NULL, 
    [Concatenate] [nvarchar](255) NULL, 
    [Assignment] [nvarchar](255) NULL, 
    [PurchDoc] [float] NULL, 
    [VendorType] [nvarchar](255) NULL, 
    [Vendor] [nvarchar](255) NULL, 
    [Name1] [nvarchar](255) NULL, 
    [Material] [nvarchar](255) NULL, 
    [ShortText] [nvarchar](255) NULL, 
    [ABSVALAmtinLC] [float] NULL, 
    [GRIRUpdated] [float] NULL, 
    [AmountinLC] [float] NULL, 
    [Type] [nvarchar](255) NULL, 
    [PostingDate] [datetime] NULL, 
    [DaysAged] [float] NULL, 
    [EYAging] [nvarchar](255) NULL, 
    [DaysAgedatMonthEnd] [float] NULL, 
    [EYAgingatMonthEnd] [nvarchar](255) NULL, 
    [RestatedPriorMonth] [float] NULL, 
    [EYAgingPriorMonth] [nvarchar](255) NULL, 
    [Slippage] [nvarchar](255) NULL, 
    [Category1] [nvarchar](255) NULL, 
    [Analyst] [nvarchar](255) NULL, 
    [ReportingPeriod] [nvarchar](255) NULL, 
    [FY] [float] NULL, 
    [Delegate] [nvarchar](255) NULL, 
    [NAM] [nchar](2) NULL, 
    [Supplier] [nvarchar](255) NULL, 
    [Division] [nvarchar](255) NULL, 
    [Company] [nvarchar](255) NULL, 
    [CompanyFilter] [nvarchar](255) NULL, 
    [BU] [nvarchar](255) NULL, 
    [BusinessUnitDescription] [nvarchar](255) NULL, 
    [Buyer] [nvarchar](255) NULL, 
    [OrderNumber] [float] NULL, 
    [OrderType] [nvarchar](255) NULL, 
    [Line] [float] NULL, 
    [ReceiptDate] [datetime] NULL, 
    [ReceiptDoc] [float] NULL, 
    [QuantityReceived] [float] NULL, 
    [AmountReceived] [float] NULL, 
    [QuantityVouchered] [float] NULL, 
    [AmountVouchered] [float] NULL, 
    [QuantityOpen] [float] NULL, 
    [AmountOpen] [float] NULL, 
    [CurCod] [nvarchar](255) NULL, 
    [BUName] [nvarchar](255) NULL, 
    [Item] [nvarchar](255) NULL, 
    [GRIRPeriod] [nvarchar](255) NULL, 
    [Div] [nvarchar](255) NULL 
) ON [PRIMARY]

我知道我有一个类型不匹配的地方，但我似乎无法找到它。以下是源数据的表格定义。

CREATE TABLE [dbo].[GRIR_JDE](
    [Supplier] [float] NULL, 
    [Name] [nvarchar](255) NULL, 
    [Order #] [float] NULL, 
    [Order Type] [nvarchar](255) NULL, 
    [Category 1] [nvarchar](255) NULL, 
    [Supplier Type] [nvarchar](255) NULL, 
    [DIV #] [nvarchar](255) NULL, 
    [Company] [nvarchar](255) NULL, 
    [BU #] [nvarchar](255) NULL, 
    [Business Unit Description] [nvarchar](255) NULL, 
    [Buyer] [nvarchar](255) NULL, 
    [Item Branch Buyer] [nvarchar](255) NULL, 
    [Line] [float] NULL, 
    [ESN] [nvarchar](255) NULL, 
    [Item] [nvarchar](255) NULL, 
    [Receipt Date] [datetime] NULL, 
    [Qty Recvd] [float] NULL, 
    [Amount Rcvd] [float] NULL, 
    [Qty Vouchered] [float] NULL, 
    [Amount Vouchered] [float] NULL, 
    [Qty Open] [float] NULL, 
    [Amount Open] [float] NULL, 
    [Curr Code] [nvarchar](255) NULL, 
    [Receipt Doc] [float] NULL, 
    [GL Account] [nvarchar](255) NULL, 
    [Time Stamp] [datetime] NULL 
) ON [PRIMARY]

你为什么到处都用'float'和'nvarchar（255）'？这只是马虎... [踢坏的习惯：选择错误的数据类型 - Aaron Bertrand - 2009-10-12]（http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad -habits-to-kick-using-the-wrong-data-type.aspx） – SqlZim

我还没有根据需要清理所有东西。现在我只是想开始工作，然后我会回去清理它，这是一个肮脏的过程。 – tsqln00b

答

如果你想运行它，后来它清理干净，使用的try_功能之一：

在SQL Server 2012及以上：每个将返回null当转换失败，而不是一个错误。

try_convert(datatype,val)
try_cast(val as datatype)
try_parse(val as datatype [using culture])

检查的j.[Order #]

类型，你正在使用它同时作为float和nvarchar(255)在：

... 
[DocumentNo] [nvarchar](255) NULL, 
[PurchDoc] [float] NULL,   
... 
DocumentNo = j.[Order #], 
PurchDoc = j.[Order #],

同样的问题在这里：

GLAcct = j.[GL Account], 
ProfitCenter = j.[GL Account],

我认为这是问题，我试图将其解决，它仍然给我错误。 GLAcct = CAST（j。[GL帐户] AS浮动） – tsqln00b

答

看来，GL科目字段的内容投射到浮球失败。我将主表上的字段更改为varchar，现在它可以工作。

SQL Server 2012：将数据类型nvarchar转换为float时出错

相关推荐