Custom Message in Bteq

Tools
Enthusiast

Custom Message in Bteq

Hi All,

I want a customized error output message for each statement failure in bteq. I tried with the below code but getting error

*** Error: Illegal value "MSG" specified. Notify ignored.

insert into target_table1 sel * from source;

.if errorcode<>0 then .notify msg 'Bteq failed in table1 insert'; .quit errorcode;

insert into target_table2 sel * from source;

.if errorcode<>0 then .notify msg 'Bteq failed in table2 insert'; .quit errorcode;

is there any other way I can give a custom error message for each failure to findout the failure step exactly ?

Thanks,

Ambuj

4 REPLIES
Enthusiast

Re: Custom Message in Bteq

I also tried with .REMARK but the custom message is not getting populated in the logfile.

Junior Contributor

Re: Custom Message in Bteq

NOTIFY:

- is not writing to standard output, but to EventLog on Windows or system log on Unix.

- must be specified before the SQL commands

- has a different syntax

.notify HIGH msg 'Bteq failed in table1 insert'; 
insert ....;
.if ERRORCODE<>0 THEN .quit ERRORCODE;

But REMARK should be what you need, what do you mean by " not getting populated in the logfile"?

.if errorcode<>0 then .REMARK 'Bteq failed in table2 insert'; .quit errorcode;
Enthusiast

Re: Custom Message in Bteq

Since you mention about logfile, I m thinking that you may want to use .goto and label too......

can you try with this:

insert into....

.if errorcode <> 0 then .goto insert1

insert into....

.if errorcode <> 0 then .goto insert2

......

.label insert1

.quit 3

.label insert2

.quit 2

.label insert3

.quit 1

.logoff

or you can try with this:

.if errorcode <> 0 then .os echo '.......goto abcdefgh........' > mmmmmm.txt;

In Unix/Linux env, once you are out of bteq, you can use $? and compare with 0 and you can echo a message as per your convenience. Bteq along with Linux/Unix is always delicious :)

Enthusiast

Re: Custom Message in Bteq

all the 3 approaches work perfectly. Thanks Dieter and Raja