BTEQ and OReplace

Tools & Utilities

BTEQ and OReplace

I have written a SQL statement using the oReplace function and it works fine within SQL Assistant.  When I run the .sql file through BTEQ however and output the resulting file as a .CSV, every column after the first column that uses the oReplace function does not get output.  If I shift the oReplace columns to the end then everything is exported except for the last oReplace column (there are two).  It appears as if the function is working properly, the comma I am removing and replacing with a Null does just that.  Why are all subsequent columns dropped from the export?

Here are the relevant BTEQ lines in my KSH script:

bteq<<EOF

.SET SESSION TRANSACTION BTET;

.logon ${TD_HOST}/${TD_USER},${TD_PASSWORD}

BT;

.EXPORT REPORT FILE = ${v_Tarfile}

.SET SIDETITLES OFF;

.SET TITLEDASHES OFF;

.SET NULL AS '';

.SET WIDTH 2000;

.SET SEPARATOR ',';

.RUN FILE = ${v_SQLfile};

.export reset

.IF ERRORCODE <> 0 THEN .GOTO ERRORS

ET;

.QUIT

.LABEL ERRORS

.QUIT ERRORCODE

3 REPLIES
Junior Supporter

Re: BTEQ and OReplace

Hi.

And if the problem is in the .sql, why don't you post it instead of the shell script?

Cheers.

Carlos.

Junior Contributor

Re: BTEQ and OReplace

Check the datatype returned by oReplace, it's probably a VarChar(8000), but your .WIDTH is set to 2000, which leads to truncation. You need to cast to a properly sized VarChar.

Re: BTEQ and OReplace

Thank you Dieter, that was exactly the case.  I did not realize that by not explicity casting the oReplace that it would expand the field size so significantly.  Casting back to VarChar(32) corrected the issue.