By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,557 Members | 1,406 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,557 IT Pros & Developers. It's quick & easy.

set integrity and testing DATE format

P: n/a
Dear all,

I have a table that contains a DATE in char(10) format. This table is
LOADed.

I put a check constraint on the column, some kind of CHECK (DATE(F) <=
DATE('9999-12-31'))
and I would like to move badly formatted field to the exception table.

The problem is I will always get SQL0180N and the record is not moved into
the exception table when I issue a SET INTEGRITY command.

Is there any trick to do the job - I am interested in moving the field to
the exception table and not let the LOAD reject the fields in a dump file
(that would be the case with a DATE field instead of a CHAR(10)).

Thanks for your help,

Jean-Marc
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:ce**********@news-reader3.wanadoo.fr...
Dear all,

I have a table that contains a DATE in char(10) format. This table is
LOADed.

I put a check constraint on the column, some kind of CHECK (DATE(F) <=
DATE('9999-12-31'))
and I would like to move badly formatted field to the exception table.

The problem is I will always get SQL0180N and the record is not moved into
the exception table when I issue a SET INTEGRITY command.

Is there any trick to do the job - I am interested in moving the field to
the exception table and not let the LOAD reject the fields in a dump file
(that would be the case with a DATE field instead of a CHAR(10)).

What platform are you on? What DB2 version are you using? It's not clear
from you're question if you are using the OS/390 LOAD utility or the
Windows/Linux/Unix Load command....

Rhino
Nov 12 '05 #2

P: n/a
"Rhino" <rh****@NOSPAM.sympatico.ca> a écrit dans le message de
news:_Q********************@news20.bellglobal.com. ..

"Jean-Marc Blaise" <no****@nowhere.com> wrote in message
news:ce**********@news-reader3.wanadoo.fr...
Dear all,

I have a table that contains a DATE in char(10) format. This table is
LOADed.

I put a check constraint on the column, some kind of CHECK (DATE(F) <=
DATE('9999-12-31'))
and I would like to move badly formatted field to the exception table.

The problem is I will always get SQL0180N and the record is not moved into the exception table when I issue a SET INTEGRITY command.

Is there any trick to do the job - I am interested in moving the field to the exception table and not let the LOAD reject the fields in a dump file (that would be the case with a DATE field instead of a CHAR(10)).

What platform are you on? What DB2 version are you using? It's not clear
from you're question if you are using the OS/390 LOAD utility or the
Windows/Linux/Unix Load command....

Rhino

Hi Rhino,

Sorry, this is a Windows/Linux/Unix question. I'm on Aix 5.1, and on DB2 UDB
version 8.1.

JM
Nov 12 '05 #3

P: n/a
Jean-Marc,

You need to distinguish between a constraint violation (which means that
the constraint resolves to FALSE) and an SQL error (because of a
malformed date).
What you could do is write a function (perhaps SQL bodied) which
verifies that the column value is well formed. Then verify the range.

CASE WHEN IS_A_DATE(c1) = 1
THEN CASE WHEN DATE(c1) < DATE('1999-01-01')
THEN 1 END
END = 1

Cheers
Serge
Nov 12 '05 #4

P: n/a
"Serge Rielau" <sr*****@ca.eye-bee-em.com> a écrit dans le message de
news:HM**************@news04.bloor.is.net.cable.ro gers.com...
Jean-Marc,

You need to distinguish between a constraint violation (which means that
the constraint resolves to FALSE) and an SQL error (because of a
malformed date).
What you could do is write a function (perhaps SQL bodied) which
verifies that the column value is well formed. Then verify the range.

CASE WHEN IS_A_DATE(c1) = 1
THEN CASE WHEN DATE(c1) < DATE('1999-01-01')
THEN 1 END
END = 1

Cheers
Serge


Hi Serge,

I am looking for such function :-), basically that would do the same stuff
as the LOAD does internally to reject the bad row ?

Regards,

JM
Nov 12 '05 #5

P: n/a
The easiest way would be to write a C-UDF which CONTAINS SQL.
That UDF could simply catch the SQL error and return NULL instead.
Call it "friendly arithmetic for dates" :-)

Cheers
Serge
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.