Discussion:
How do I insert null in table?
(too old to reply)
damezumari
2010-05-30 15:24:45 UTC
Permalink
I use vfp9 sp2.

When I try to say:

insert into table (fields) ;
select ..., ;
null
from ..

I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.

How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.

Here is the entire code:

INSERT INTO graderep ;
(teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
SELECT teachers02.id, ;
teachers02.firstname, ;
teachers02.lastname, ;
classes02.id, ;
subjects.subject, ;
sets.fldset, ;
years.year, ;
former.form, ;
exams.exam, ;
students.id, ;
studentsclasses.id, ;
students.statusid, ;
students.nname, ;
students.firstname, ;
students.lastname, ;
null, ;
null, ;
null, ;
nlevel, ;
nnumber, ;
ntype, ;
ctitle, ;
dt,;
.f. ;
from teachers02 ;
inner join classes02 on classes02.teacherid = teachers02.id ;
INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
INNER JOIN students on students.id = studentsclasses.studid ;
inner join subjects on subjects.id = classes02.subjectid ;
inner join years on years.id = classes02.yearid ;
left join sets on sets.id = classes02.setid ;
left join former on former.id = classes02.formid ;
left join exams on exams.id = classes02.examid ;
where ;
students.statusid = 1 ;
AND inlist(years.year,"7","8","9","10","11")

UPDATE graderep ;
SET attainment = null, effort = null, hw = null ;
WHERE repdate == dt

Regards,

Jan
Rush Strong
2010-05-30 15:37:32 UTC
Permalink
Try:

.NULL.

instead of just:

NULL

- Rush
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
(teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
SELECT teachers02.id, ;
teachers02.firstname, ;
teachers02.lastname, ;
classes02.id, ;
subjects.subject, ;
sets.fldset, ;
years.year, ;
former.form, ;
exams.exam, ;
students.id, ;
studentsclasses.id, ;
students.statusid, ;
students.nname, ;
students.firstname, ;
students.lastname, ;
null, ;
null, ;
null, ;
nlevel, ;
nnumber, ;
ntype, ;
ctitle, ;
dt,;
.f. ;
from teachers02 ;
inner join classes02 on classes02.teacherid = teachers02.id ;
INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
INNER JOIN students on students.id = studentsclasses.studid ;
inner join subjects on subjects.id = classes02.subjectid ;
inner join years on years.id = classes02.yearid ;
left join sets on sets.id = classes02.setid ;
left join former on former.id = classes02.formid ;
left join exams on exams.id = classes02.examid ;
where ;
students.statusid = 1 ;
AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
SET attainment = null, effort = null, hw = null ;
WHERE repdate == dt
Regards,
Jan
damezumari
2010-05-30 15:54:27 UTC
Permalink
        .NULL.
        NULL
  - Rush
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
  (teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
   studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
  SELECT  teachers02.id, ;
  teachers02.firstname, ;
  teachers02.lastname, ;
  classes02.id, ;
  subjects.subject, ;
  sets.fldset, ;
  years.year, ;
  former.form, ;
  exams.exam, ;
  students.id, ;
  studentsclasses.id, ;
  students.statusid, ;
  students.nname, ;
  students.firstname, ;
  students.lastname, ;
  null, ;
  null, ;
  null, ;
  nlevel, ;
  nnumber, ;
  ntype, ;
  ctitle, ;
  dt,;
  .f. ;
from teachers02 ;
  inner join classes02 on classes02.teacherid = teachers02.id ;
  INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
  INNER JOIN students on students.id = studentsclasses.studid ;
  inner join subjects on subjects.id = classes02.subjectid ;
  inner join years on years.id = classes02.yearid ;
  left join sets on sets.id = classes02.setid ;
  left join former on former.id = classes02.formid ;
  left join exams on exams.id = classes02.examid ;
where ;
 students.statusid = 1 ;
 AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
  SET attainment = null, effort = null, hw = null ;
  WHERE repdate == dt
Regards,
Jan
Same error message: SQL: Cannot determine datatype of SQL Column:
EXP_16

Regards,

Jan
Gregory Adam
2010-05-30 16:31:04 UTC
Permalink
You'll have to cast null to your data type - see example with c(20)

insert into table (fields) ;
select ..., ;
cast( null as c(20) ) ;
from ..
--
Gregory
_
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
(teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
SELECT teachers02.id, ;
teachers02.firstname, ;
teachers02.lastname, ;
classes02.id, ;
subjects.subject, ;
sets.fldset, ;
years.year, ;
former.form, ;
exams.exam, ;
students.id, ;
studentsclasses.id, ;
students.statusid, ;
students.nname, ;
students.firstname, ;
students.lastname, ;
null, ;
null, ;
null, ;
nlevel, ;
nnumber, ;
ntype, ;
ctitle, ;
dt,;
.f. ;
from teachers02 ;
inner join classes02 on classes02.teacherid = teachers02.id ;
INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
INNER JOIN students on students.id = studentsclasses.studid ;
inner join subjects on subjects.id = classes02.subjectid ;
inner join years on years.id = classes02.yearid ;
left join sets on sets.id = classes02.setid ;
left join former on former.id = classes02.formid ;
left join exams on exams.id = classes02.examid ;
where ;
students.statusid = 1 ;
AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
SET attainment = null, effort = null, hw = null ;
WHERE repdate == dt
Regards,
Jan
damezumari
2010-05-30 17:13:47 UTC
Permalink
Post by Gregory Adam
You'll have to cast null to your data type - see example with c(20)
insert into table (fields) ;
 select ..., ;
cast( null as c(20) ) ;
from ..
--
Gregory
_
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
 (teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
  studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
 SELECT  teachers02.id, ;
 teachers02.firstname, ;
 teachers02.lastname, ;
 classes02.id, ;
 subjects.subject, ;
 sets.fldset, ;
 years.year, ;
 former.form, ;
 exams.exam, ;
 students.id, ;
 studentsclasses.id, ;
 students.statusid, ;
 students.nname, ;
 students.firstname, ;
 students.lastname, ;
 null, ;
 null, ;
 null, ;
 nlevel, ;
 nnumber, ;
 ntype, ;
 ctitle, ;
 dt,;
 .f. ;
from teachers02 ;
 inner join classes02 on classes02.teacherid = teachers02.id ;
 INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
 INNER JOIN students on students.id = studentsclasses.studid ;
 inner join subjects on subjects.id = classes02.subjectid ;
 inner join years on years.id = classes02.yearid ;
 left join sets on sets.id = classes02.setid ;
 left join former on former.id = classes02.formid ;
 left join exams on exams.id = classes02.examid ;
where ;
students.statusid = 1 ;
AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
 SET attainment = null, effort = null, hw = null ;
 WHERE repdate == dt
Regards,
Jan
Thank you Gregory! That did it.

- Jan
Gregory Adam
2010-05-30 17:29:22 UTC
Permalink
This may well be "the last post" here
--
Gregory
_


">
Post by damezumari
Thank you Gregory! That did it.
- Jan
Rush Strong
2010-05-30 17:53:54 UTC
Permalink
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.

- Rush
Dan Freeman
2010-05-30 18:03:02 UTC
Permalink
Post by Rush Strong
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.
- Rush
But if you make the last post and nobody sees it does it matter? ;-)

Dan
Stefan Wuebbe
2010-05-30 20:10:12 UTC
Permalink
Post by Dan Freeman
Post by Rush Strong
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.
- Rush
But if you make the last post and nobody sees it does it matter? ;-)
Dan
Probably not <s>


-Stefan
Villi Bernaroli
2010-06-01 07:29:51 UTC
Permalink
Post by Stefan Wuebbe
Post by Dan Freeman
Post by Rush Strong
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.
But if you make the last post and nobody sees it does it matter? ;-)
Probably not <s>
They did choose the right moment, almost summer: it feels like the last day
at school...
--
Villi Bernaroli, Italy
VFP, SOAP, MS SQLserver, MySQL, Oracle
Rush Strong
2010-06-01 17:09:43 UTC
Permalink
Post by Villi Bernaroli
Post by Stefan Wuebbe
Post by Dan Freeman
Post by Rush Strong
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.
But if you make the last post and nobody sees it does it matter? ;-)
Probably not <s>
They did choose the right moment, almost summer: it feels like the last day
at school...
Lasties?

- Rush
Stefan Wuebbe
2010-06-01 17:23:08 UTC
Permalink
Post by Rush Strong
Post by Villi Bernaroli
Post by Stefan Wuebbe
Post by Dan Freeman
Post by Rush Strong
Post by Gregory Adam
This may well be "the last post" here
Or the penultimate one.
But if you make the last post and nobody sees it does it matter? ;-)
Probably not <s>
They did choose the right moment, almost summer: it feels like the
last day at school...
Lasties?
What is a lasty, is that something nasty?


Regards
-Stefan

Rush Strong
2010-05-30 16:53:01 UTC
Permalink
OK, let's make life easier, not harder. Drop the field names (of
the fields to be null) from the INSERT command, and leave the "null"
values out as well. Also, SET NULL ON before running the command:

cSetNull = SET("NULL")
SET NULL OFF
INSERT INTO [...]
SET NULL &cSetNull

The inserted record will be populated by NULLs by default; only the
fields that you specify in the INSERT will be non-nulled.

- Rush
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
(teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
SELECT teachers02.id, ;
teachers02.firstname, ;
teachers02.lastname, ;
classes02.id, ;
subjects.subject, ;
sets.fldset, ;
years.year, ;
former.form, ;
exams.exam, ;
students.id, ;
studentsclasses.id, ;
students.statusid, ;
students.nname, ;
students.firstname, ;
students.lastname, ;
null, ;
null, ;
null, ;
nlevel, ;
nnumber, ;
ntype, ;
ctitle, ;
dt,;
.f. ;
from teachers02 ;
inner join classes02 on classes02.teacherid = teachers02.id ;
INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
INNER JOIN students on students.id = studentsclasses.studid ;
inner join subjects on subjects.id = classes02.subjectid ;
inner join years on years.id = classes02.yearid ;
left join sets on sets.id = classes02.setid ;
left join former on former.id = classes02.formid ;
left join exams on exams.id = classes02.examid ;
where ;
students.statusid = 1 ;
AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
SET attainment = null, effort = null, hw = null ;
WHERE repdate == dt
Regards,
Jan
damezumari
2010-05-30 17:22:48 UTC
Permalink
OK, let's make life easier, not harder.  Drop the field names (of
the fields to be null) from the INSERT command, and leave the "null"
cSetNull = SET("NULL")
SET NULL OFF
INSERT INTO [...]
SET NULL &cSetNull
The inserted record will be populated by NULLs by default; only the
fields that you specify in the INSERT will be non-nulled.
  - Rush
Post by damezumari
I use vfp9 sp2.
insert into table (fields) ;
select ..., ;
null
from ..
I get the error message "Cannot determine data type" and the guilty
part seems to be the null part.
How do I insert a null value above? Now I insert 0 and later do a
update statement inserting the null that works fine.
INSERT INTO graderep ;
  (teacherid, teacherfirstname, teacherlastname, classid, subject,
fldset, year, form, exam, ;
   studentid, studentsclasses, statusid, nname, firstname, lastname,
attainment, effort, hw, level, number, type, title, repdate, status) ;
  SELECT  teachers02.id, ;
  teachers02.firstname, ;
  teachers02.lastname, ;
  classes02.id, ;
  subjects.subject, ;
  sets.fldset, ;
  years.year, ;
  former.form, ;
  exams.exam, ;
  students.id, ;
  studentsclasses.id, ;
  students.statusid, ;
  students.nname, ;
  students.firstname, ;
  students.lastname, ;
  null, ;
  null, ;
  null, ;
  nlevel, ;
  nnumber, ;
  ntype, ;
  ctitle, ;
  dt,;
  .f. ;
from teachers02 ;
  inner join classes02 on classes02.teacherid = teachers02.id ;
  INNER JOIN studentsclasses ON studentsclasses.classid =
classes02.id ;
  INNER JOIN students on students.id = studentsclasses.studid ;
  inner join subjects on subjects.id = classes02.subjectid ;
  inner join years on years.id = classes02.yearid ;
  left join sets on sets.id = classes02.setid ;
  left join former on former.id = classes02.formid ;
  left join exams on exams.id = classes02.examid ;
where ;
 students.statusid = 1 ;
 AND inlist(years.year,"7","8","9","10","11")
UPDATE graderep ;
  SET attainment = null, effort = null, hw = null ;
  WHERE repdate == dt
Regards,
Jan
:) Good idea! Thank you! It worked as a charm.

- Jan
Continue reading on narkive:
Loading...