SQLTeam.com | Weblogs | Forums

How to replace null with 0


#1

hello

i have the following query and i want when bestand is null it ould be replaced with 0
can you help please?
thank you

SELECT dbo.v_verbrauchnew.Disponent, dbo.v_verbrauchnew.Teil_Nummer, dbo.Teile.Teil_Benennung, dbo.Lieferanten_Teile.Lieferanten_Nummer,
dbo.Lieferanten.Lieferanten_Name_1, FLOOR(dbo.v_summengebedarf.Bedarf) AS Bedarf, dbo.v_Bestand_new.Expr1 AS Bestand,
dbo.v_Bestand_new.Expr1 - dbo.v_summengebedarf.Bedarf AS Diff, dbo.Teile.Warengruppe
FROM dbo.Lieferanten INNER JOIN
dbo.Lieferanten_Teile ON dbo.Lieferanten.Lieferanten_Nummer = dbo.Lieferanten_Teile.Lieferanten_Nummer RIGHT OUTER JOIN
dbo.v_verbrauchnew ON dbo.Lieferanten_Teile.Teil_Nummer = dbo.v_verbrauchnew.Teil_Nummer LEFT OUTER JOIN
dbo.Teile ON dbo.v_verbrauchnew.Teil_Nummer = dbo.Teile.Teil_Nummer LEFT OUTER JOIN
dbo.v_Bestand_new ON dbo.v_verbrauchnew.Teil_Nummer = dbo.v_Bestand_new.Teil_Nummer LEFT OUTER JOIN
dbo.v_summengebedarf ON dbo.v_verbrauchnew.Teil_Nummer = dbo.v_summengebedarf.Teil_Nummer
WHERE (dbo.Lieferanten_Teile.Liefer_Quote = 100)
GROUP BY dbo.v_verbrauchnew.Disponent, dbo.v_verbrauchnew.Teil_Nummer, dbo.Teile.Teil_Benennung, dbo.Lieferanten_Teile.Lieferanten_Nummer,
dbo.Lieferanten.Lieferanten_Name_1, FLOOR(dbo.v_summengebedarf.Bedarf), dbo.v_Bestand_new.Expr1,
dbo.v_Bestand_new.Expr1 - dbo.v_summengebedarf.Bedarf, dbo.Teile.Warengruppe
HAVING (dbo.v_verbrauchnew.Disponent <> '')


#2
ISNULL ( dbo.v_Bestand_new.Expr1 , 0 ) AS Bestand

#3

thank you

now this is my query but for diff in result below i have null instead of -16
do you know why?

SELECT dbo.v_verbrauchnew.Disponent, dbo.v_verbrauchnew.Teil_Nummer, dbo.Teile.Teil_Benennung, dbo.Lieferanten_Teile.Lieferanten_Nummer,
dbo.Lieferanten.Lieferanten_Name_1, FLOOR(dbo.v_summengebedarf.Bedarf) AS Bedarf, ISNULL(dbo.v_Bestand_new.Expr1, 0) AS Bestand,
dbo.v_Bestand_new.Expr1 - dbo.v_summengebedarf.Bedarf AS Diff, dbo.Teile.Warengruppe
FROM dbo.Lieferanten INNER JOIN
dbo.Lieferanten_Teile ON dbo.Lieferanten.Lieferanten_Nummer = dbo.Lieferanten_Teile.Lieferanten_Nummer RIGHT OUTER JOIN
dbo.v_verbrauchnew ON dbo.Lieferanten_Teile.Teil_Nummer = dbo.v_verbrauchnew.Teil_Nummer LEFT OUTER JOIN
dbo.Teile ON dbo.v_verbrauchnew.Teil_Nummer = dbo.Teile.Teil_Nummer LEFT OUTER JOIN
dbo.v_Bestand_new ON dbo.v_verbrauchnew.Teil_Nummer = dbo.v_Bestand_new.Teil_Nummer LEFT OUTER JOIN
dbo.v_summengebedarf ON dbo.v_verbrauchnew.Teil_Nummer = dbo.v_summengebedarf.Teil_Nummer
WHERE (dbo.Lieferanten_Teile.Liefer_Quote = 100)
GROUP BY dbo.v_verbrauchnew.Disponent, dbo.v_verbrauchnew.Teil_Nummer, dbo.Teile.Teil_Benennung, dbo.Lieferanten_Teile.Lieferanten_Nummer,
dbo.Lieferanten.Lieferanten_Name_1, FLOOR(dbo.v_summengebedarf.Bedarf), dbo.v_Bestand_new.Expr1,
dbo.v_Bestand_new.Expr1 - dbo.v_summengebedarf.Bedarf, dbo.Teile.Warengruppe
HAVING (dbo.v_verbrauchnew.Disponent <> '')

result:
Disponent Teil_Nummer Teil_Benennung Lieferanten_Nummer Lieferanten_Name_1 Bedarf Bestand Diff Warengruppe
89 478869370 SEAL,HOOD TO RADIATOR SIDE BKV81 YARIS KABIN SANAYI VE TICARET A S 16 0 NULL W119


#4

you need to use ISNULL on that calculation also

ISNULL ( dbo.v_Bestand_new.Expr1 , 0) - dbo.v_summengebedarf.Bedarf AS Diff

#5

thank you

but when i change it i have this error:
dbo.v_summengebedarf.bedarf is not valid because it is not used in aggregations
do you know what the problem is?
thank you


#6

oh.. i didn't go through your full query. you need to change the GROUP BY part to use the same expression

GROUP BY dbo.v_verbrauchnew.Disponent, dbo.v_verbrauchnew.Teil_Nummer, 
         dbo.Teile.Teil_Benennung, dbo.Lieferanten_Teile.Lieferanten_Nummer, 
         dbo.Lieferanten.Lieferanten_Name_1, FLOOR(dbo.v_summengebedarf.Bedarf), 
         ISNULL(dbo.v_Bestand_new.Expr1, 0) ,    
        ISNULL(dbo.v_Bestand_new.Expr1 , 0) - dbo.v_summengebedarf.Bedarf, dbo.Teile.Warengruppe

#7

To remove the null with a specified replacement value, you can use any of the following command.

1. ISNULL() function
2. CASE statement
3. COALESCE() function

SELECT ISNULL(myColumn, 0 ) FROM myTable


#8

Well worth considering using COALESCE instead of ISNULL. The differences are subtle, but can be significant.

ISNULL will use the data type of the first parameter, which can cause subtle changes to the use of the second parameter (when the first is NULL), and that can create a bug that is hard to find ...

COALESCE allows unlimited parameters - the first, non-NULL, is returned - and it determines the precedence of the data types of the parameters, rather than imposing the data type of the first one.

ISNULL is easier to type ... but a silly name as it is not descriptive of what it does. COALESCE is a monster to type ...

ISNULL is optimised out if the first parameter is defined as NOT NULL, which is definitely a benefit if ISNULL is used like confetti in code! but assuming this is used on columns which CAN be NULL :slight_smile: then there is no difference on that point alone.

People also mention that COALESCE is standards-compliant. I can't see that mattering to most people - I stopped supporting Oracle and Sybase nearly 20 years ago!