Carter Henderson Posted February 9, 2022 Share Posted February 9, 2022 I have this code that produces the details of a hold file: HOLD DAILYTOTALS END -RUN -EXIT` image.png981446 29.1 KB What is the code to do the same for a table file Thanks Link to comment Share on other sites More sharing options...
David Beagan Posted February 9, 2022 Share Posted February 9, 2022 FF CAR should do the trick. Link to comment Share on other sites More sharing options...
Charles Morris 2 Posted February 9, 2022 Share Posted February 9, 2022 or do an ON TABLE SAVE - It should give you a similar output of all fields in the TABLE FILE Link to comment Share on other sites More sharing options...
Carter Henderson Posted February 9, 2022 Author Share Posted February 9, 2022 Thanks a bunch. This was impossible to search in the docs Link to comment Share on other sites More sharing options...
Carter Henderson Posted February 9, 2022 Author Share Posted February 9, 2022 @david.beagan, @charles.morris Since yall were so helpful I have another question. I have filtered one hold file on the values in another by using WHERE field IN FILE HOLD and that works great in one instance. In another instance it doesnt at all. The second table will not pull from the HOLD FILE ONE when the define ENDOFWEEK is included. When the define is removed or is in the summary its works. Why does it do this and how can I fix it I need the grouping by the weekend date. DEFINE FILE fuel_dept/metadata/purchases_by_padd/tbl_fuel_purchases_by_fuelpadd ENDOFWEEK/YYMD = DTRUNC(TRANSACTION_DATE,WEEK_END); END TABLE FILE fuel_dept/metadata/purchases_by_padd/tbl_fuel_purchases_by_fuelpadd SUM QTY AS SUMGALL BY ENDOFWEEK -* WITH THIS AS A BY STATEMENT, THE FILTER IN PART TWO DOESN'T WORK. BY HIGHEST 10 TOTAL QTY NOPRINT BY STATION_NAME ON TABLE HOLD AS ONE END -*PART TWO TABLE FILE fuel_dept/metadata/purchases_by_padd/tbl_fuel_purchases_by_fuelpadd SUM QTY AS SUMGALL BY ENDOFWEEK BY STATION_NAME WHERE STATION_NAME IN FILE ONE -* THIS DOES NOT WORK WHEN ENDOFWEEK IS A 'BY' IN PART ONE Link to comment Share on other sites More sharing options...
Martin Yergeau Posted February 9, 2022 Share Posted February 9, 2022 When you use IN FILE, the values that are in that file must only be those to test (STATION_NAME) without any other fields and must not be a varchar. You should also HOLD the file as ON TABLE HOLD AS ONE FORMAT ALPHA Link to comment Share on other sites More sharing options...
Carter Henderson Posted February 9, 2022 Author Share Posted February 9, 2022 HI @MartinY Thanks for the response. Why do I need to change the format of the hold file to ALPHA and what does that do Why does DEFINE ENDOFWEEK cause problems as a BY FIELD for the filter I have used this technique before and had multiple fields in the hold file( see the code at the end). STATION_NAME is a VARCHAR and the condition WHERE STATION_NAME IN FILE ONE works when the DEFINE ENDOFWEEK is in the query not as BY ENDOFWEEK but as ENDOFWEEK. For instance, I have this example that works that has multiple fields. TABLE FILE fuel_dept/metadata/purchases_by_padd/tbl_fuel_purchases_by_fuelpadd SUM TOTALMILES/I10 TBL_FUEL_PURCHASES_BY_FUELPADD.TBL_FUEL_PURCHASES_BY_FUELPADD.QTY/I8 AS 'GALLONS' COMPUTE BOOKMPG/D10.2 = TOTALMILES/QTY; AS BOOKMPG -*BY TRANSACTION_DATE BY LOWEST COMPUTE BOOK_MPG/D10 = TOTALMILES/QTY; NOPRINT AS BOOK_MPG BY HIGHEST TOTAL TOTALMILES NOPRINT BY TBL_FUEL_PURCHASES_BY_FUELPADD.TBL_FUEL_PURCHASES_BY_FUELPADD.UNIT_NUM AS UNIT WHERE TRANSACTION_DATE GE DTADD(DT_CURRENT_DATE(),DAY,-14); WHERE QTY GT 0 WHERE_GROUPED SUM.BOOKMPG LE 4; WHERE_GROUPED SUM.TOTALMILES GE 1000 ON TABLE HOLD AS T1 END TABLE FILE fuel_dept/metadata/purchases_by_padd/tbl_fuel_purchases_by_fuelpadd SUM TRANSACTION_NUM QTY AMT TOTALMILES COMCHK_CARD BY TRANSACTION_DATE BY UNIT_NUM BY DRIVER_1 BY DRIVER_2 BY STATION_NAME BY STATION_CITY WHERE TRANSACTION_DATE GE DTADD(DT_CURRENT_DATE(),DAY,-14); WHERE QTY GT 0 WHERE UNIT_NUM IN FILE T1 ON TABLE PCHOLD FORMAT AHTML END Link to comment Share on other sites More sharing options...
Martin Yergeau Posted February 9, 2022 Share Posted February 9, 2022 Look at page 262 of this document regarding the Usage Notes for Reading Values From a File Creating Report Link to comment Share on other sites More sharing options...
Pierre CASTILLO 2 Posted February 10, 2022 Share Posted February 10, 2022 Another tricky answer ! -* HOLD failing HOLD ibisamp/CAR -RUN -* Any dummy query with HOLD TABLE FILE SYSTABLE PRINT NAME WHERE RECORDLIMIT EQ 1 WHERE READLIMIT EQ 1 ON TABLE HOLD END -RUN -* HOLD succeeding HOLD ibisamp/CAR -EXIT -*----------------------------------- -* OUTPUT RESULT AUCUN FICHIER HOLD ACTUELLEMENT ACTIF (INF32080) NOMBRE D ENREGISTREMENTS DANS LA TABLE= 1 LIGNES= 1 DEFINITION DU FICHIER HOLD: ibisamp/ FIELDNAME ALIAS FORMAT COUNTRY COUNTRY A10 CAR CARS A16 MODEL MODEL A24 BODYTYPE TYPE A12 SEATS SEAT I3 DEALER_COST DCOST D7 RETAIL_COST RCOST D7 SALES UNITS I6 LENGTH LEN D5 WIDTH WIDTH D5 HEIGHT HEIGHT D5 WEIGHT WEIGHT D6 WHEELBASE BASE D6.1 FUEL_CAP FUEL D6.1 BHP POWER D6 RPM RPM I5 MPG MILES D6 ACCEL SECONDS D6 WARRANTY WARR A40 STANDARD EQUIP A40 Link to comment Share on other sites More sharing options...
Martin Yergeau Posted February 10, 2022 Share Posted February 10, 2022 You need a name to refer to when using HOLD Doing the below code result in the following TABLE FILE SYSTABLE PRINT NAME WHERE RECORDLIMIT EQ 1 WHERE READLIMIT EQ 1 ON TABLE HOLD AS TST END -RUN HOLD TST HOLD ibisamp/CAR The above ibisamp/CAR is not a HOLDed file; its a file that already exist somewhere So the command cannot return a result. But the below return a result FF CAR Link to comment Share on other sites More sharing options...
Pierre CASTILLO 2 Posted February 10, 2022 Share Posted February 10, 2022 Hello MartinY, I ran my code with v8105M. And, as I displayed it, I got a result for HOLD ibisamp/car. The trick is to create a dummy HOLD just before ! Pierre 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