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     [solved]How to filter out max/min values in webfocus?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[solved]How to filter out max/min values in webfocus?
 Login/Join
 
Platinum Member
posted
In SQL I'd do:

SELECT * FROM CAR
WHERE DEALER_COST <> (SELECT MIN(DEALER_COST)
                     FROM CAR)


But in webfocus this does not work:

TABLE FILE CAR
PRINT *
WHERE DEALER_COST NE MIN.DEALER_COST
END

This message has been edited. Last edited by: bug,


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Expert
posted Hide Post
This is very useful for SQL tables, but also works for FOCUS databases or HOLD files. Use the SQL_SCRIPT hold format for the selection criteria:

TABLE FILE CAR
SUM MIN.DEALER_COST
ON TABLE HOLD AS HMINDC FORMAT SQL_SCRIPT
END

TABLE FILE CAR
PRINT *
WHERE NOT DB_INFILE (HMINDC, DEALER_COST, DEALER_COST);
END

The first table request sets up the selection criteria, which is used in the second table request. In the case of WebFOCUS databases or HOLD files, the actual MIN value is stored in the first hold file. For SQL tables, only SQL is stored and then used as a sub-select in the second table request.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
TABLE FILE CAR
SUM
MAX.DEALER_COST AS MAX_COST
MIN.DEALER_COST AS MIN_COST
ON TABLE SAVE
END
-RUN
-READ SAVE &MAX.A7. &MIN.A7.
TABLE FILE CAR
PRINT DEALER_COST
WHERE DEALER_COST NE &MAX OR &MIN
END


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
This is very useful for SQL tables, but also works for FOCUS databases or HOLD files. Use the SQL_SCRIPT hold format for the selection criteria:

TABLE FILE CAR
SUM MIN.DEALER_COST
ON TABLE HOLD AS HMINDC FORMAT SQL_SCRIPT
END

TABLE FILE CAR
PRINT *
WHERE NOT DB_INFILE (HMINDC, DEALER_COST, DEALER_COST);
END

The first table request sets up the selection criteria, which is used in the second table request. In the case of WebFOCUS databases or HOLD files, the actual MIN value is stored in the first hold file. For SQL tables, only SQL is stored and then used as a sub-select in the second table request.


Does SQL_SCRIPT work in version 8+ only?

I run it in 7.64 there is this error:

(FOC160) THE OPTION AFTER THE WORD 'FORMAT' IS INVALID: SQL_SCRIPT


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Spence:
TABLE FILE CAR
SUM
MAX.DEALER_COST AS MAX_COST
MIN.DEALER_COST AS MIN_COST
ON TABLE SAVE
END
-RUN
-READ SAVE &MAX.A7. &MIN.A7.
TABLE FILE CAR
PRINT DEALER_COST
WHERE DEALER_COST NE &MAX OR &MIN
END


This is what I am using now. Hope there is a better way that doesn't need to read from disk. For example, a datetime field may have trouble.


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Expert
posted Hide Post
Oops! Pardon me - yes, this is a WF 7.7.05 and newer feature. Sorry about that.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
Here is another way to do it in a single pass.

TABLE FILE CAR
SUM
COMPUTE MIN_DCOST/D7 = MIN.DEALER_COST ;
-*COMPUTE MAX_DCOST/D7 = MAX.DEALER_COST ;
PRINT *
WHERE TOTAL DEALER_COST NE MIN_DCOST
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Waz:
Here is another way to do it in a single pass.

TABLE FILE CAR
SUM
COMPUTE MIN_DCOST/D7 = MIN.DEALER_COST ;
-*COMPUTE MAX_DCOST/D7 = MAX.DEALER_COST ;
PRINT *
WHERE TOTAL DEALER_COST NE MIN_DCOST
END


Very nice! This is what I wanted. Thanks Waz!


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Guru
posted Hide Post
quote:
Originally posted by Waz:
Here is another way to do it in a single pass.

TABLE FILE CAR
SUM
COMPUTE MIN_DCOST/D7 = MIN.DEALER_COST ;
-*COMPUTE MAX_DCOST/D7 = MAX.DEALER_COST ;
PRINT *
WHERE TOTAL DEALER_COST NE MIN_DCOST
END


Multi-verb requests are becoming a bit of a lost art now that the GUI is hiding code from us


WebFOCUS 8.2.03 (8.2.06 in testing)
 
Posts: 253 | Location: Melbourne, Australia | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
You can always get more out of FOCUS code without the GUI.

Cool


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
quote:
Originally posted by Waz:
You can always get more out of FOCUS code without the GUI.

Cool


Amen to that!


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report 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     [solved]How to filter out max/min values in webfocus?

Copyright © 1996-2020 Information Builders