Help needed in Bteq Export

Database

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 ;



28 REPLIES

Re: Help needed in Bteq Export

Help Please :(

N/A

Re: Help needed in Bteq Export

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 ''),

Re: Help needed in Bteq Export

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;


Re: Help needed in Bteq Export

Hi Experts

Help Please

N/A

Re: Help needed in Bteq Export

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!

Re: Help needed in Bteq Export

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

Re: Help needed in Bteq Export

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

N/A

Re: Help needed in Bteq Export

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

Re: Help needed in Bteq Export

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