Re: SQL 22007

[rob@xxxxxxxxx]

CREATE TABLE ROB/ERIC (MYDATE DATE NOT NULL WITH DEFAULT, MYCHAR
CHAR (1 ) NOT NULL WITH DEFAULT) 

INSERT INTO ROB/ERIC (MYDATE, MYCHAR) VALUES('0001-01-01', 'a')
It did allow this.  However,

STRDBG
select * from rob/eric
Did generate some messages
CPF5035-error code 18

INSERT INTO ROB/ERIC (MYDATE, MYCHAR) VALUES('2004-01-01', 'b')

select * from rob/eric
....+....1....+.
MYDATE    MYCHAR
++++++++    a 
01/01/04    b 
********  End of

Perhaps instead of using a default date of that, you need to either pick a 
different default date, or better yet, set up your applications to handle 
null fields.

drop table rob/eric 
Drop of ERIC in ROB complete. 
CREATE TABLE ROB/ERIC (MYDATE DATE , MYCHAR CHAR ( 1) NOT NULL WITH
DEFAULT) 
Table ERIC in ROB created but could not be journaled. 
INSERT INTO ROB/ERIC (MYCHAR) VALUES('a') 
1 rows inserted in ERIC in ROB. 
INSERT INTO ROB/ERIC (MYDATE, MYCHAR) VALUES('2004-01-01', 'b') 
1 rows inserted in ERIC in ROB. 
select * from rob/eric 
SELECT statement run complete.
....+....1....+.
MYDATE    MYCHAR
-           a 
01/01/04    b 
********  End of

or perhaps
select coalesce(mydate,'0001-01-01'), mychar from rob/eric


Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Eric Kempter" <EKempter@xxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/10/2004 01:42 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
SQL 22007






When attempting to read a file via an SQL select statement in RPGLE, I 
receive SQL state error 22007 on a record that contains a non NULL date 
field valued with 0001-01-01.   To my mind, this is a valid date.  When I 
value this field with 2003-01-01, I do not receive an error.  Any thoughts 
on what the problem could be?  We are using V5R2.

Thanks in advance for any assistance. 



_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.