Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Carter Henderson

Recommended Posts

@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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...