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:
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.
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.