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     [CLOSED] Determine MAX value within a DEFINE
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Determine MAX value within a DEFINE
 Login/Join
 
Member
posted
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
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Are you looking for BY HIGHEST 1 DAG_KEY?


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
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

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
  
TABLE FILE GWTBXXX
BY HIGHEST 1 DAG_KEY
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Expert
posted Hide Post
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.
   Most recent: 8204 Gen 48 in Test and Production.
   Currently Available. Please contact me.
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Member
posted Hide Post
To determine the information represent the right day.


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: April 13, 2016Reply With QuoteReport This Post
Guru
posted Hide Post
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.
 
Posts: 411 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Determine MAX value within a DEFINE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.