I need to do this:

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

But SSMS complains with this message:

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

Here's my whole statement that works WITHOUT the 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 - Thanks for the pointers on the code structure, hopefully that is now up to snuff. I tried both option to return a zero if null but still get NULLs in the results. Here's the code:

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;

How else can I turn NULLs into zeroes?

1 Answers

1
Gordon Linoff On

The problem is that you have written:

[CompanyE.dbo.WipMaster.QtyToMake]

This is looking for a column with that full name -- periods, warts, and all. You actually intend:

CompanyE.dbo.WipMaster.QtyToMake

Don't use the escape characters if they are not needed.

How can you read this? Learn to use table aliases and proper date formats:

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;