CASTLESCE

#1

Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?

--
Regards,
Troels Arvin <troels (AT) arvin (DOT) dk>
http://troels.arvin.dk/

#2

Troels Arvin wrote:

Quote:

Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..

You won't get a prize for performance though....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#3

On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:

Quote:

What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....

How bad is it - will it be more rational to dump the data, massage it
procedurally (e.g. with perl), and then import it again?

--
Regards,
Troels Arvin <troels (AT) arvin (DOT) dk>
http://troels.arvin.dk/

#4

Troels Arvin wrote:

Quote:

On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote:
What's my best option? Try using a procedure, or in some other way?
You can use a SQL UDF that calls a procedure..
You won't get a prize for performance though....

How bad is it - will it be more rational to dump the data, massage it
procedurally (e.g. with perl), and then import it again?

*lol* No it will be better than that...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

#5

Quote:

What's my best option? Try using a procedure, or in some other way?

Hello.
You should use an external UDF.
For example, in java it whould be fairly simple code.

Sincerely,
Mark B.

#6

On Fri, 09 Feb 2007 06:39:22 -0800, 4.spam wrote:

Quote:

What's my best option? Try using a procedure, or in some other way?

You should use an external UDF.
For example, in java it whould be fairly simple code.

Yes. But I'm afraid of external UDFs:
- are external UDFs backed up when the database is
backed up? (using TSM)
- what will happen if/when we upgrade from DB2 v. 8 to v. 9?

--
Regards,
Troels Arvin <troels (AT) arvin (DOT) dk>
http://troels.arvin.dk/

#7

Troels Arvin wrote:

Quote:

Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

If there are just a few of such values, then maybe NULLIF is a way to go?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

#8

Quote:

Yes. But I'm afraid of external UDFs:
- are external UDFs backed up when the database is
backed up? (using TSM)

AFAIK, no.

Quote:

- what will happen if/when we upgrade from DB2 v. 8 to v. 9?

I whould recompile all external routines (C, JAVA) except those with
PARAMETER STYLE JAVA.

#9

On Feb 9, 12:06 pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote:

Quote:

Hello,

I need to cleanse some data. Some of the data consists of CHARs of the
form '2006-12-24' which are to be converted to DATE values.

Some of the values are known to be invalid (such as '0000-00-00' or
'2006-02-45'), and those values need to be converted to NULLs.

So I'm looking for a "CASTLESCE" function: Cast to a type; if the cast
fails, return NULL.

My first thought was to create a UDF where the UDF catches cast-errors via
a declared continue-handler. But using a continue-handler seems to be
possible only in procedures(?).

What's my best option? Try using a procedure, or in some other way?

Hi Troels, you can use a calendar table in the process. Don't know if
its the best option but it may serve your needs:

[lelle@53dbd181 lelle]$ db2 "create table calendar (d date not null
primary key)"
[lelle@53dbd181 lelle]$ db2 "insert into calendar with t (d) as
(values current_date - 1 year union all select d + 1 day from t where
d < current_date + 1 year) select * from t"

start date and stop date should be chosen so that it spans your date
interval

Assuming your source table like:

[lelle@53dbd181 lelle]$ db2 "create table X (mydate char(10) not
null)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$
[lelle@53dbd181 lelle]$ db2 "insert into X values ('2006-12-24'),
('0000-00-00') ,('2006-02-45') "

and target table

[lelle@53dbd181 lelle]$ db2 "create table Y (newdate date not null)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$ db2 "insert into Y select * from X where
mydate in (select char(d) from calendar)"
DB20000I The SQL command completed successfully.
[lelle@53dbd181 lelle]$ db2 "select * from Y"

NEWDATE
----------
2006-12-24

1 record(s) selected.

You probably have a different cleaning process :-), but the idea
should work for other scenarios as well.

HTH
/Lennart