Блог

Мне нужно сделать это:

 CAST(ISNULL([CompanyE.dbo.WipMaster.QtyToMake],0) As INT) As QtyToMake
  

Но SSMS жалуется на это сообщение:

 Msg 207, Level 16, State 1, Line 5
Invalid column name 'CompanyE.dbo.WipMaster.QtyToMake'.
  

Вот все мое утверждение, которое работает БЕЗ ISNULL:

 DECLARE @fromdate Date = '12-1-2018', @ToDate Date = '12-31-2019'

SELECT JobDeliveryDate As [Delivery Date], Job, QtyToMake As [PNL QTY], PSAW,   PPEP,   PINS,   PFRP, PASM,     PRES,   PTMI,   PCOT,  PFIL
        FROM
            (SELECT        CompanyE.dbo.WipMaster.JobDeliveryDate, CompanyE.dbo.WipMaster.Job, CAST(CompanyE.dbo.WipMaster.QtyToMake As INT) As QtyToMake, CAST(CompanyE.dbo.WipJobAllLab.QtyCompleted As INT) As QtyCompleted, CompanyE.dbo.WipJobAllLab.WorkCentre
            FROM            CompanyE.dbo.WipMaster INNER JOIN
                                     CompanyE.dbo.WipJobAllLab ON CompanyE.dbo.WipMaster.Job = CompanyE.dbo.WipJobAllLab.Job
            WHERE        (CompanyE.dbo.WipMaster.Job LIKE '%P') AND (CompanyE.dbo.WipMaster.Complete <> 'Y') AND (CompanyE.dbo.WipMaster.JobDeliveryDate BETWEEN @FromDate AND @ToDate)) 
AS D
            PIVOT (SUM(QtyCompleted) FOR WorkCentre IN(PSAW,    PPEP,   PINS,   PFRP,  PASM,    PRES,   PTMI,   PCOT, PFIL)) AS P
            ORDER BY Job;
  

@Gordon Linoff — Спасибо за указания по структуре кода, надеюсь, что теперь все в порядке. Я попробовал оба варианта, чтобы вернуть ноль, если null, но все равно получаю NULL в результатах. Вот код:

 DECLARE @fromdate Date = '2018-12-01', 
        @ToDate Date = '2019-12-31';

SELECT JobDeliveryDate AS [Delivery Date], Job, QtyToMake AS [PNL QTY], PSAW, 
        PPEP, PINS, PFRP, PASM, PRES, PTMI, PCOT, PFIL
FROM (SELECT wm.JobDeliveryDate, wm.Job, 
        CAST(ISNULL(wm.QtyToMake,0) AS INT) AS QtyToMake, 
        ISNULL(CAST(wjal.QtyCompleted AS INT),0) AS QtyCompleted, 
        wjal.WorkCentre
    FROM CompanyE.dbo.WipMaster AS wm INNER JOIN
        CompanyE.dbo.WipJobAllLab AS wjal ON wm.Job = wjal.Job
    WHERE (wm.Job LIKE '%P') AND 
        (wm.Complete <> 'Y') AND 
        (wm.JobDeliveryDate BETWEEN @FromDate AND @ToDate)) 
        AS D
PIVOT (SUM(QtyCompleted) FOR WorkCentre IN(PSAW, PPEP, PINS, PFRP, PASM, PRES, PTMI, PCOT, PFIL)) AS P
ORDER BY Job;
  

Как еще я могу превратить нули в нули?

Проблема в том, что вы написали:

 [CompanyE.dbo.WipMaster.QtyToMake]
  

Это поиск столбца с этим полным именем — точки, бородавки и все такое. Вы на самом деле намереваетесь:

 CompanyE.dbo.WipMaster.QtyToMake
  

Не используйте escape-символы, если они не нужны.

Как вы можете это прочитать? Научитесь использовать псевдонимы таблиц и правильные форматы дат:

 DECLARE @fromdate Date = '2018-12-01',
        @ToDate Date = '2019-12-31';

SELECT JobDeliveryDate As [Delivery Date], Job, QtyToMake As [PNL QTY],
       PSAW, PPEP, PINS, PFRP, PASM, PRES, PTMI,  PCOT, PFIL
FROM (SELECT wm.JobDeliveryDate, wm.Job,
             CAST(wm.QtyToMake As INT) As QtyToMake, 
             Cast(wjal.QtyCompleted As INT) As QtyCompleted, 
             wjal.WorkCentre
      FROM CompanyE.dbo.WipMaster wm INNER JOIN
           CompanyE.dbo.WipJobAllLab wjal
           ON wm.Job = wjal.Job
       WHERE wm.Job LIKE '%P' AND
             wm.Complete <> 'Y' AND
             wm.JobDeliveryDate BETWEEN @FromDate AND @ToDate
      ) D
PIVOT (SUM(QtyCompleted) FOR WorkCentre IN(PSAW,PPEP,   PINS, PFRP, PASM, PRES, PTMI, PCOT,PFIL)) AS P
ORDER BY Job;