I have this code that produces the details of a hold file: ...

[Carter Henderson]

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

[David Beagan]

FF CAR

should do the trick.

[Charles Morris 2]

or do an ON TABLE SAVE - It should give you a similar output of all fields in the TABLE FILE

[Carter Henderson]

Thanks a bunch. This was impossible to search in the docs

[Carter Henderson]

@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

[Martin Yergeau]

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

[Carter Henderson]

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

[Martin Yergeau]

Look at page 262 of this document regarding the Usage Notes for Reading Values From a File

Creating Report

[Pierre CASTILLO 2]

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

[Martin Yergeau]

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

[Pierre CASTILLO 2]

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