Hello, I am trying to create an .ftm file by combining the d...

[Devendra Singh 4]

Hello,

I am trying to create an .ftm file by combining the detail and trailer data from database table, but getting the extra spaces in the output file. Below is the sample code that, I have written.

FILEDEF DTL DISK app_kq_out/kq_points_rewards_file.ftm

TABLE FILE TKQ_OUT_REWARDPOINTSMRY_DTL

PRINT

REC_TYPE

BNS_ACCT_ID

PRODUCT_CODE

POINTS_CUR_BAL

POINTS_BONUS

POINTS_USED

POINTS_ADJ

POINTS_CORE_EARNED

POINTS_EARNED

POINTS_EARNED_TEIR1

POINTS_EARNED_TEIR3

POINTS_EARNED_TEIR2

ON TABLE HOLD AS DTL FORMAT ALPHA

END

-RUN

FILEDEF TRL DISK app_kq_out/kq_points_rewards_file.ftm (APPEND

TABLE FILE TKQ_OUT_REWARDPOINTSMRY_TRL

PRINT

REC_TYPE

BUS_LOAD_DATE

POINTS_USED

POINTS_ADJ

POINTS_EARNED

POINTS_CUR_BAL

TOTAL_RECORD_COUNT

ON TABLE HOLD AS TRL FORMAT ALPHA

END

-RUN

The .ftm file is having the extra spaces. When i checked using ON TABLE save, I see the length getting multiplied by 3 as shown in below log.

0 FIELDNAME ALIAS FORMAT LENGTH

PIR_ACCOUNT_NUM PIR_ACCOUNT_> A16 48

PIR_PLASTIC_NUM PIR_PLASTIC_> A3 9

PIR_PROC_DATE PIR_PROC_DAT> YYMD 8

PIR_SORT_MINOR PIR_SORT_MIN> I6 6

PIR_CYCLE_NUM PIR_CYCLE_NU> I6 6

Is this the expected behavior

Can any one please help me, how can we trim spaces for the all the fields, so that output .ftm file is generated with correct record length excluding the spaces.

Note: I am using IWay version 7705

[Martin Yergeau]

What is the definition of the read fields

If they are defined as varchar (AxxV) your problem may be there.

Using variable field add 6 digits in front of each alpha field that indicates the field lenght

Look at the difference between the two samples

With variable lenght

TABLE FILE CAR

PRINT SEATS

BY COUNTRY/A10V

BY CAR/A16V

BY MODEL/A24V

ON TABLE HOLD AS TMP

END

-RUN

 

TABLE FILE TMP

PRINT SEATS

BY COUNTRY

BY CAR

BY MODEL

ON TABLE SAVE AS TST FORMAT ALPHA

END

-RUN

 

ALPHANUMERIC RECORD NAMED TST

0 FIELDNAME ALIAS FORMAT LENGTH

COUNTRY E01 A10V 16

CAR E02 A16V 22

MODEL E03 A24V 30

SEATS E04 I3 3

TOTAL 71

 

000010ENGLAND 000016JAGUAR 000024V12XKE AUTO 2

000010ENGLAND 000016JAGUAR 000024XJ12L AUTO 5

000010ENGLAND 000016JENSEN 000024INTERCEPTOR III 4

000010ENGLAND 000016TRIUMPH 000024TR7 2

000010FRANCE 000016PEUGEOT 000024504 4 DOOR 5

000010ITALY 000016ALFA ROMEO 0000242000 4 DOOR BERLINA 4

000010ITALY 000016ALFA ROMEO 0000242000 GT VELOCE 2

000010ITALY 000016ALFA ROMEO 0000242000 SPIDER VELOCE 2

000010ITALY 000016MASERATI 000024DORA 2 DOOR 2

000010JAPAN 000016DATSUN 000024B210 2 DOOR AUTO 4

000010JAPAN 000016TOYOTA 000024COROLLA 4 DOOR DIX AUTO 4

000010W GERMANY 000016AUDI 000024100 LS 2 DOOR AUTO 5

000010W GERMANY 000016BMW 0000242002 2 DOOR 5

000010W GERMANY 000016BMW 0000242002 2 DOOR AUTO 4

000010W GERMANY 000016BMW 0000243.0 SI 4 DOOR 5

000010W GERMANY 000016BMW 0000243.0 SI 4 DOOR AUTO 5

000010W GERMANY 000016BMW 000024530I 4 DOOR 5

000010W GERMANY 000016BMW 000024530I 4 DOOR AUTO 5

Without variable lenght

TABLE FILE CAR

PRINT SEATS

BY COUNTRY

BY CAR

BY MODEL

ON TABLE SAVE AS TST FORMAT ALPHA

END

-RUN

 

ALPHANUMERIC RECORD NAMED TST

0 FIELDNAME ALIAS FORMAT LENGTH

COUNTRY COUNTRY A10 10

CAR CARS A16 16

MODEL MODEL A24 24

SEATS SEAT I3 3

TOTAL 53

 

ENGLAND JAGUAR V12XKE AUTO 2

ENGLAND JAGUAR XJ12L AUTO 5

ENGLAND JENSEN INTERCEPTOR III 4

ENGLAND TRIUMPH TR7 2

FRANCE PEUGEOT 504 4 DOOR 5

ITALY ALFA ROMEO 2000 4 DOOR BERLINA 4

ITALY ALFA ROMEO 2000 GT VELOCE 2

ITALY ALFA ROMEO 2000 SPIDER VELOCE 2

ITALY MASERATI DORA 2 DOOR 2

JAPAN DATSUN B210 2 DOOR AUTO 4

JAPAN TOYOTA COROLLA 4 DOOR DIX AUTO 4

W GERMANY AUDI 100 LS 2 DOOR AUTO 5

W GERMANY BMW 2002 2 DOOR 5

W GERMANY BMW 2002 2 DOOR AUTO 4

W GERMANY BMW 3.0 SI 4 DOOR 5

W GERMANY BMW 3.0 SI 4 DOOR AUTO 5

W GERMANY BMW 530I 4 DOOR 5

W GERMANY BMW 530I 4 DOOR AUTO 5

[Toby Mills]

devendra.singh:

 

0 FIELDNAME ALIAS FORMAT LENGTH

PIR_ACCOUNT_NUM PIR_ACCOUNT_> A16 48

PIR_PLASTIC_NUM PIR_PLASTIC_> A3 9

PIR_PROC_DATE PIR_PROC_DAT> YYMD 8

PIR_SORT_MINOR PIR_SORT_MIN> I6 6

PIR_CYCLE_NUM PIR_CYCLE_NU> I6 6

 

 

Hi Devendra

As @MartinY points out, you get an extra 6 characters in length for variable length fields. That might be it.

I couldnt help noticing that your output of your ON TABLE SAVE that none of your fieldnames match your example TABLE request. Is that what youre expecting

While I think the extra 6 characters from a variable field is likely, it doesnt really account for the PIR_ACCOUNT_NUM thats supposed to be A16 and ends up as 48 (as you pointed out- thats triple the size youd think).

Make sure you dont have another master coming into play in your app paths. Its possible youre not using the Master file you think you are.

To test, try adding this for your Trailer record (for example) with the DDNAME TRL:

WHENCE TRL MASTER

See if the answer comes back showing you a master in a folder you werent expecting.

Keep nosing around - youll find the problem. Itll make sense to you when you find it.

[Walter Brengel]

In addition to the additional length for the AnV field (which will have a AnW in the master), another cause of a multiplication of 3 for character output bytes is attributed to a unicode (UTF-8) codepage. For UTF-8 a single character can be up to 3 UTF-8 bytes so WebFOCUS needs to have enough space in case all characters require 3 bytes.

Walter

[Devendra Singh 4]

Thanks Martin and Toby for sharing the details.

All of my fields in database table are of CHAR, except the one field which is VARCHAR. Still, I am able to see the byte multiply *3 as below.

0 FIELDNAME ALIAS FORMAT LENGTH

REC_TYPE REC_TYPE A1 3

BNS_ACCT_ID BNS_ACCT_ID A13 39

PRODUCT_CODE PRODUCT_CODE A3 9

POINTS_CUR_BAL POINTS_CUR_B> A10 30

POINTS_BONUS POINTS_BONUS A8 24

POINTS_USED POINTS_USED A8 24

POINTS_ADJ POINTS_ADJ A8 24

POINTS_CORE_EARNED POINTS_CORE_> A10 30

POINTS_EARNED POINTS_EARNE> A8 24

POINTS_EARNED_TEIR1 POINTS_EARNE> A8 24

POINTS_EARNED_TEIR3 POINTS_EARNE> A8 24

POINTS_EARNED_TEIR2 POINTS_EARNE> A8 24

TOTAL 279

I have kept the usage and actual same as well in master files as below

FIELDNAME=BNS_ACCT_ID, ALIAS=BNS_ACCT_ID, USAGE=A13, ACTUAL=A13,

MISSING=ON, $

FIELDNAME=PRODUCT_CODE, ALIAS=PRODUCT_CODE, USAGE=A3, ACTUAL=A3,

MISSING=ON, $

FIELDNAME=POINTS_CUR_BAL, ALIAS=POINTS_CUR_BAL, USAGE=A10, ACTUAL=A10,

MISSING=ON, $

FIELDNAME=POINTS_BONUS, ALIAS=POINTS_BONUS, USAGE=A8, ACTUAL=A8,

MISSING=ON, $

FIELDNAME=POINTS_USED, ALIAS=POINTS_USED, USAGE=A8, ACTUAL=A8,

Still, I am getting the extra spaces in the output file.

Thanks & Regards,

Devendra

[Martin Yergeau]

And what about Walter highlight

Do you have UTF-8 code page assigned

[Devendra Singh 4]

Hi Martin,

Yes, we have the UTF-8 code page assigned.

[Martin Yergeau]

devendra.singh:

 

Yes, we have the UTF-8 code page assigned.

 

 

So you have your answer reagrding the 3x the lenght.

I am not sure that this can be avoided

If you try to perform this to each alpha field (but I doubt that it will work due to the code page used)

COMPUTE FLD1 /A3 = TRIM_(BOTH, ' ', FLD1);

[Devendra Singh 4]

Hey Martin,

It didnt worked.

Is there any other way, I can achieve it.

[Martin Yergeau]

Without changing the code page I doubt

But maybe someone else will have an answer

In the mean time, I suggest that you open a case with TechSupport

[Devendra Singh 4]

sure. I have opened a case with TechSupport now.

[Yannick Reformat]

Bonjour,

j'ai le même problème.

Quel est le numéro/lien du Case ?

bien cordialement

YRT