I'm working on a report for fixed assets......(getting the data from banner).

I originally was joining 3 tables (FFRMASA, FFBMAST, FFRMASF). My problem is that when a change is made, it inserts another record (called change_seq_num......one OTAG can have numerous different amounts associated with it.....and for one of the OTAGs, the amount has 9 different sequences) in the FFRMASA table which gives me MANY duplicates on the report. (A co-worker said to try using a MATCH, but I never have used matches before). So far I have this code:

TABLE FILE FFBMAST
SUM FFBMAST_USER_ID
BY FFBMAST_OTAG_CODE
BY FFBMAST_PTAG_CODE
ON TABLE HOLD AS JT1
END
MATCH FILE JT1
PRINT FFBMAST_USER_ID
BY FFBMAST_OTAG_CODE
BY FFBMAST_PTAG_CODE
RUN
FILE FFBMAST
PRINT
FFBMAST_POHD_CODE
FFBMAST_ASSET_DESC
FFBMAST_SERIAL_NUM_VIN
FFBMAST_DEPR_START_DATE
BY FFBMAST_OTAG_CODE
BY FFBMAST_PTAG_CODE
AFTER MATCH HOLD AS J1 OLD
END
TABLE FILE J1
PRINT
FFBMAST_OTAG_CODE AS 'Otag Code'
FFBMAST_PTAG_CODE AS 'Ptag Code'
FFBMAST_POHD_CODE AS 'PO #'
FFBMAST_ASSET_DESC AS 'Asset Desc.'
FFBMAST_SERIAL_NUM_VIN AS 'Serial No.'
FFBMAST_DEPR_START_DATE AS 'Depr. Start Date'
HEADING
"JT TESTING"
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *

I don't know where to go from here.........I need these fields from FFRMASA:

FFRMASA_ACCT_CODE_ASSET
FFRMASA_ACCT_CODE_DEPR
FFRMASA_AMT
FFRMASA_ADJ_AMT
FFRMASA_DEPR_ACCUM_AMT

Hopefully someone can help me out....I'm still learning!

Thanks!
Janet
Ferris State University

Jan, try SUM, rather than PRINT.
you'll need it on the second half of a match in order to get the elements to line up;
if you can use it on the first half of your match, so much the better.
MATCH FILE FFBMAST
SUM
FFBMAST_USER_ID
BY FFBMAST_OTAG_CODE
BY FFBMAST_PTAG_CODE
RUN
FILE FFBMAST
SUM
FFBMAST_POHD_CODE FFBMAST_ASSET_DESC FFBMAST_SERIAL_NUM_VIN FFBMAST_DEPR_START_DATE
BY FFBMAST_OTAG_CODE
BY FFBMAST_PTAG_CODE
AFTER MATCH HOLD AS J1 OLD
END

I talked with someone from IBI yesterday....he said to use the join instead of match. He tried using "HIGHEST 1" to get the last sequence number and was getting an error (due to having NULLS in the data). He concluded that we need to upgrade our version of WebFocus. So, that's where we stand now.

fs, you need to update your profile on this board so we can see what version you're on.
JOIN may or may not be an alternative answer for you, depending on your data granularity, and there's no indication from your example that highest 1 would have any relevance.
but in any event, you can just fix your MATCH syntax and be good to go, today.

We're using Dev. Studio 7.1