how to ignore error on sql function

[jayvaughn]

lets say i'm trying to do the following to a column that is 10 char...

DAY (DATE(ELCTDATE))

simply wanting to extract the day from the date...

what if there is blanks or invalid date data in the ELCTDATE column. Is there a way to return a 0 if so?

Guess I'm looking something syntax'd similiar to the coalesce - return literal if null - but return 0 if failed.

Oh and how I'm going about this, i am not wanting to condition anything in the where clause.

Another way to describe what I'm needing and why is...

i'm doing the following...

insert into tgtfile (target columns)
values (source columns)

many of the source columns may be wrapped in functions, like above... DAY (DATE(ELCTDATE))

so I really want to error check on each column and ignore, OR default a return value such as 0 in this case for when it fails to insert into the target column, instead of allowing the entire insert statement to abort.

[Jim_IT]

Jay,

You'll need to create a user defined function to replace the built in function DATE which handles error checking.

There are lots of examples. http://www.code400.com/forum/forum/i...s-a-valid-date

Jim

[jayvaughn]

Jay,

You'll need to create a user defined function to replace the built in function DATE which handles error checking.

There are lots of examples. http://www.code400.com/forum/forum/i...s-a-valid-date

Jim

thanks - i was hoping to avoid that, but no biggy to go there...

i assume STRSQL is callling a udf? would be very sweet to do it inline on the function itself.


strsql

select month (date('0000000000'))
from sysibm.sysdummy1

....+....1....
MONTH
++++++++++++++
******** End of data ********

[jayvaughn]

wow - hang on - a case statement will suit me perfectly...

case when ELCDATE = ' '
then 0
else month (date(ELCDATE))
end

[TedHolt]

I have a function that validates a date value, Jay.

https://www.itjungle.com/2006/04/26/fhg042606-story01/

You could put it in a CASE expression in the row value expression. Something like this:

insert . . . values ( . . . case when isValidDate(ELCDATE) = '1' then ELCTDATE else '0' end . . .)

[jj_dahlheimer]

wow - hang on - a case statement will suit me perfectly...

case when ELCDATE = ' '
then 0
else month (date(ELCDATE))
end

Just want to point out that your example just above would still fail with the case statement. You could spend a lot of time developing a case statement or just use teds example.

select case when '0000000000' = '' then 0 else
month (date('0000000000')) end                
from sysibm.sysdummy1

[jayvaughn]

Just want to point out that your example just above would still fail with the case statement. You could spend a lot of time developing a case statement or just use teds example.

select case when '0000000000' = '' then 0 else
month (date('0000000000')) end
from sysibm.sysdummy1

it could - if i'm confident (which i am) it will always either contain blanks or a valid date I am good
- otherwise I need the UDF - thanks Ted for the UDF

[jayvaughn]

question on the UDF... am i able to have it do an insert to an error file within the UDF? I have made the following similiar change to the UDF and it did create but when it runs, i get...
Token INSERT was not valid. Valid tokens: (.

Or am i going to have to look at creating a UDTF?

create function qgpl/IsValidDate (InValue varchar(10)) returns char (1) language sql returns null on null input begin declare CharDate date; declare exit handler for SQLState '22007' insert into error file; return '0'; set CharDate = Date(InValue); return '1'; end

[TedHolt]

I assume you can make the function insert into a table, Jay. I'd have to try it out.

But your insert doesn't have any values.

insert into error_file values('1")

[jayvaughn]

I assume you can make the function insert into a table, Jay. I'd have to try it out.

But your insert doesn't have any values.

insert into error_file values('1")

Ted - thanks - I said "similiar to" - sorry was not the exact code (that was the concept though)...
so i got it to work by declaring a "continue" handler...


declare continue handler for SQLState '22007'
begin
insert into cnverr (erpgmid ...


btw, do you have similiar UDF's to validate CHAR and NUMERIC data?

[TedHolt]

Anything is valid character data. I probably don't understand your question.

Here's a function for numeric data:

https://www.itjungle.com/2008/10/08/fhg100808-story02/

[jayvaughn]

Anything is valid character data. I probably don't understand your question.

Here's a function for numeric data:

https://www.itjungle.com/2008/10/08/fhg100808-story02/

you got me on that one - duh - thanks! looking at the numeric - really appreciate it.