|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/4/2007 7:16:00 PM
Posts: 4,
Visits: 1
|
|
Hi,
1. I have a bteq script in which I am exporting data. I have a trim statement written in 2 diffrent forms as shown below:
SELECT
trim(c1.grp_cd) (title 'LC2'),
coalesce(trim ( both ' ' from c2.parent_grp_cd ),'') (title 'LC1'),
coalesce(trim ( trailing ' ' from c2.parent_grp_cd ),'') (title '').
But on every instance it is adding additioal spaces to the output columns. The total width of data in that column is equal to column width (data + white spaces).
Not sure if this is an issue with bteq when exporting data.
2. I have used .set separator ' '; command (tab seperator) in the script and I am exporting data in a txt file. If I write a script as given:
select
'No 1' (title '')
'No 2' (title '')
;
select c1,c2
from t1;
In the output I get a blank line followed by title, again a blank line and then the data.
But if written is this way,
select c1 (title 'No 1'),
c2 (title 'No 2'
from t1;
the output in this case is a correctly dispayed. So not able to get why such 2 different outputs for same functionality.
Regards
Parag
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 6/27/2008 7:19:28 AM
Posts: 42,
Visits: 54
|
|
Firstly, you could try this syntax :
trim( ... ' ' from coalesce(c2.parent_grp_cd,'')) (title 'LC1')
because trim(null) is NULL.
Secondly, how do you think BTEQ can evaluate the width of columns ?
It's simply based on the largest width the expression could have... and so the width of the column, without trim !
If you want limit the width of your columns, cast it...
SELECT
cast(c1.grp_cd as char(XXX))(title 'LC2'),
cast(coalesce(c2.parent_grp_cd,'') as char(XXX)) (title 'LC1'),
cast(coalesce(c2.parent_grp_cd,'') as char(XXX)) (title '').
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/22/2008 6:31:29 AM
Posts: 2,
Visits: 6
|
|
The proposed resolution only forces the output to shrink to a specified size, which may lead to data truncation at some cases. Also the specified size will contain spaces in addition to the data values.
I have also faced the same problem while exporting from BTEQ. I am not sure how bteq recognizes the field width, but it certainly does. Not only BTEQ, even while spooling files from Oracle field values are getting space padded to the Database field width. (evne though datatype is Varchar)
I wished to put TAB as separator but the following also didn't work
.set separator '9'xc
rather i had to hardcord the same .set separator 'actually pressed tab from keyboard'
Please suggest...
satyasai
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/21/2008 10:45:25 PM
Posts: 79,
Visits: 98
|
|
BTEQ knows how much room each column could take, because the database provides that as part of the information to BTEQ. Therefore BTEQ has to space everything out to what could be the worse case column length. If you want to do your own separation then concatenate all of the columns into a string using the appropriate separator or space or whatever. It is up to you. Here is an example. You should be able to improvise from that using your own data.
CREATE SET TABLE RGS.test2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
c1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
c2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
c3 INTEGER)
PRIMARY INDEX ( c1 );
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test2;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
c1 c2 c3
---------- -------------------- -----------
sample sample2 1
BTEQ -- Enter your DBC/SQL request or BTEQ command:
select trim(c3) || ',' || trim(c1) || ',' || trim(c2) (title 'c3,c2,c1') from test2;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
c3,c2,c1
-------------------------------------------
1,sample,sample2
|
|
|
|