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,

Are you looking for BY HIGHEST 1 DAG_KEY?

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

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).

  
TABLE FILE GWTBXXX
BY HIGHEST 1 DAG_KEY
END

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… :-(

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.

What did you want to do with the DAG_KEY ?

Unfortunaltely, it doesn't work.

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

To determine the information represent the right day.

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.

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

You can use a masterfile profile and do the TABLE FILE there and use a global (&&) parameter