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.

I changed the sum to Print and the data is stored with the most recent entry at the top. So I used a Max on the column but it didn't work then a Min and that didn't work. I've researched past postings that were a little similar where they suggested using HIGHEST 1. I've been playing around with that. I haven't gotten anything back yet, but I'll keep trying.

try first or last (FST. LST.)

If I understand your question properly, you're going to need to use a multi-verb request.

Something like this:

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

If you run this, you'll see that the query will give you only those records that have the highest retail cost AND the highest number of seats, by country. You would want to do something similar. You can comment out the where total lines one-by-one (or both) to see the difference in behavior.

The other option of course would be to create a couple of hold files that contain the max values and then join back to them. But I think you can avoid that.

Looks like you are using Banner.
What I have done is created a seperate routine that can then be copied or -INCLUDE into multiple fexes. Then JOIN the created file and pull the required fields from that subset of data.

TABLE FILE SGRADVR
PRINT *
WHERE SGRADVR_TERM_CODE_EFF LE '&TERMCODE'
BY SGRADVR_PIDM NOPRINT
BY HIGHEST 1 SGRADVR_TERM_CODE_EFF NOPRINT
ON TABLE HOLD AS SGRADVR_MAX_TERM FORMAT FOCUS INDEX SGRADVR_PIDM
END
JOIN PIDM_KEY IN AS_ADMISSIONS_APPLICANT TO SGRADVR_PIDM IN SGRADVR_MAX_TERM AS J6

I've tried the FST and LST, but they didn't work. I also tried the multi-verb request. I think that would have worked if the fields were numeric. So that brings me to your suggestion Rick Man. Yes I am using Banner, I am researching some info on -INCLUDE now. I will try your suggestion. I don't really understand but I'm hoping it will be a little clearer after I read. So stay tuned I may need your help pleeesse.

It's possible I don't understand your request or your data -- however, MAX doesn't care whether data is alpha or numeric.

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

quote:

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;

Why don't you break this down into separate requests. I can't test the code for you but it should be something like:

 
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

(You will need everything in CAPS) and then join to those files to the original.

Let's step back. What's your objective? Why not just leave it as a pass-through SQL request?

It's improbable that one can cook up a Focus request that will generate the equivalent SQL (with its two coordinated sub-selects). A HOLD and JOIN alternative to produce the same net result will be less efficient -- it deprives the SQL server of the opportunity to optimize, so potentially many rows irrelevant to the final result will have to be transmitted to and processed by Focus.

SOLVED (Thanks to everyone for all your help)

Hi Rick...I did finish the procedure on yesterday, but I was too tired to update. I had every intention of updating this morning but had a meeting. Here is what I ended up doing I'm sure there is probably an easier or shorter way to pull this info. I tried deleting all the styling code out of the sample. Let me know what you think.....any suggestions.

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
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
BY PIDM
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 HOLD AS CONFIRMHOLD FORMAT FOCUS INDEX 'PIDM'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
END
JOIN
NICOLE46.NICOLE46.PIDM IN NICOLE46 TO UNIQUE SGBSTDN.SGBSTDN.SGBSTDN_PIDM
IN SGBSTDN AS J2
END
TABLE FILE NICOLE46
PRINT
SGBSTDN_TERM_CODE_EFF
SGBSTDN_STYP_CODE
BY PIDM
BY HIGHEST 1 SGBSTDN_TERM_CODE_EFF NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TERMHOLD FORMAT FOCUS INDEX 'PIDM'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
END
JOIN
CONFIRMHOLD.SEG01.PIDM IN CONFIRMHOLD TO UNIQUE TERMHOLD.SEG01.PIDM IN TERMHOLD
AS J3
END
TABLE FILE CONFIRMHOLD
SUM
SPRIDEN_ID
Name
BAL
ABS
TOTALAMT
SGBSTDN_STYP_CODE AS 'Student,Type'
SFBETRM_AR_IND AS 'Confirm,IND'
BY Name NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(,,,,,.Select type of display output.
ON TABLE SET STYLE *
END

WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML