How to change null field to 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to change null field to 0

Author

Topic

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-07-14 : 00:44:28

Hi
In the below query some times IPRM.purchaesReturnId and IPRM.purchaseReturnDate may be null in such cases how can I assign value o to these fields instred of null

SELECT
IPIM.purchaseInvoiceId,
IPM.purchaseId,
IPM.purchaseDate,
IPRM.purchaseReturnId,
IPRM.purchaseReturnDate
FROM
InventoryPurchaseMaster IPM
INNER JOIN InventorySupplierMaster ISM
ON IPM.supplierId= ISM.supplierId
INNER JOIN InventoryPurchaseInvoicePurchaseVoucherIds IPIPVID
ON IPM.purchaseId = IPIPVID.purchaseVoucherId
INNER JOIN InventoryPurchaseInvoiceMaster IPIM
ON IPIPVID.purchaseInvoiceId = IPIM.purchaseInvoiceId
LEFT JOIN InventoryPurchaseInvoicePurchaseReturnIds IPIPRID
ON IPIM.purchaseInvoiceId = IPIPRID.purchaseInvoiceId
LEFT JOIN InventoryPurchaseReturnMaster IPRM
ON IPIPRID.purchaseReturnId = IPRM.purchaseReturnId
WHERE IPIM.purchaseInvoiceId = '10'

Thanks and Regards
Anu Palavila

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 00:46:18

isnull( column_name, 0)

KH
[spoiler]Time is always against us[/spoiler]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-07-14 : 01:09:09

Or, <code> CASE WHEN <column> IS NULL THEN 0 ELSE <column> AS NewColumn.. </code>

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 01:10:52

quote:
Originally posted by dinakar

Or, <code> CASE WHEN <column> IS NULL THEN 0 ELSE <column> END AS NewColumn.. </code>

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

if you're using CASE you need an END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 01:13:24

you can also use

coalesce(column_name, 0)

KH
[spoiler]Time is always against us[/spoiler]

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-07-14 : 01:15:13

Thanks Khtan for your valuable answer but the date field is showing as 01/01/1900 is it possible to make as 0 or 0/0/0000

Thanks and Regards
Anu Palavila

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 01:31:10

quote:
Originally posted by anupalavila

Thanks Khtan for your valuable answer but the date field is showing as 01/01/1900 is it possible to make as 0 or 0/0/0000

Thanks and Regards
Anu Palavila

0/0/0000 is not a valid datetime. Do the formatting in your front end application or convert the datatime to string (not recommended).

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-14 : 01:33:23

quote:
Originally posted by anupalavila

Thanks Khtan for your valuable answer but the date field is showing as 01/01/1900 is it possible to make as 0 or 0/0/0000

Thanks and Regards
Anu Palavila

why do you want to make it 0?

anupalavila
Yak Posting Veteran

56 Posts

Posted - 2008-07-14 : 01:54:36

because in my report viewer control in C#.net application I am using this stored procedure to populate the report and if there is no return for an Invoice I want to display all field ie, retunId,returndate... as 0

Thanks and Regards
Anu Palavila

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 02:19:28

It does not make sense to display date as 0. Why not leave it as NULL ? for numeric you can use isnull(col, 0) or string use isnull(col, '')

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources