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

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

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

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

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.

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

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!

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

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

quote:

Originally posted by Waz:
You can always get more out of FOCUS code without the GUI.