BTEQ : Missing columns and special characters in flat file export

Tools

BTEQ : Missing columns and special characters in flat file export

Hello Everyone,

I am trying to export data from a teradata table to a flat file. My table columns are Unicode formatted in the DDL itself.

Session character set is set to UTF8 inline to my table data definition, because I expect to encounter few foreign language characters as well.

1. Record/Data mode :-

I see all the columns with a lot of special characters. I believe they are data indicators and row length etc because my table data is appearing fine at the SQL Assistant.

2. Record/Data mode and .set indicdata off;

Now the special characters don't appear but I see only 2 columns of the original 80 columns returned by the BTEQ statement. I have set width to even 60,000.

Interesingly, whichever 2 columns I choose in my SELECT sql are showing up fine in the flat file but nothing more than 2 columns. So I can't say that something is wrong with my data or a specific datatyped column.  Its like a capping of 2 columns is placed over the file which I hope is just my imagination.

3 Report mode :-

I thought data mode written records be problematic to view in normal editor so I went for Report mode.

Same result as in no. 2 above. I see only 2 columns.

I could give a try to EXPORT REPORT WIDE but my columns aren't too big anyways and more than 2 columns (3 or more) are never showing up. When I try so, only the first two columns appear in the file.

Please help.

Regards,

Virendra

3 REPLIES
N/A

Re: BTEQ : Missing columns and special characters in flat file export

You need to share the table DDL and the bteq script. Otherwise it is just guessing from our side.

Re: BTEQ : Missing columns and special characters in flat file export

Virendra - Can you share the script plz.......

Re: BTEQ : Missing columns and special characters in flat file export

.LOGON IPADDRESS/UN,PW

 DATABASE ACCESS_VIEWS;

.SET ECHOREQ OFF;

.SET TITLEDASHES OFF;

.set RECORDMODE OFF; 

.SET FORMAT OFF;

.SET PAGEBREAK OFF;

.set separator '|';

.EXPORT report file = C:\Exported_Buyer_Payouts.txt;

.SET PAGEBREAK OFF;

-- BUYER PAYOUT QUERY

select

TRIM(TRAILING from a11.MERCHANT_NAME) Merchant_Email,

a11.CASH_STLMNT_DT (format 'mm-dd-yyyy') Settlement_Date,

CAST(sum(case when a12.ACTN_CD in(110,107) then -a11.TRANS_AMT else 

a11.TRANS_AMT end ) AS Decimal(15,2)) (title 'Money') 

Buyer_Payout_Transaction_Amount

from ACCESS_VIEWS.SSA_EMS_PYMNT_FACT a11

join Access_Views.DW_EMS_JRNL_CNFG a12

 on (a11.EMS_RECONCILE_JRNL_CNFG_ID = a12.EMS_JRNL_CNFG_ID)

join ACCESS_VIEWS.DW_COUNTRIES a13

 on (a11.SELLER_CNTRY_ID = a13.CNTRY_ID)

join ACCESS_VIEWS.DW_REV_ROLLUP a14

 on (a13.REV_ROLLUP = a14.REV_ROLLUP and 

a13.REV_ROLLUP_ID = a14.REV_ROLLUP_ID)

join Access_Views.DW_ODR_CPS_CLAIM a15

 on (a11.CPS_CLAIM_ID = a15.CPS_CLAIM_ID)

join Access_Views.DW_CURRENCIES a16

 on (a11.PYMNT_CURNCY_ID = a16.CURNCY_ID)

join Access_Views.DW_EMS_LKP_PYMT_OPTN a17

 on (a11.PYMNT_OPTION_ID = a17.PYMT_OPTN_ID)

join Access_Views.DW_BW_LEGAL_ENTITY a18

 on (a14.LEGAL_ENTITY_ID = a18.LEGAL_ENTITY_ID)

join Access_Views.DW_SITES a19

 on (coalesce(a15.TRANS_SITE_ID, -999) = a19.SITE_ID)

where (a11.CLIENT_ID = 19

 and a11.PRCS_RSPNS_CD in ('T0001', 'T0015', 'T1114', 'T1115')

 and a11.CASH_STLMNT_DT between  (current_date-28 - extract(day from 

current_date-28))+1 and (current_date+28 - extract(day from current_date+28)) 

 and a11.PYMNT_CURNCY_ID in (1, 2, 3, 5, 7, 13, 22, 34, 35, 39, 84))

group by 1, 2

ORDER by 2 asc, 3 desc;

.EXPORT RESET

.LOGOFF

--.QUIT

.EXIT