IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

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


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Fri Jan 18, 2008 9:44 am
Reply with quote

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?
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 12:19 pm
Reply with quote

Kiranmayee,

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

Go thru the 'select' syntax.
Back to top
View user's profile Send private message
ousep143

New User


Joined: 06 Oct 2007
Posts: 32
Location: India

PostPosted: Fri Jan 18, 2008 3:15 pm
Reply with quote

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


Regards
icon_smile.gif
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Mon Jan 21, 2008 11:23 am
Reply with quote

Hi

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


Here's the correct query :
Code:

INSERT INTO EMP_TABLE                             
SELECT EMP_NAME,101,EMP_JOINING,'' FROM
EMP_TABLE where EMP_ID = 201;           
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Mon Jan 21, 2008 12:01 pm
Reply with quote

I tried this also. I am getting invalid date format error. icon_sad.gif
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Mon Jan 21, 2008 12:07 pm
Reply with quote

Quote:
I am getting invalid date format error.

emp_retireddate is of format Date/Char right ?

Make sure the
Code:
SELECT EMP_NAME,101,EMP_JOINING,'' FROM

'' are two single quotes without space.
As the same query is working here.
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Mon Jan 21, 2008 12:45 pm
Reply with quote

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.
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Mon Jan 21, 2008 12:56 pm
Reply with quote

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.
Back to top
View user's profile Send private message
ousep143

New User


Joined: 06 Oct 2007
Posts: 32
Location: India

PostPosted: Mon Jan 21, 2008 2:14 pm
Reply with quote

Hi Kiranmayee,

Move -1 to Null indicator variable & try it.


Regards
icon_smile.gif
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Mon Jan 21, 2008 6:22 pm
Reply with quote

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
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Mon Jan 21, 2008 6:24 pm
Reply with quote

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

Code:

INSERT INTO EMP_TABLE   
SELECT EMP_NAME,101,EMP_JOINING,NULLIF(1.0) FROM
EMP_TABLE where EMP_ID = 201;   


or does this work
Code:

INSERT INTO EMP_TABLE   
SELECT EMP_NAME,101,EMP_JOINING FROM
EMP_TABLE where EMP_ID = 201;   
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Tue Jan 22, 2008 11:48 am
Reply with quote

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
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Tue Jan 22, 2008 11:57 am
Reply with quote

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
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Tue Jan 22, 2008 1:19 pm
Reply with quote

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
Back to top
View user's profile Send private message
bhushan.shete

New User


Joined: 14 Nov 2006
Posts: 17

PostPosted: Tue Jan 22, 2008 4:02 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Kiranmayee

New User


Joined: 09 Jan 2008
Posts: 25
Location: India

PostPosted: Tue Jan 22, 2008 6:57 pm
Reply with quote

Hi Bhushan,

It successfully got inserted this time. Thanks a lot.

Regards,
Kiranmayee
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Need help with ADABAS query (COBOL-AD... All Other Mainframe Topics 0
No new posts Date format correction using dfsort DFSORT/ICETOOL 3
No new posts Sorting Date Field DFSORT/ICETOOL 4
No new posts CONVERT a given date from UTC TO BST CICS 0
No new posts Pull data using date difference betwe... DB2 6
Search our Forums:

Back to Top