[solved]How to filter out max/min values in webfocus?

October 28, 2015, 01:57 PM

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,

October 28, 2015, 02:25 PM

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.

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

October 28, 2015, 02:36 PM

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

October 28, 2015, 02:37 PM

bug

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

October 28, 2015, 02:55 PM

bug

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.

October 28, 2015, 03:57 PM

Francis Mariani

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

October 28, 2015, 05:03 PM

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

Waz...

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

October 29, 2015, 03:06 PM

bug

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!

October 29, 2015, 06:53 PM

StuBouyer

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

October 29, 2015, 07:06 PM

Waz

You can always get more out of FOCUS code without the GUI.

Waz...

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