Блог

Я получаю эту ошибку только после добавления 2 столбцов ( Message и 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
     
     
     

    
 

Вероятно, причиной здесь являются ваши утверждения о вставке. Когда вы

 INSERT INTO @LoadsTbl
 

без списка столбцов — ожидается, что будут предоставлены все столбцы таблицы. Вы выбираете только LoadId.
Если вы добавите:

 INSERT INTO @LoadsTbl (LoadId)
...
 

или добавьте значения-заполнители в инструкции select для сообщений и идентификаторов ошибок

 INSERT INTO @LoadsTbl
SELECT LoadId, '' Message, '' ErrorId

 

Это должно решить эту ошибку.

Вот хороший способ записи вставок, который поддерживает SQL Server.

Как правило, рекомендуется избегать написания вставок таким образом, чтобы ваше предложение SELECT оставалось полностью синхронизированным с целевым DDL. В противном случае, если вы когда-нибудь добавите столбец в таблицу, все, что у вас есть, будет в нее добавлено, сломается. Это ошибка, которую вы получаете. В таблице есть три поля, но вы даете ей только одно в поле «ВЫБРАТЬ».

 CREATE TABLE DEMO_SYNTAX
  ( Field1 VARCHAR(100),
    Field2 VARCHAR(1000)
  )

INSERT INTO DEMO_SYNTAX (Field2)
    SELECT 'dummy';
    
INSERT INTO DEMO_SYNTAX (Field1,Field2)
    SELECT 'dummy2','dummy3';