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

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, the
n
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference
(and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, t
hen
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that need
s
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/d...fintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:

> In 2005, you can define SET DEFAULT and SET NULL for a foreign key referen
ce (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for exampl
e).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>
>

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, then
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, then
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that needs
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:
> In 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
> >I have a couple of tables in my database (SQL server 2000)
> > 1. Student -- Columns are as
> > "StudentID" -- INT
> > "StudentName" -- NVARCHAR
> > "DepartmentID" -- INT
> > 2. Department -- Columns are as
> > "DepartmentID" -- INT
> > "DepartmentName" -- NVARCHAR
> >
> > So in the table "Student", the field "DepartmentID" is a foreign key. Now
> > what I need to do is that when I delete a row in the "Department" table, then
> > the rows in "Student" table where the to be deleted "Department ID" occurs
> > must *not* get deleted. Instead NULL should be inserted there.
> > Can this be achieved through database schema? Or is it something that needs
> > to be done through triggers?
> >
> > A.
> >
> > --
> > Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
>
>

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

Inserting NULL values on Date Fields trhough DAL

I am using a DAL and i want to insert a new row where one of the columns is DATE and itcanbe 'NULL'.

I am assigning SqlTypes.SqlDateTime.Null.

But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL??

how can i put an empty date in the database?

THANK YOU!!!

check ifthishelps.|||

That does not work. Besides, in your example you are NOT using DAL. You are inserting directly to the DB, through a SQL statement.

Thannks,

JeffKish

|||

The code in the link below uses ADO.NET parameters. Hope this helps.

http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp

inserting null values into smalldatetime

hi
how can we insert null into a small datetime field (from client application VB)Insert into table values (null);|||Originally posted by r123456
Insert into table values (null); that only works if the field in question happens to be the only field in the table ;)

insert into thetable (foo, bar, datetimefld, qux, fap)
values ( 21, 'fred', null, 42, 'okay')

Inserting Null values

Hi,
I am trying to insert null values into sql server from my access from. I am using sql statement. But it says 'Syntex error in Insert statement'. When i remove null values it works fine? How can I insert null values into a table?
Any help will be highly appreciated.It would help if you post the statement...|||Use SQL Pass-Through Query:

INSERT INTO Table1 ( id, name ) values(13, null);|||If your table accepts null in the field (name) then you don't need to mention that in your isert statment. Just use:

INSERT INTO Table1 (id) values(13);

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Inserting Null Value

Is there a way, I can insert NULL value to "DT_Date" type Row Column using Script Component Transformation of Data flow?

Ie. I have a column named Ordered Date which is type DT_Date. Based on some condition within a script component task, I want to set the value to NULL, however, since the type is date it will not allow nulls.

Found the answer, it was fairly simple. Row.FieldName_ISNULL() property is a read/write property. This property can be used to set nulls.

|||I'm assuming you are also doing some other work in the Script, but I thought I would mention that you can also do this in the Derived Column transform with the NULL() function. In your case, NULL(DT_DATE).|||Let me just add that the <columnname>_IsNull property may be read/write, or may be read-only, depending on the corresponding Usage Type specified for the individual column.

-Doug
|||

I have a simple package that takes data from excel sources, runs a Data Transformation to get the decimal conversion etc into the same format as SQL ... but some of the columns are nullable - so my Data Transformation fails when it hits a null value in the source... (converting from float in the source to decimal in the destination.. though becuase of the empty values it sees the source as a string). Seems like it should be simple to allow it to pass nulls but I can't seem to figure it out (am new to SSIS). None of the fields in the transformation appear to be editable so that I can add the NULL() as mentioned above - if I got into the Advanced editor I can manually type the data type but NULL(DT_DECIMAL) gives me error "DataTypeConverter cannot convert from System.String."

Can anyone point me in the right direction? :)

|||

<<a Data Transformation to get the decimal conversion etc into the same format as SQL >>

I believe your problem is in the decimal conversion processing you mention. The expression - or code - should be written to handle the occurence of nulls without bombing...

|||

That is just it... I am not "writing" anything - its not a script component transformation task but rather just a "Data Conversion" object. If you edit the Data Conversion object it has input and then output... you can change the output alias, datatype, scale, codepage etc but there isn't any options regarding nulls that I can find.

|||

So the problem is that the source data looks like an empty string, and in that case you want to put NULL in the destination? You might try using a Derived Column instead of the Data Conversion. There, you can write an expression.

For example, if your string input column is named "InputCol", and you are converting to DT_DECIMAL with scale 10, your expression for the new column would look like:

LEN(InputCol) == 0 ? NULL(DT_DECIMAL, 10) : (DT_DECIMAL, 10)InputCol

Let me know if that helps.

Mark

Inserting NULL into Datetime field

I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.

The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting '' but that also throws an exception ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"), so I don't know how I can insert this value when the field is blank?

Can anyone shed some light please?

Thanks

Check to see if the last answer of this other post helps:http://forums.asp.net/t/996855.aspx

|||

You can also check if this post helps:http://dotnetjunkies.com/WebLog/dinakar/articles/74221.aspx

inserting NULL into Datetime and int

Hello,

I have an asp.net page with different textboxes that put text into an sql database.
When I try to leave the textboxes blank that correspond with datatypes of int and datetime
in sql server it gives an error : I need to fill in these textboxes.

How can I solve this problem so I can leave the boxes blanc? What is the best sollution for this? I know I can set the types to String but then the user could fill in what he wants :)
Could you give me some advice on this please? All ideas are welcome.
Thanks !if you strictly want to enforce the user to enter the proper data and not mess up the system, use customvalidators for the textboxes.

for date, you can prbly have three textboxes for month, yr and day and validate it to numeric and when you add the values to db, concatenate them with a "/" in between.

or if the user leaves it blank, check if the txtDate.text = "" then use system.dbnull to enter null into the db.

HTH.|||Hey thanks ndinakar :)

Ok I've looked in the .net classes for this dbnull. There's no good example of how to do that.
Do I use this dbnull on the page script or in the database (for example in a stored procedure?)
Could you give an example please?(I'm very noob)

Thanks again for replying !