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.

You can retrieve the MAX from a data base on selection.

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

(Tony, i'll answer this one. )

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

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.

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

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

sorry Mickey

Mistakely i clicked postnow

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

thanqs

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?

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.

hi Leah

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

Thanqs

Anish

As Mickey said then, check spelling and case for your MFD.

Thanqs very much

That worked after changing to Upper case.

Now i will try to join this column in actual report

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

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?

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.