I'm using decode to aggregate some but not all codes in an initial query. Then, in the initial query I'm summing amounts across the decode field and saving the output to a hold file. Then I'm performing calculations on the columns in the hold file. A "where" clause is used to filter the records selected and sometimes a non-aggregated code does not exist in the original file for the "where" clause conditions. Consequently, the corresponding column does not exist in the hold file and the report blows up because the report is referring to a non-existant column.

When I'm tabling the hold file, how can I get around the error of a field not found.

****** SAMPLE CODE *****

-*
DEFINE FILE TBRACCD
DTL_CODE/A4=DECODE TBRACCD_DETAIL_CODE (
TF01 TF01
TF10 TF10
TF26 TF26
TF30 TF30
AEP INST
AEPM INST
ALIK INST
WHIT INST
STFD LOAN
STFU LOAN
PELL PELL
PERK PERK
SEOG SEOG ELSE OTHR);
END
TABLE FILE TBRACCD
SUM TBRACCD_AMOUNT
BY LAST_NAME
BY BANNER_ID
ACROSS DTL_CODE AS 'Detail Code'
WHERE STVTERM_CODE EQ '&TERM_CODE_YYYYMM'
AND DTL_CODE NE 'OTHR';
ON TABLE HOLD AS CASHBYCODE FORMAT FOCUS
END
-*
DEFINE FILE CASHBYCODE
AID_SMRY /P14.2 = TBRINST + TBRLOAN + TBRPELL + TBRPERK + TBRSEOG;
TOTAL_CHARGES /P14.2 = TBRTF01 + TBRTF10 + TBRTF26 + TBRTF30 ;
DATE/A10 = TODAY(DATE);
END


-****** END SAMPLE CODE ******

The decode is working fine. The report runs properly for fall semesters where there are cases of TBRACCD_DETAIL_CODE = TF26. But in winter semesters there are no cases where TBRACCD_DETAIL_CODE is TF26.

Consequently, the TOTAL_CHARGES calculation against the hold file blows up because the column TBRTF26 is never created.

Thanks for any and all suggestions.

Use ACROSS ... COLUMNS ...
as in:

DEFINE FILE CAR
  SSS/A8=DECODE SEATS(1 ONE 2 TWO 3 THREE 4 FOUR ELSE OTHR);
END
TABLE FILE CAR
 SUM SALES
 BY COUNTRY
 ACROSS SSS
  COLUMNS ONE AND TWO AND THREE AND FOUR
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
?FF HOLD

 FILENAME=  HOLD
 COUNTRY       E01           A10
 SALONE        E02           I6
 SALTWO        E03           I6
 SALTHREE      E04           I6
 SALFOUR       E05           I6

This message has been edited. Last edited by: j.gross,

Resolved. Thanks go to Jack Gross for his response.