Focal Point
[CLOSED] Determine MAX value within a DEFINE

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9347036196

September 10, 2019, 11:17 AM
Michel-V
[CLOSED] Determine MAX value within a DEFINE
Is it possible to determine the max/highest value within a column ?


To determine max value normally we use:

TABLE FILE GWTBXXX
SUM
MAX.DAG_KEY
DST.DAG_KEY NOPRINT
ON TABLE HOLD AS HLDXXX FORMAT ALPHA
END
-RUN

To make use of a conditional join is not possible because the matrix is still missing.

We need max/highest value for selection within a Business View

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
September 10, 2019, 12:26 PM
BabakNYC
Are you looking for BY HIGHEST 1 DAG_KEY?


WebFOCUS 8206, Unix, Windows
September 10, 2019, 04:10 PM
Waz
Assuming this request will go against a RDBMS, you could possibly use the function DB_EXPR.

DB_EXPR: Inserting an SQL Expression Into a Request


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

September 11, 2019, 09:21 AM
Michel-V
Hi Was and BabakNCY,
By using masters we have acces to DB2-tables.

I've tried several options by using DB_EXPR, such as :

DEFINE FILE GWTBXXX
DAG_KEY/I11 WITH ID = DB_EXPR( SELECT MAX(DAG_KEY) FROM GWEGDBA.GWTBXXX ) ;
END

or

DEFINE FILE GWTBXXX
DAG_KEY/I11 WITH ID = DB_EXPR(SUM CNT.MAX(DAG_KEY) BY MAX.DAG_KEY AS DAG_KEY ;
END

and some more possibilities, but all results contains all possible dag_key's.

Till now I don't have 1 single DAG_KEY as output from the querie(s).


WebFOCUS 8
Windows, All Outputs
September 11, 2019, 09:58 AM
BabakNYC
  
TABLE FILE GWTBXXX
BY HIGHEST 1 DAG_KEY
END



WebFOCUS 8206, Unix, Windows
September 11, 2019, 10:12 AM
Michel-V
Using TABLE FILE in a BusinessView is not possible.
I do need a DEFINE FILE only.

SQL SQLDBM

SELECT MAX(DAG_KEY) AS DAG_KEY FROM GWEGDBA.GWTB263
;

-* Query below required to see the output

TABLE FILE SQLOUT
PRINT
DAG_KEY
ON TABLE HOLD AS MICHEL FORMAT ALPHA
END

TABLE FILE MICHEL
BY
DAG_KEY
ON TABLE PCHOLD AS TEMPHLD FORMAT HTML
END
-RUN
;
-EXIT

Unfortunately using:
DAG_KEY/I11 WITH ID = DB_EXPR( SELECT MAX(DAG_KEY) AS DAG_KEY FROM GWEGDBA.GWTB263 ) ;

give me not the result I was hoping for… :-(


WebFOCUS 8
Windows, All Outputs
September 11, 2019, 11:19 AM
Doug
You could use the MIN and MAX functions based on 'THIS' and 'LAST' records for a given field. I've done this and it works fine.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Currently with Fidelity Investments
September 11, 2019, 04:54 PM
Waz
What did you want to do with the DAG_KEY ?


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

September 12, 2019, 05:09 AM
Michel-V
Unfortunaltely, it doesn't work.

I thinks that a specific setting is required for using DB_EXPR on the server.


WebFOCUS 8
Windows, All Outputs
September 12, 2019, 05:10 AM
Michel-V
To determine the information represent the right day.


WebFOCUS 8
Windows, All Outputs
September 12, 2019, 08:02 AM
Frans
Why not first retrieve DAY_KEY and read it into a param:

 
SQL SQLDBM

SELECT MAX(DAG_KEY) AS DAG_KEY FROM GWEGDBA.GWTB263
;

-* Query below required to see the output

TABLE FILE SQLOUT
PRINT
DAG_KEY
ON TABLE HOLD AS MICHEL FORMAT TAB
END
-RUN
-READFILE MICHEL 
-TYPE &DAG_KEY
 


After that you have some other TABLE request with join and you just use ... EQ &DAG_KEY instead of using a field.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
September 12, 2019, 10:15 AM
Michel-V
Hi Frans,
There is no possibility to use TABLE FILE within a BusinessView.
A DEFINE is the only way.

I'm a step further:
DEFINE FILE GWTB263
MAX_DAG_KEY/I11 WITH DAG_KEY = DB_EXPR(SELECT MAX(DAG_KEY) FROM GWEGDBA.GWTB263 ) ;
END


WebFOCUS 8
Windows, All Outputs
September 13, 2019, 01:38 AM
Frans
You can use a masterfile profile and do the TABLE FILE there and use a global (&&) parameter


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.