Inserting Zero instead of Null

Hi Guys,

I have the following SQL Code:

SELECT TOP (100) PERCENT

CAST(Item AS nvarchar(32)) AS Item,

CAST(Customer AS nvarchar(12)) AS Customer,

CAST(Warehouse AS nvarchar(12)) AS Warehouse,

MAX(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END) AS INV1,

MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END) AS INV2,
MAX(CASE WHEN InvMonth = 'INV3' THEN CAST(Qty AS numeric(8, 0)) END) AS INV3,

MAX(CASE WHEN InvMonth = 'INV4' THEN CAST(Qty AS numeric(8,0)) END) AS INV4,

MAX(CASE WHEN InvMonth = 'INV5' THEN CAST(Qty AS numeric(8, 0)) END) AS INV5,
MAX(CASE WHEN InvMonth = 'INV6' THEN CAST(Qty AS numeric(8, 0)) END) AS INV6

FROM MVXReport.DMSExportStage1
GROUP BY CAST(Item AS nvarchar(32)), CAST(Customer AS nvarchar(12)), CAST(Warehouse AS nvarchar(12))
ORDER BY Item, Customer

And i am getting the following Results:

T100 APGL 10 1 6 2 1 3 3
T100 AUTOONE 10 NULL NULL NULL NULL NULL NULL
T100 CBCNSW 10 NULL NULL NULL NULL NULL NULL
T100 CBCQLD 10 NULL NULL NULL NULL 2 3
T100 CBCSA 10 NULL NULL NULL NULL NULL NULL
T100 CBCVIC 10 NULL NULL NULL NULL NULL NULL

I would like to know how to insert a 0 (zero) when the qty is null.. I have tried :

MAX(CASE WHEN InvMonth = 'INV1' THEN (CASE WHEN QTY IS NULL THEN (CAST(0 AS numeric(8, 0))) ELSE CAST(Qty AS numeric(8, 0)) END) END) AS INV1,

But it didnt seem to work.. If someone could point me in the right direction that would be wonderful..

thanks

Scotty

You could use COALESCE function for this task. Try change

CASE WHEN QTY IS NULL THEN (CAST(0 AS numeric(8, 0))) ELSE CAST(Qty AS numeric(8, 0)) END

to COALESCE(QTY,0)

|||

Try something like this:

isnull( Qty, 0 )

in each location where you have just Qty.

|||

Thanks Guys,

I have tried both of your solutions, but i am still getting null values. I can see why, as in the file that i am building these columns from, not every customer/Item every month has a record.. Hence why it is bringing back NULL..

Any other ideas...

thnkas

scotty

|||try this one instead

SELECT TOP (100) PERCENT
CAST(Item AS nvarchar(32)) AS Item,
CAST(Customer AS nvarchar(12)) AS Customer,
CAST(Warehouse AS nvarchar(12)) AS Warehouse,
COALESCE(MAX(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV1,
COALESCE(MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV2,
COALESCE(MAX(CASE WHEN InvMonth = 'INV3' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV3,
COALESCE(MAX(CASE WHEN InvMonth = 'INV4' THEN CAST(Qty AS numeric(8,0)) END),0) AS INV4,
COALESCE(MAX(CASE WHEN InvMonth = 'INV5' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV5,
COALESCE(MAX(CASE WHEN InvMonth = 'INV6' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV6
FROM MVXReport.DMSExportStage1
GROUP BY
CAST(Item AS nvarchar(32)),
CAST(Customer AS nvarchar(12)),
CAST(Warehouse AS nvarchar(12))
ORDER BY
Item
, Customer|||

use

MAX(Isnull(CASE WHEN InvMonth = 'INV1' THEN CAST(Qty AS numeric(8, 0)) END,0)) AS INV1

--or

Isnull(MAX(CASE WHEN InvMonth = 'INV2' THEN CAST(Qty AS numeric(8, 0)) END),0) AS INV2