Devendra Singh 4 Posted November 8, 2021 Share Posted November 8, 2021 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 Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 9, 2021 Share Posted November 9, 2021 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 Link to comment Share on other sites More sharing options...
Toby Mills Posted November 9, 2021 Share Posted November 9, 2021 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. Link to comment Share on other sites More sharing options...
Walter Brengel Posted November 9, 2021 Share Posted November 9, 2021 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 Link to comment Share on other sites More sharing options...
Devendra Singh 4 Posted November 9, 2021 Author Share Posted November 9, 2021 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 Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 10, 2021 Share Posted November 10, 2021 And what about Walter highlight Do you have UTF-8 code page assigned Link to comment Share on other sites More sharing options...
Devendra Singh 4 Posted November 10, 2021 Author Share Posted November 10, 2021 Hi Martin, Yes, we have the UTF-8 code page assigned. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 10, 2021 Share Posted November 10, 2021 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); Link to comment Share on other sites More sharing options...
Devendra Singh 4 Posted November 12, 2021 Author Share Posted November 12, 2021 Hey Martin, It didnt worked. Is there any other way, I can achieve it. Link to comment Share on other sites More sharing options...
Martin Yergeau Posted November 12, 2021 Share Posted November 12, 2021 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 Link to comment Share on other sites More sharing options...
Devendra Singh 4 Posted November 12, 2021 Author Share Posted November 12, 2021 sure. I have opened a case with TechSupport now. Link to comment Share on other sites More sharing options...
Yannick Reformat Posted February 7, 2023 Share Posted February 7, 2023 Bonjour,j'ai le même problème.Quel est le numéro/lien du Case ?bien cordialementYRT Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now