All Forums Database
Tnewbee 215 posts Joined 05/10
26 Mar 2014
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.

Tnewbee 215 posts Joined 05/10
26 Mar 2014

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;

Tnewbee 215 posts Joined 05/10
27 Mar 2014

Any inputs?

sanjay patidar 1 post Joined 03/14
27 Mar 2014

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 '?'.

Tnewbee 215 posts Joined 05/10
28 Mar 2014

Dieter,need your help please!

Raja_KT 1246 posts Joined 07/09
31 Mar 2014

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: 

http://forums.teradata.com/forum/analytics/how-strip-of-the-non-numeric-data-from-varchar-field

Check the characters properly too.

 

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Tnewbee 215 posts Joined 05/10
31 Mar 2014

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.
 

Tnewbee 215 posts Joined 05/10
31 Mar 2014

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

Raja_KT 1246 posts Joined 07/09
01 Apr 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Tnewbee 215 posts Joined 05/10
03 Apr 2014

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

Raja_KT 1246 posts Joined 07/09
03 Apr 2014

Hi,
I am not able to understand clearly. Are you able to get the error table?
Below sample ksh script can help you(test it before running). You can think of redirecting the output to a file. Then take that file content and compare it against a right format.

bteq<<! >>${LOG_FILENAME} 2>&1

.logon your DB_NAME/username,password

.run file $HOME/abc.sql

.quit

!

 val=$?

if [ $val -eq 0 ]

then

echo "Put the logic here"

##########

else

##########

echo "FAILURE HERE"

fi

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Tnewbee 215 posts Joined 05/10
07 Apr 2014

Thanks Raja for your inputs but my question is still different. 
Dieter can you help please?

Adeel Chaudhry 773 posts Joined 04/08
08 Apr 2014

Please have a look at Jimm's reply on following link:
 
http://forums.teradata.com/forum/database/sending-email-from-procedure
 
And rgs's reply on:
 
http://forums.teradata.com/forum/connectivity/udf-for-calling-email-sending-program
 
You would be requiring to design an SP in a way which returns a text as output which contains the subject, email text, etc. And then you can use that to send an email out either by using .OS command or by using external-SP.
 
If none helps, either wait for Dieter to get back to you .... or contact your DBA/CS team to confirm if its doable in your TD version or not.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.