Hi Parag
Instead of chr(9) use x'09' which is the hex char for tab:
select c1||x'09'||c2||x'09'||c3||x'09',c4,c5
from t1;
Cheers,
Andy
Hi Andy,
Thanks for the update. I tried this option but when I try to export the data in the file it is not giving a tab delimited file.
Hi,
The reason that this is not working is because in the exported file, the tab in file gets converted to blank spaces. Are you doing the exports in Windows or in Unix? In Unix there is no specification of a Tab separated file. You can try one work around which is, export a special character between the fields you want the tabs. Once the export is done, replace the special character with a Tab. If that works for you, great, otherwise I will see what else I can think of.
Hi Arun,
Thanks for the update.
As you said I am trying to do this export at Unix level. The option that you have mentioned can not be applied because at our end we are trying to get the export and FTP that file to the client server. In short, my program will first call the bteq script which will create this export file and then a shell script is called which will be FTPed to client server.
So I can not get the files to desktop for replacing the special character by tab.
If there is no other option then I will have to run a shell script after the export file is created whcih will find the special character and replace it by tab and then will FTP the file. May be that will be the last option I can try if tab delimited file is not created using BTEQ.
Regards
Parag
Hi Parag,
AFAIK there's no way to export special characters using REPORT mode in BTEQ, because that mode was ment for printing reports.
Of course you could use DATA mode, but then you'll have to get rid of the first two bytes of the exported row, which indicates the length of the following Varchar.
So you'll need a shell script again or you have to write an Access Module to do that.
Dieter
Hi Parag
Yes sorry I forgot to mention the fact that data mode plus some subsequent file massaging is required for that to work.
Hi,
I am trying to covert a oracle query using bteq.
The query in oracle is as follows:
select c1||chr(9)||c2||chr(9)||c3||(chr9),c4,c5
from t1;
As you can see the first 3 values are tab delimited. While converting in BTEQ when I try to use the same format, chr(9) is not recognized which means I do not get tab delimited values.
I tried to replace chr(9) by its character litral 0b, but was not able to do the so. If I use .SET SEPERATOR then all columns will be table seperated which is not the requirement.
Please let me know how such a scenario is handeled in BTEQ.