How to insert null value to a date field in insert query .

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

I am not able to insert null value to date filed from a select clause.
Like the following:

insert into emp_table (
select emp_name,101,emp_joining,null
from emp_table
where emp_id = 201
);

here i am giving 101 as emp_id and null from emp_retireddate.
Then i am getting the following error:

SQLCODE = -206, ERROR: NULL IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER

Can any one help me in correcting the query?

murmohk1 · Posted: Fri Jan 18, 2008 12:19 pm

Kiranmayee,

ousep143 · New User Joined: 06 Oct 2007 Posts: 32 Location: India

Hi Kiran,
Your select statement is wrong,Using indicator variable,we can select/insert null values.

Regards

abhishekmdwivedi · New User Joined: 22 Aug 2006 Posts: 95 Location: india

Hi

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

I tried this also. I am getting invalid date format error.

abhishekmdwivedi · New User Joined: 22 Aug 2006 Posts: 95 Location: india

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

Even without giving spaces between single quotes i am getting the below error.

SQLCODE = -180, ERROR: THE DATE, TIME, OR TIMESTAMP VALUE *N IS
INVALID

I am suprised how come for me it is not executing.

abhishekmdwivedi · New User Joined: 22 Aug 2006 Posts: 95 Location: india

My apologies I took date as CHAR hence the problem occurred. Well in that case use Null indicator and try inserting values through Cobol code.

ousep143 · New User Joined: 06 Oct 2007 Posts: 32 Location: India

Hi Kiranmayee,

Move -1 to Null indicator variable & try it.

Regards

bhushan.shete · New User Joined: 14 Nov 2006 Posts: 17

Hi Kiranmayee

insert into emp_table (
select emp_name,101,emp_joining
from emp_table
where emp_id = 201
);

This will by default insert null for your retireddate field.

Plz let me know if i am wrong.

Thanks
Bhushan

stodolas · Posted: Mon Jan 21, 2008 6:24 pm

You don't have a NI variable in a query, only in COBOL. Try this

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

Hi Bushan,

I tried the query which you said.

If the retired date filed is not given, then the value of the next filed is taken as the date value and the following error is displayed if the retireddate is not the last field in the table.

SQLCODE = -408, ERROR: THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET.

If the retired date is last filed and if didn't give any value for it like

insert into emp_table (
select emp_name,101,emp_joining
from emp_table
where emp_id = 201
);

The following error is displayed

SQLCODE = -117, ERROR: THE NUMBER OF ASSIGNED VALUES IS NOT THE SAME AS THE NUMBER OF SPECIFIED OR IMPLIED COLUMNS.

Thanks
Kiranmayee

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

Hi Stodolas,

For the query which you said using NULLIF(1,0), i am getting the following error:

SQLCODE = -408, ERROR: THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET.

The other method is also not working.

Is there any other way through which i can get out of this prob?

Thanks,
Kiranmayee

bhushan.shete · New User Joined: 14 Nov 2006 Posts: 17

Hi Kiranmayee

u r right, it is giving same error as u said , i worked on it, here is solution.

insert into emp_table (
select emp_name,101,emp_joining, NULLIF('','')
from emp_table
where emp_id = 201
);

This will work, if not working try
NULLIF('2008-01-22','2008-01-22') instead of NULLIF('','')

Thanks
Bhushan

bhushan.shete · New User Joined: 14 Nov 2006 Posts: 17

Hi

for Date field, we must use.
NULLIF('2008-01-22','2008-01-22')

you can any date, but both dates should be same.

Regards
Bhushan.

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

Hi Bhushan,

It successfully got inserted this time. Thanks a lot.

Regards,
Kiranmayee