I'm trying to construct a webfocus procedure that I originally wrote in sql. Here's the sql code:
select distinct spriden_id id, spriden_last_name||','||spriden_first_name name,
bal, abs, totalamt, a.sgbstdn_styp_code type,sfbetrm_ar_ind IND
from spriden, nicole46, sgbstdn a, sfbetrm a
where spriden_pidm = pidm
and pidm = a.sgbstdn_pidm
and a.sfbetrm_pidm = pidm
and spriden_change_ind is null
and Sgbstdn_term_code_eff =
(select MAX(Sgbstdn_term_code_eff)
from SGBSTDN
where a.Sgbstdn_pidm = Sgbstdn_pidm)
and sfbetrm_term_code =
(select MAX(sfbetrm_term_code)
from sfbetrm
where a.sfbetrm_pidm = sfbetrm_pidm)
order by name;

Now....my problem is I don't know how to create the two MAX sub-queries. Here is my webfocus code:

JOIN
NICOLE46.NICOLE46.PIDM IN NICOLE46 TO UNIQUE SPRIDEN.SPRIDEN.SPRIDEN_PIDM
IN SPRIDEN AS J0
END
JOIN
NICOLE46.NICOLE46.PIDM IN NICOLE46 TO UNIQUE SFBETRM.SFBETRM.SFBETRM_PIDM
IN SFBETRM AS J1
END
JOIN
NICOLE46.NICOLE46.PIDM IN NICOLE46 TO UNIQUE SGBSTDN.SGBSTDN.SGBSTDN_PIDM
IN SGBSTDN AS J2
END
DEFINE FILE NICOLE46
Name/A40=SPRIDEN_LAST_NAME ||(', ' | SPRIDEN_FIRST_NAME) || (' ' | SPRIDEN_MI);
END
TABLE FILE NICOLE46
SUM
SPRIDEN_ID
Name
BAL
ABS
TOTALAMT
SFBETRM_AR_IND
SGBSTDN_STYP_CODE
BY Name NOPRINT
HEADING
""
FOOTING
""
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
WHERE SFBETRM_TERM_CODE EQ '200809';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *

I don't understand the order that the SGBSTDN_STYP_CODE field is pulling. Is it pulling the first record? I've used the MIN and MAX on the field but I keep getting the same data. Can anyone give any suggestion?

Change your SUM to PRINT And see how the records are being sorted.

TABLE FILE CAR
SUM MAX.RETAIL_COST
MAX.SEATS
BY COUNTRY
PRINT COUNTRY CAR MODEL RETAIL_COST DEALER_COST SEATS
WHERE TOTAL RETAIL_COST EQ MAX.RETAIL_COST;
WHERE TOTAL SEATS EQ MAX.SEATS;
END

TABLE FILE CAR
SUM MAX.MODEL
BY COUNTRY
PRINT COUNTRY CAR MODEL RETAIL_COST DEALER_COST SEATS
WHERE TOTAL MODEL EQ MAX.MODEL;
END

 
TABLE FILE SGBSTDN
SUM MAX.sfbetrm_term_code
BY Sgbstdn_pidm
ON TABLE HOLD AS HLDSTDN
END

TABLE FILE sfbetrm
SUM MAX.sfbetrm_term_code
BY sfbetrm_pidm
ON TABLE HOLD AS HLDBETRM
END