Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How to insert null value to a date field in insert query .
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: 1439
Location: Bangalore,India

PostPosted: Fri Jan 18, 2008 12:19 pm    Post subject:
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    Post subject: Re: How to insert null value to a date field in insert query
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    Post subject: Reply to: How to insert null value to a date field in insert
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: very simple
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: 632
Location: Wisconsin

PostPosted: Mon Jan 21, 2008 6:24 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject: TESTED
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    Post subject: CLOSE THE TOPIC NOW.
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 17 Tue Dec 06, 2016 8:04 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us