Error handling when inserting records

Database
Enthusiast

Error handling when inserting records

I have to drop tables from a few databases if they have expired. The expiry date is mentioned in comment string. I am working on a macro as below. My question is - How to throw an error with the databasneame and  tablename ,if that table does not have the expiry date right or not in the mentioned format. Commenstring should be like "YYYY-MM-DD  XXXXXX" 

INSERT DB1.Drop_tables

sel   

case when date <= (

SEL

CAST(otranslate ( A.commentstring, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()', '')  AS date format 'yyyy-mm-dd'   ) (char(10))     -- AS DECIMAL(18,0)) 

from dbc.tables A

where databasename = 'DB1'

--and tablename = 'test'


then tablename else null end as tablename 

from dbc.tables

where databasename = 'DB1';

Any inputs are appreciated.

12 REPLIES
Enthusiast

Re: Error handling when inserting records

I tried using the - Logging errors and Error table . However,it does not insert any records in the ET_Drop_tables though it gives an error on the INSERT.

create  error table for  Drop_tables ;

INSERT DB1.Drop_tables

sel   

case when date <= (

SEL

CAST(otranslate ( A.commentstring, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ! @#$%^&*()', '')  AS date format 'yyyy-mm-dd'   ) (char(10))     -- AS DECIMAL(18,0)) 

from dbc.tables A

where databasename = 'DB1'

--and tablename = 'test'

then tablename else null end as tablename 

from dbc.tables

where databasename = 'DB1'

logging errors;

Enthusiast

Re: Error handling when inserting records

Any inputs?

Re: Error handling when inserting records

Hi Experts,

Please any one tell me

when i inserting recoreds from oracle to teradata like('?') but it's show in teradata just opposite of '?'.

Enthusiast

Re: Error handling when inserting records

Dieter,need your help please!

Enthusiast

Re: Error handling when inserting records

I am not Dieter, but just trying to help you, if it helps you. I hope you have already checked that you have insert privilege into error table.

See the select part alone first.

Did you have a look at this link if it helps you: 


Check the characters properly too.

My suggestion is do step by step, not at one go.

Enthusiast

Re: Error handling when inserting records

Thank you for replying. I have the stirpping of date logic running perfectly. My issue is how to send an email out with the tablename which has an error in the commentstring like one of these below:

1. Does not have date.

2. Does not have date format right. etc.

Especially the logic which throw an error at a particular table while inserting into Drop_tables.

Enthusiast

Re: Error handling when inserting records

Probbaly I am looking for something like an Exit handler and/or continue handler which will send an email with the tablename in error.

Enthusiast

Re: Error handling when inserting records

For emailing, I usually put my TD script in unix scripting and use  mailx -s ".....".... I am not aware of, if there is any feature that can  handle within TD itself.

Cheers,

Enthusiast

Re: Error handling when inserting records

Thanks Raja.

My main questoin is still how to throw an error with the tablename whose comments is not in the correct format.