All Forums Database
Niesh20us 78 posts Joined 06/13
03 Apr 2014
Help needed in Bteq Export

Hi All,
I am trying to migrate my SAS export script to Bteq export script.
Thorugh SAS it generates file in below fixed width format

3033155992601282000000000005000CashPromotion 

3038142768472181000000000000500Atm Incentive 

3038037319092181000000000000500Atm Incentive 

 

While i am genarating the file it gives me below format

 

3082309659522181    500.00         Atm Incentive

3038145552902181    500.00         Atm Incentive

3033151139962181      50.00         Atm Incentive

 

 

Actually this blank portion highlighted above should be padded with '0' and values after decimal should not come .

 

Can someone please help. Below is my script

 

 

 

bteq <<ZBTEQ

.set session transaction BTET;

.run file=$HOME/.tdlogon

.set titledashes off;

.set width 86

.set recordmode off

.set separator '';

 

 

.export report file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/ iFRE_Testing/Bteq/Payment_file.txt

     

Select 

CAST (BANK_NUM AS CHAR(2))  (title ''),

CAST (ACCT_NUM AS CHAR(10))  (title ''),

CAST(INC_TRXN_CD AS CHAR(8))   (title ''),

CAST (inc_amt*100 AS CHAR(15))  (title ''),

     case when FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt '  AS CHAR(13))

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive' AS CHAR(13))

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive'  AS CHAR(13))

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo' AS CHAR(13)) else  CAST ('CashPromotion' AS CHAR(13)) end    (title ''),

          '                                   '  (title '')

FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

 

.export reset

.QUIT ERRORCODE ;

 

 

 
 

Niesh20us 78 posts Joined 06/13
03 Apr 2014

Help Please :(

Jimm 298 posts Joined 09/07
03 Apr 2014

When you multiply the inc-amt by 100 to get the pence, you are multiplying a decimal field with two decimal places by an integer, so the answer is still a decimal with two decimal places.
You need to give this a numeric format before casting it, so
 
CAST ((inc_amt*100 (format 9(15))) AS CHAR(15))  (title ''),

Niesh20us 78 posts Joined 06/13
03 Apr 2014

Thank you so much Jimm.
Only one issue :(
After using below query i am getting almost the desired output except very last column still coming as Char(13) , ideally there should be one more space . Like in below example after atm Incentive there should be an space but just after 'e' its going to next line even after i am putting it as char(14)
 

3082309659522181000000000000500Atm Incentive

3038145552902181000000000000500Atm Incentive

3033151139962181000000000000500Atm Incentive

 

 

Query:-

 

 

Select 

CAST (BANK_NUM AS CHAR(2))  (title ''),

CAST (ACCT_NUM AS CHAR(10))  (title ''),

CAST(INC_TRXN_CD AS CHAR(4))   (title ''),

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15))  (title ''),

     case when FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) else  CAST ('CashPromotion ' AS CHAR(14)) end    (title '')

FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

 

 

 

 

Niesh20us 78 posts Joined 06/13
04 Apr 2014

Hi Experts
Help Please

Jimm 298 posts Joined 09/07
04 Apr 2014

Its a problem with BTEQ - when you export report, any trailing space on a line is truncated - it does not show up on the paper!
You need to change to export data. The separator, titles/ titledashes are then superfluous, but I would leave them on with a comment!
Your script is ok, but make sure all future fields are CHAR fields, not VARCHAR. A character constant is VARCHAR unless you tell SQL explicitly!

Niesh20us 78 posts Joined 06/13
04 Apr 2014

Hi Jimm,
 
I changed to below but it does not work
 

bteq <<ZBTEQ

.set session transaction BTET;

.run file=$HOME/.tdlogon

.set titledashes off;

.set width 45

.set recordmode off

.set separator '';

 

 

.export data = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt

     

 

Select 

CAST (BANK_NUM AS CHAR(2))  (title ''),

CAST (ACCT_NUM AS CHAR(10))  (title ''),

CAST(INC_TRXN_CD AS CHAR(4))   (title ''),

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15))  (title ''),

     case when FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) else  CAST ('CashPromotion ' AS CHAR(14)) end    (title '')

FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

 

 

.export reset

.QUIT ERRORCODE ;

 

I would appriciate if you could give me the syntax.

 

Thanks,

Neel

Niesh20us 78 posts Joined 06/13
04 Apr 2014

Oh ok if i am getting it correctly . Are you asking me to change the delimiter? If yes then this not the option because i will have to send this file to other server which expects only fixed width file :(

Jimm 298 posts Joined 09/07
04 Apr 2014

When you export data, you dont have a delimiter unless you export one as part of the query.
When you say it does not work, what is happening; what error is reported from bteq.
A couple of likely causes:
.export data = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
should be 
.export data file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
Export data will always output in recordmode, so remove the ".set recordmode off" (I thought it would just ignore it.)

Niesh20us 78 posts Joined 06/13
04 Apr 2014

Its working but giving some weird result. Sorry to bother you but do you know how to correct the format

-^@3082309659522181000000000000500Atm Incentive

-^@3038145552902181000000000000500Atm Incentive

-^@3033151139962181000000000000500Atm Incentive

-^@3038137904662181000000000000500Atm Incentive

-^@3082301242062181000000000000500Atm Incentive

 

Niesh20us 78 posts Joined 06/13
05 Apr 2014

Hi Experts ,
I am using below script . Everything is as expected but only thing is i am getting some extra characters at the begining of the line. Please help
 

-^@3033151139962181000000000000500Atm Incentive

-^@3038137904662181000000000000500Atm Incentive

-^@3082301242062181000000000000500Atm Incentive

-^@3033149077782181000000000000500Atm Incentive

-^@3033153916432181000000000000500Atm Incentive

-^@3033129962182181000000000000500Atm Incentive

-^@3082304293252181000000000000500Atm Incentive

-^@3082308230072181000000000000500Atm Incentive

-^@3033159301291282000000000005000ReferralPromo

-^@3033151976261282000000000005000ReferralPromo

-^@3033125459172181000000000000500Atm Incentive

 

 

Script:-

 

 

bteq <<ZBTEQ

.set session transaction BTET;

.run file=$HOME/.tdlogon

.set titledashes off;

.set width 45

 

 

.export data file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt

     

 

Select 

CAST (BANK_NUM AS CHAR(2))  (title ''),

CAST (ACCT_NUM AS CHAR(10))  (title ''),

CAST(INC_TRXN_CD AS CHAR(4))   (title ''),

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15))  (title ''),

     case when FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) else  CAST ('CashPromotion ' AS CHAR(14)) end    (title '')

FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

 

 

.export reset

.QUIT ERRORCODE ;

Niesh20us 78 posts Joined 06/13
07 Apr 2014

Help Please

krishaneesh 140 posts Joined 04/13
07 Apr 2014
Select cast(
CAST (BANK_NUM AS CHAR(2))  (title '')||
CAST (ACCT_NUM AS CHAR(10))  (title '')||
CAST(INC_TRXN_CD AS CHAR(4))   (title '')||
CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15))  (title '')||
     case when FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))
          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))
          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))
          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) else  CAST ('CashPromotion ' AS CHAR(14)) end    (title '')) as char(45)
FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

we faced the same error which is generated by default when we export in data format. we resolved by casting the total output into the char(sum of all internal datatypes) and concatenating all of them like I showed above.

Niesh20us 78 posts Joined 06/13
07 Apr 2014

Somehow this query is not working :( Please help. When  i removing commented part it stops working
 

Select  --CAST (

 

CAST (BANK_NUM AS CHAR(2)) ||

 

CAST (ACCT_NUM AS CHAR(10)) ||

 

CAST(INC_TRXN_CD AS CHAR(4))  ||

 

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15))  ||

 

CAST(    case when FFL_DESC like '%ASSOC EXEMPT%' then 'Assoc Exempt  '

               when FFL_DESC like '%EXCEPTION LOG%' then  'CashIncentive '

               when FFL_DESC = 'NP STANDARD' then 'Atm Incentive '

               when PROMO_CD = 'RAF' THEN 'ReferralPromo '  else  'CashPromotion '  end  as char(14))

 

-- ) AS  char(45)  title('')

 

FROM UD466.FF_PRCSS 

where  ELG_FLG = 'Y' 

ORDER BY bank_num;

Raja_KT 1246 posts Joined 07/09
07 Apr 2014

Why did you comment it? It is a concatenation of all and it is titled as ''.
Also, when you get extra characters at the beginning of the line , you can easily remove them using unix.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Niesh20us 78 posts Joined 06/13
07 Apr 2014

If i uncomment then its does not work. Is there any syntax issue?

krishaneesh 140 posts Joined 04/13
07 Apr 2014
Select   cast((
CAST (BANK_NUM AS CHAR(2)) ||
CAST (ACCT_NUM AS CHAR(10))||
CAST(INC_TRXN_CD AS CHAR(4)) ||
CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15)) ||
     case 
    when    FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))
          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))
          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))
          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) 
else   CAST ('CashPromotion ' AS CHAR(14)) 
end   ) as char(45)) as "concatenated result"
FROM   UD466.FF_PRCSS 
 
where    ELG_FLG = 'Y'
 
ORDER BY bank_num;

My Bad!! missed the title and an additional set of braces. . it does not play a role for each column when concatenated.

Niesh20us 78 posts Joined 06/13
08 Apr 2014

Thanks but no luck :( still its giving the same issue 

-^@3038148303722181000000000000500Atm Incentive

-^@3082314501071282000000000005000ReferralPromo

-^@3030100756522181000000000000500Atm Incentive

-^@3038030746302181000000000000500Atm Incentive

-^@3033159156851282000000000005000ReferralPromo

-^@3033153457142181000000000000500Atm Incentive

-^@3082307728442181000000000000500Atm Incentive

-^@3038012272512181000000000000500Atm Incentive

-^@3033158230711282000000000005000ReferralPromo

-^@3033130359042181000000000000500Atm Incentive

-^@3036100873602181000000000000500Atm Incentive

 

 

Script:-

 

bteq <<ZBTEQ

 

.set session transaction BTET;

 

.run file=$HOME/.tdlogon

 

.set titledashes off;

 

.set width 45

 

.set recordmode off

 

.export data file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt

     

 

Select  ( cast((

CAST (BANK_NUM AS CHAR(2)) ||

CAST (ACCT_NUM AS CHAR(10))||

CAST(INC_TRXN_CD AS CHAR(4)) ||

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15)) ||

     case

    when    FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) 

else   CAST ('CashPromotion ' AS CHAR(14)) 

end   ) as char(45)) as "concatenated result" ) 

FROM   UD466.FF_PRCSS 

  

where    ELG_FLG = 'Y'

  

ORDER BY bank_num;

 

 

.export reset

.QUIT ERRORCODE ;

 

Niesh20us 78 posts Joined 06/13
08 Apr 2014

Thanks Raja . I know we can remove first character thorugh unix but is there no option to handle in TD.

krishaneesh 140 posts Joined 04/13
08 Apr 2014

what do you see the output for just the query in sql assistant. if it is as per your requirement then try changing all the inner char to varchar  keeping the wrapped char as char(45) and try exporting which should work ideally.

Niesh20us 78 posts Joined 06/13
08 Apr 2014

Its again giving the same output
 
-^@3038148303722181000000000000500Atm Incentive
-^@3082314501071282000000000005000ReferralPromo
-^@3030100756522181000000000000500Atm Incentive
-^@3038030746302181000000000000500Atm Incentive
-^@3033159156851282000000000005000ReferralPromo
-^@3033153457142181000000000000500Atm Incentive
-^@3082307728442181000000000000500Atm Incentive
-^@3038012272512181000000000000500Atm Incentive
-^@3033158230711282000000000005000ReferralPromo
-^@3033130359042181000000000000500Atm Incentive
-^@3036100873602181000000000000500Atm Incentive
-^@3033157722721282000000000005000ReferralPromo
-^@3038136860572181000000000000500Atm Incentive
 
 
Script:-
 
bteq <<ZBTEQ
.set session transaction BTET;
.run file=$HOME/.tdlogon
.set titledashes off;
.set width 45
.set recordmode off
.export data file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
    
Select  ( cast((
CAST (BANK_NUM AS VARCHAR(2)) ||
CAST (ACCT_NUM AS VARCHAR(10))||
CAST(INC_TRXN_CD AS VARCHAR(4)) ||
CAST ((inc_amt*100 (Format '9(15)')) AS VARCHAR(15)) ||
     case
    when    FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS VARCHAR(14))
          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS VARCHAR(14))
          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS VARCHAR(14))
          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS VARCHAR(14))
else   CAST ('CashPromotion ' AS VARCHAR(14))
end   ) as char(45)) as "concatenated result" )
FROM   UD466.FF_PRCSS
 
where    ELG_FLG = 'Y'
 
ORDER BY bank_num;

.export reset
.QUIT ERRORCODE ;

Niesh20us 78 posts Joined 06/13
09 Apr 2014

can someone help?

dnoeth 4628 posts Joined 11/04
09 Apr 2014

You export in DATA mode, which includes a two-byte record length.
Why don't you use REPORT mode? 

Dieter

Niesh20us 78 posts Joined 06/13
09 Apr 2014

Hey Dieter ,
 
I have used everything it seems. Can you please tell me how to change it to Report mode?

dnoeth 4628 posts Joined 11/04
09 Apr 2014

.export REPORT file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt

Dieter

Niesh20us 78 posts Joined 06/13
09 Apr 2014

Actually its back to square . Now i am not getting extra characters at the begining but i am getting very last column still coming as Char(13) , ideally there should be one more space . Like in below example after atm Incentive there should be an space but just after 'e' its going to next line even after i am putting it as char(14). Also please tell me why this title thing is not working in the query i dont want header in the query.
Please Help :(
concatenated result

3038148303722181000000000000500Atm Incentive

3082314501071282000000000005000ReferralPromo

3030100756522181000000000000500Atm Incentive

3038030746302181000000000000500Atm Incentive

3033159156851282000000000005000ReferralPromo

3033153457142181000000000000500Atm Incentive

3082307728442181000000000000500Atm Incentive

3038012272512181000000000000500Atm Incentive

 

Script:-

 

bteq <<ZBTEQ

 

.set session transaction BTET;

 

.run file=$HOME/.tdlogon

 

.set titledashes off;

 

.set width 45

 

.export report file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt

     

 

Select  ( cast((

 

CAST (BANK_NUM AS CHAR(2)) ||

 

CAST (ACCT_NUM AS CHAR(10))||

 

CAST(INC_TRXN_CD AS CHAR(4)) ||

 

CAST ((inc_amt*100 (Format '9(15)')) AS CHAR(15)) ||

 

     case

 

    when    FFL_DESC like '%ASSOC EXEMPT%' then CAST ('Assoc Exempt  '  AS CHAR(14))

 

          when FFL_DESC like '%EXCEPTION LOG%' then CAST ( 'CashIncentive ' AS CHAR(14))

 

          When FFL_DESC = 'NP STANDARD' then CAST ('Atm Incentive '  AS CHAR(14))

 

          WHEN PROMO_CD = 'RAF' THEN CAST ( 'ReferralPromo ' AS CHAR(14)) 

 

else   CAST ('CashPromotion ' AS CHAR(14)) 

 

end   ) as char(45)) as "concatenated result" ) 

 

FROM   UD466.FF_PRCSS 

 

where    ELG_FLG = 'Y'

 

ORDER BY bank_num;

 

 

.export reset

.QUIT ERRORCODE ;

 

dnoeth 4628 posts Joined 11/04
09 Apr 2014

Sorry, i didn't read the first posts in detail :(
REPORT mode never writes trailing blanks (it was ment to be sent directly to a printer).
The only way to prevent that is to right-align the last column with leading blanks or to add a dummy character.
 
Or switch to FastExport or TPT...
 

 

Dieter

King Cobra 1 post Joined 06/14
16 Jun 2014

Hi,
When i tried exporting data in file from Teradata table via Bteq Export, then got warning stating only 100 columns can be returned.
When tried with fastexport, then data is becoming corrupt in file. Hence, finally i switched to TPT export and it works fine with me.
 
Now, when i am trying to export data from a big table suppose occuping 4-5 GB space in DB. then TPT job gets terminated.
 
Please help..!!

M.Saeed Khurram 544 posts Joined 09/12
17 Jun 2014

Hi King,
One possible option is to split the single large data set to mutiple file based on a condition. Can you please paste the TPT termination error message?
 

Khurram

You must sign in to leave a comment.