Why cant I establish a filter on a hold file What are th...

[Carter Henderson]

Why cant I establish a filter on a hold file What are the possible causes

I have created a HOLD file called LASTWEEK_STATIONS.

I need to create a filter for the final page. However it seems that because this is a hold file, I need to create the filter differently. There is no filter on the table file that the hold is created from as shown here.

ENGINE INT CACHE SET ON

SET PAGE-NUM=NOLEAD

SET SQUEEZE=ON

-DEFAULTH &WF_HTMLENCODE=ON;

SET HTMLENCODE=&WF_HTMLENCODE

 

SET HTMLCSS=ON

-DEFAULTH &WF_EMPTYREPORT=ON;

SET EMPTYREPORT=&WF_EMPTYREPORT

SET ASNAMES = ON

 

SET EMBEDHEADING=ON

SET GRAPHDEFAULT=OFF

SET COMPONENT=TableChart_1

SET ARVERSION=2

 

 

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

BY STATION_NAME

BY STATION_CODE

BY STATION_STATE

-*RANKED AS GALLRANK BY HIGHEST TOTAL QTY NOPRINT

WHERE DTRUNC(TRANSACTION_DATE,WEEK_END) GE DTRUNC( DTADD(DT_CURRENT_DATE(),DAY,-91),WEEK_END)AND DTRUNC(TRANSACTION_DATE,WEEK_END) LE DTRUNC( DTADD(DT_CURRENT_DATE(),DAY,-14),WEEK_END)

ON TABLE SET BYDISPLAY ON

ON TABLE HOLD AS DAILYTOTALS

END

 

A few steps later I create

DEFINE FILE LASTWEEK_STATIONS

EXCEPTIONFLAG/A2 = IF SUMGALL GT MOST OR SUMGALL LT LEAST THEN 'Y' ELSE '-';

MORETHAN/D10 = IF SUMGALL - MOST GT 0 THEN SUMGALL - MOST ELSE 0;

LESSTHAN/D10 = IF LEAST - SUMGALL GT 0 THEN SUMGALL - LEAST ELSE 0;

END

 

-DEFAULT &EXCEPTIONFLAGFLAG = 'Y' ;

TABLE FILE LASTWEEK_STATIONS

PRINT

SUMGALL AS 'LAST WEEK,GALLONS'

EXCEPTIONFLAG AS 'EXCEPTION, FLAG'

MORETHAN

LESSTHAN

BY STATION_NAME AS 'STATION,NAME'

WHERE EXCEPTIONFLAG EQ &EXCEPTIONFLAG.(FIND EXCEPTIONFLAG IN LASTWEEK_STATIONS ).EXCEPTIONFLAG.;

 

The FIELD EXCEPTIONFLAG is clearly in LASTWEEK_STATIONS as shown below.

 

but i get this error.

Error occurred during Autoprompt processing

EXCEPTIONFLAG

NoDB : (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: LASTWEEK_STATIONS

[David Beagan]

Is LASTWEEK_STATIONS in a folder that is on the App Path I can reproduce the same error if I try to FIND a master file that is not on the App Path. (I dont think the error message is saying EXCEPTIONFLAG is not in LASTWEEK_STATIONS).

You could try putting the appfolder into the code:

&EXCEPTIONFLAG.(FIND EXCEPTIONFLAG IN appfolder/LASTWEEK_STATIONS )

Where appfolder is the folder containing LASTWEEK_STATIONS.

[Carter Henderson]

thanks David.

how would I know what the App Path is for this file

I honestly have no idea where a HOLD file gets stored ( didnt realize it was stored somewhere other than in the file) and if it is stored in the App Path I cant say. I will look into that.

[David Beagan]

Hold files are stored under edatemp which has a subfolder for each users agent running the fex. When the fex finishes running, your files in edatemp go away. You need to have your LASTWEEK_STATIONS stored somewhere more permanent before running your fex with the

&EXCEPTIONFLAG.(FIND EXCEPTIONFLAG IN LASTWEEK_STATIONS )

You can use APP HOLD to store your LASTWEEK_STATIONS in an application folder. For example,

APP HOLD baseapp

TABLE FILE CAR

PRINT MODEL LENGTH

ON TABLE HOLD AS cartest

END

 

Which would store the cartest Hold file in baseapp which is always on the App Path. However, all users would be sharing this file which may not be what you want. So, you can Hold to foccache instead of baseapp. The foccache is isolated for each user session, always on the App Path, and the files will exist there for about 3 hours, then get deleted.

You can learn about this in the manual: Developing Reporting Applications, page 33