View previous topic :: View next topic
|
Author |
Message |
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? |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
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 |
|
|
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
|
|
Back to top |
|
|
abhishekmdwivedi
New User
Joined: 22 Aug 2006 Posts: 95 Location: india
|
|
|
|
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 |
|
|
Kiranmayee
New User
Joined: 09 Jan 2008 Posts: 25 Location: India
|
|
|
|
I tried this also. I am getting invalid date format error. |
|
Back to top |
|
|
abhishekmdwivedi
New User
Joined: 22 Aug 2006 Posts: 95 Location: india
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
ousep143
New User
Joined: 06 Oct 2007 Posts: 32 Location: India
|
|
|
|
Hi Kiranmayee,
Move -1 to Null indicator variable & try it.
Regards
|
|
Back to top |
|
|
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 |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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 |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
Kiranmayee
New User
Joined: 09 Jan 2008 Posts: 25 Location: India
|
|
|
|
Hi Bhushan,
It successfully got inserted this time. Thanks a lot.
Regards,
Kiranmayee |
|
Back to top |
|
|
|