sql - 为什么我会收到“列名或提供的值的数量与表定义不匹配”？

问题描述

Message只有在将 2 列 (和ErrorId) 添加到 2 个临时表后，我才会收到此错误。为什么？是我要添加的代码吗？在添加之前，此 MS Report 查询中的所有内容都运行良好。这是一个更大的问题，但我正在分解成更小的部分。谢谢你的帮助。

请忽略 NOLOCKS：

我只在这个临时加载表中添加了Message和ErrorId列

DECLARE @LoadsTbl TABLE
(
     LoadId     varchar(52),
     Message    varchar(Max),    -- after adding this it errors
     ErrorId    varchar(20)      -- after adding this it errors
)

DECLARE @SelLoadsTbl TABLE
(
     SourceSystem varchar(20),
     Message      varchar(Max),  -- after adding this it errors
     ErrorId      varchar(20),   -- after adding this it errors
     LoadId       varchar(52)
)

下面的所有内容都是我要添加的内容。

DECLARE
    @Sep             CHAR(1),
    @PkupFromDttemp  DATETIME,
    @PkupToDttemp    DATETIME,
    @LoadNbrtemp     VARCHAR(20),
    @InvoiceNbrtemp  VARCHAR(20)

SET @PkupFromDttemp = @PkupFromDt
SET @PkupToDttemp = @PkupToDt
SET @LoadNbrtemp = @LoadNbr
SET @InvoiceNbrtemp = @InvoiceNbr
SET @Sep = ';'

SET @PkupToDttemp = dateadd(day, 1, @PkupToDttemp )

IF @CutoffTime IS NOT NULL
AND @CutoffTime <> '00:00'
    BEGIN
    SET @PkupFromDttemp = cast((etopsuser.fnDateOnly(@PkupFromDttemp) + ' ' + @CutoffTime ) as datetime)
    SET @PkupToDttemp = cast((etopsuser.fnDateOnly(@PkupToDttemp) + ' ' + @CutoffTime ) as datetime)
    END

    IF @LoadNbrtemp IS NOT NULL
    BEGIN
    SET @LoadNbrtemp = rtrim(ltrim(@LoadNbrtemp))

    IF charindex('%',@LoadNbrtemp) = 0
    BEGIN
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    WHERE fpl.LoadNbr = @LoadNbrtemp
    END
    ELSE
    BEGIN

    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl  WITH (NOLOCK)
    WHERE fpl.LoadNbr LIKE @LoadNbrtemp
    ORDER BY fpl.LoadId

    SET ROWCOUNT 0
    END
    END
    ELSE IF @InvoiceNbrtemp IS NOT NULL
    BEGIN
    SET @InvoiceNbrtemp = rtrim(ltrim(@InvoiceNbrtemp))

    IF charindex('%',@InvoiceNbrtemp) = 0
    BEGIN
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN dbo.vwFPFrtInvoiceLoads vwinv WITH (NOLOCK)
    ON fpl.LoadId = vwinv.LoadId
    WHERE vwinv.InvoiceNbr = @InvoiceNbrtemp
    END
    ELSE
    BEGIN

    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN dbo.vwFPFrtInvoiceLoads vwinv WITH (NOLOCK)
    ON fpl.LoadId = vwinv.LoadId
    WHERE vwinv.InvoiceNbr LIKE @InvoiceNbrtemp
    ORDER BY fpl.LoadId
    
    SET ROWCOUNT 0
    END
    END
    ELSE IF @InvoiceStatusList IS NULL
    AND @CarrierIdList IS NULL
    AND @ScacList IS NULL
    BEGIN
    -- no invoice filters
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
      ON fpl.OwnerId = uo.OwnerId
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    END
    ELSE
    BEGIN
    -- invoice filters - non statement bill
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
    ON fpl.OwnerId = uo.OwnerId
    INNER JOIN dbo.FPLoadTenderedSCACs lts WITH (NOLOCK)
    ON fpl.LoadId = lts.LoadId AND lts.ActiveInd = 'Y'
    INNER JOIN dbo.CMSCAC cms WITH (NOLOCK)
    ON lts.SCAC = cms.SCAC
    LEFT OUTER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON fpl.LoadId = inv.LoadId AND inv.BillType <> 'STATEMENT BILL'
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    AND ( @InvoiceStatusList IS NULL OR inv.InvoiceStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@InvoiceStatusList,@Sep)) )
    AND ( @CarrierIdList IS NULL OR cms.CarrierId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@CarrierIdList,@Sep)) )
    AND ( @ScacList IS NULL OR lts.Scac IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@ScacList,@Sep)) )

    -- invoice filters - statement bill
    INSERT INTO @LoadsTbl
    SELECT
    fpl.LoadId
    FROM dbo.FPLoads fpl WITH (NOLOCK)
    INNER JOIN etopsuser.fnSecurUserAppOwners(@UserId,'FP') uo
    ON fpl.OwnerId = uo.OwnerId
    INNER JOIN dbo.FPLoadTenderedSCACs lts WITH (NOLOCK)
    ON fpl.LoadId = lts.LoadId AND lts.ActiveInd = 'Y'
    INNER JOIN dbo.CMSCAC cms WITH (NOLOCK)
    ON lts.SCAC = cms.SCAC
    INNER JOIN dbo.FPFrtInvoiceSBLoads sb WITH (NOLOCK)
    ON fpl.LoadId = sb.LoadId
    LEFT OUTER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON sb.InvoiceId = inv.InvoiceId AND inv.BillType = 'STATEMENT BILL'
    WHERE (@OwnerId IS NULL OR fpl.OwnerId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@OwnerId,@Sep)) )
    AND ( @PkupFromDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) >= @PkupFromDttemp )
    AND ( @PkupToDttemp IS NULL OR isnull(fpl.PkupActlDtTm,fpl.PkupDtTm) < @PkupToDttemp )
    AND ( @SourceSystemList IS NULL OR fpl.SourceSystem IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@SourceSystemList,@Sep)) )
    AND ( @MovementStatusList IS NULL OR fpl.MovementStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementStatusList,@Sep)) )
    AND ( @FPStatusList IS NULL OR fpl.FPStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@FPStatusList,@Sep)) )
    AND ( @RateStatusList IS NULL OR fpl.RateStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@RateStatusList,@Sep)) )
    AND ( @MovementTypeList IS NULL OR fpl.MovementType IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@MovementTypeList,@Sep)) )
    AND ( @InvoiceStatusList IS NULL OR inv.InvoiceStatus IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@InvoiceStatusList,@Sep)) )
    AND ( @CarrierIdList IS NULL OR cms.CarrierId IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@CarrierIdList,@Sep)) )
    AND ( @ScacList IS NULL OR lts.Scac IN (SELECT ListVal FROM etopsuser.fnParseListToTable(@ScacList,@Sep)) )
END
    
    --ME

    --EndChange


INSERT INTO @SelLoadsTbl
SELECT fpl.SourceSystem, fpl.LoadId
FROM @LoadsTbl tmp
INNER JOIN dbo.FPLoads fpl WITH (NOLOCK)
    ON tmp.LoadId = fpl.LoadId
WHERE fpl.FPStatus <> 'DO NOT PAY'
ORDER BY
     fpl.SourceSystem
    ,fpl.LoadId

SELECT DISTINCT
     fpl.SourceSystem as SourceSystem
    ,fpl.LoadId as LoadId
    ,etopsuser.fnFPLoadTenderedScac(fpl.LoadId) as Scac
    ,fpl.MovementType as MovementType
    ,cast(etopsuser.fnDateOnly(isnull(fpl.PkupActlDtTm, fpl.PkupDtTm))as datetime) as PkupDt
    ,right(etopsuser.fnMilDate(isnull(fpl.PkupActlDtTm, fpl.PkupDtTm)),5) as PkupTm
    ,fpl.MovementStatus as MovementStatus
    ,fpl.FPStatus           as FPLoadStatus
    ,fpl.RateStatus         as RatingStatus
    ,etopsuser.fnFmtCityStateZip(st1.StopCity, st1.StopState, st1.StopZip) as 'Origin'
    ,etopsuser.fnFmtCityStateZip(st2.StopCity, st2.StopState, st2.StopZip) as 'Destination'
     
    
    ,null as InvoiceId
    ,null as InvoiceNbrSeq
    ,null as InvoiceStatus
    ,null as BillType
    
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPLoads fpl WITH (NOLOCK)
    ON tmp.LoadId = fpl.LoadId
INNER JOIN FPStops ST1 WITH (NOLOCK)
    ON fpl.LoadId = ST1.LoadId AND ST1.StopNbr = 1
INNER JOIN FPStops ST2 WITH (NOLOCK)
    ON fpl.LoadId = ST2.LoadId AND ST2.StopNbr = fpl.TotalStops
--WHERE (fpl.LoadId IN (SELECT LoadId FROM @LoadsTbl) )

union 

SELECT 
    tmp.SourceSystem as SourceSystem
    ,inv.LoadId as LoadID
    ,inv.BilledScac as Scac
    ,null as MovementType
    ,null as PkupDt
    ,null as PkupTm
    ,null as MovementStatus
    ,null           as FPLoadStatus
    ,null           as RatingStatus
    ,null as 'Origin'
    ,null as 'Destination'


    
    ,inv.InvoiceId as InvoiceId
    ,inv.InvoiceNbr + '-' + inv.InvoiceSeqNbr as InvoiceNbrSeq
    ,inv.InvoiceStatus as InvoiceStatus
    ,inv.BillType as BillType
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON tmp.LoadId = inv.LoadId
WHERE (inv.LoadId IN (SELECT LoadId FROM @SelLoadsTbl) )
AND inv.BillType <> 'STATEMENT BILL'
--
UNION
--
SELECT 
    tmp.SourceSystem  as SourceSystem
    ,sb.LoadId as LoadID
    --My Addition
    --Addition end
    ,inv.BilledScac as Scac
    ,null as MovementType
    ,null as PkupDt
    ,null as PkupTm
    ,null as MovementStatus
    ,null           as FPLoadStatus
    ,null           as RatingStatus
    ,null as 'Origin'
    ,null as 'Destination'
    
    ,inv.InvoiceId as InvoiceId
    ,inv.InvoiceNbr + '-' + inv.InvoiceSeqNbr as InvoiceNbrSeq
    ,inv.InvoiceStatus as InvoiceStatus
    ,inv.BillType as BillType
    
    
FROM @SelLoadsTbl tmp
INNER JOIN dbo.FPFrtInvoiceSBLoads sb WITH (NOLOCK)
ON tmp.LoadId = sb.LoadId
--me

--me end
INNER JOIN dbo.FPFrtInvoice inv WITH (NOLOCK)
    ON sb.InvoiceId = inv.InvoiceId AND inv.BillType = 'STATEMENT BILL'
WHERE (sb.LoadId IN (SELECT LoadId FROM @SelLoadsTbl) )
--
ORDER BY
     1, 2, 3

标签： sqlreporting-servicesmicrosoft-reporting

sql - 为什么我会收到“列名或提供的值的数量与表定义不匹配”？

问题描述

解决方案

推荐阅读