Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     how to use a sub-query that selects the MAX record

Read-Only Read-Only Topic
Go
Search
Notify
Tools
how to use a sub-query that selects the MAX record
 Login/Join
 
Gold member
posted
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?


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
Change your SUM to PRINT And see how the records are being sorted.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
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.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Virtuoso
posted Hide Post
try first or last (FST. LST.)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
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.



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Platinum Member
posted Hide Post
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


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Gold member
posted Hide Post
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.


WebFOCUS 7.6.2, MS Windows Server/______, Excel, PDF, HTML
 
Posts: 91 | Registered: May 15, 2008Report This Post
Platinum Member
posted Hide Post
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



Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Master
posted Hide Post
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.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
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
 
Posts: 91 | Registered: May 15, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     how to use a sub-query that selects the MAX record

Copyright © 1996-2020 Information Builders