Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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?
Go
New
Search
Notify
Tools
Reply
  
[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, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2004Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2005Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport 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, 2005Reply With QuoteReport 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, 2007Reply With QuoteReport 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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport 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: 956 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.