Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to get Max of column (database) ?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to get Max of column (database) ?
 Login/Join
 
Anish
Member
posted
In Define field, how to get max value of a column (database Field)

I have seen the function MAX, but it can just retrieve Max from two values


Can anyone help me out.


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
You can retrieve the MAX from a data base on selection.

TABLE FILE XYZ
SUM MAX.FIELDNAME
....
END


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
mgrackin
Virtuoso
posted Hide Post
(Tony, i'll answer this one. Wink)

Anish,

The MAX. function is designed to be used on a verb object in a SUM statement such as:

TABLE FILE CAR
SUM MAX.RCOST
END

If you really need to find the MAX in a DEFINE field try this:

DEFINE FILE CAR
MAXVALUE/D12=IF (RCOST GT LAST RCOST) THEN RCOST ELSE LAST RCOST;
END

TABLE FILE CAR
SUM LST.MAXVALUE
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Anish
Member
posted Hide Post
Hi Mickey

Actually below query i want to add a column to a report,
is that possible of joining Sqlout with a report?
Or with define field?


select b_levl_code
from b
where b_term_code_eff =
(select max(a_term_code_eff)
from b a
where a_id = b_id)
;
Please help me out.


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
mgrackin
Virtuoso
posted Hide Post
Anish,

Look at the following as an example:

TABLE FILE CAR
SUM MAX.RCOST
PRINT RCOST DCOST
BY COUNTRY
BY CAR
WHERE TOTAL RCOST EQ MAX.RCOST
END

TABLE FILE CAR
PRINT RCOST DCOST
BY COUNTRY
BY CAR
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Anish
Member
posted Hide Post
Hi Mickey

Thanks

TABLE FILE sgbstdn
SUM MAX.sgbstdn_term_code_eff
BY sgbstdn_pidm
WHERE TOTAL sgbstdn_term_code_eff EQ MAX.sgbstdn_term_code_eff
END

THE FIELDNAME IS NOT RECOGNIZED: MAX.sgbstdn_term_code_eff

Can you give me solutions
Thanks
Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Anish
Member
posted Hide Post
sorry Mickey

Mistakely i clicked postnow

Actually the above code i am using and i am getting field name is not recognized.

thanqs


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
Since you are already pulling the max value, I don't know as you even need the WHERE TOTAL line. Did you run once without it?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
mgrackin
Virtuoso
posted Hide Post
Three suggestions:

1) Check your master file description to make sure your field names are correct.

2) Put file names and fieldnames in ALL CAPS.

3) you need to add some code to your request. Look at the example again I posted using the CAR file and make sure you have a SUM and a PRINT statement.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Anish
Member
posted Hide Post
hi Leah

Yes, i executed without that line but still i am getting the same error

Thanqs

Anish


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
As Mickey said then, check spelling and case for your MFD.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Anish
Member
posted Hide Post
Thanqs very much

That worked after changing to Upper case.

Now i will try to join this column in actual report


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Anish
Member
posted Hide Post
Hi Leah or Mickey

1. i am trying to attach this column to existing report. Is there any chance of changing the format of field which is BY

bcoz, as my BY field is A9, i want to start my second column from 19 so i want to change A9 to A18.......

2. And also Please can you give some suggestion, after generating this report i want to export to Excel, where there are not maintaing these spaces(like A18) sequeezing

Can You please help me out

Thanks


WebFOCUS 762 and 761
Windows
Output: Excel
 
Posts: 24 | Registered: October 02, 2007Report This Post
Leah
Virtuoso
posted Hide Post
You can't change the format of the BY field on the BY statement, however you could add a compute

COMPUTE NEWFIELD/A18 = byfieldname;

Of course if you are going to EXECL and using excel as the output format the width of the alpha field is pretty much 'ignored' by excel.

ON TABLE SET ONLINE-FMT EXL2K

OR

ON TABLE PCHOLD FORMAT EXL2K

Do you have documentation available?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
mgrackin
Virtuoso
posted Hide Post
Anish,

If the field you are getting the MAX for is already in your existing request, just add the SUM MAX.FIELDNAME and the WHERE TOTAL statements into the existing request.

Take another look at the CAR FILE example I posted to see that the MAX value of RCOST is used to select only records from the CAR file that have the MAX RCOST. This sounds like what you are trying to do.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to get Max of column (database) ?

Copyright © 1996-2020 Information Builders