473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

set integrity and testing DATE format

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
5 4158

"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
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Magic1812 | last post by:
Magic Software invites you to join us this coming Tuesday (January 27th, 2004) at 12:00 EDT / 17:00 GMT for a FREE live Webinar: Title: Data Integrity Using eDeveloper Date: January 27, 2004...
0
by: Sharon Cowling | last post by:
Hi, my problem is this: I have a table called faps_key the unique identifier being key_code: taupo=# \d faps_key Table "faps_key" Column | Type | Modifiers...
4
by: maricel | last post by:
I have the following base table structure - DDL: CREATE TABLE "ADMINISTRATOR"."T1" ( "C1" INTEGER NOT NULL ) IN "TEST_TS" ; ALTER TABLE "ADMINISTRATOR"."T1" ADD PRIMARY KEY
1
by: HGT | last post by:
Hello all, I am currently on a project which the source data come into the databases is always dirty (not surprisingly); however, due to the design of the database, it is very difficult to...
80
by: Andrew R | last post by:
Hi I'm creating a series of forms, each with with around 15-20 text boxes. The text boxes will show data from tables, but are unbound to make them more flexible. I want the form to be used...
24
by: Ian Boyd | last post by:
i accidentally ran an UPDATE statement without the WHERE clause on a customer's live customers table. After saying "oh shit" many many times, i closed the ad-hoc query tool, hoping DB2 would see my...
16
by: Brian Tkatch | last post by:
Is there a way to check the order in which SET INTEGRITY needs to be applied? This would be for a script with a dynamic list of TABLEs. B.
0
by: WTH | last post by:
I ask because I've got a windows service I've written that manages failover and replication for our products (or even 3rd party applications) and it worked great right until I tested it (for ease...
16
by: Mike P | last post by:
How would I write some Javascript to test whether a date is in the past or not? I need to only accept either the current date or future dates. *** Sent via Developersdex...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.