I am having a problem using the HIGHEST function using the table below to return the single highest date, that is less than today's date. The function is being used in the first of 3 hold files. The results returns the lowest date record, 2005/08/20.

RATE TABLE
===========

ACCOUNT_NBR EFFECTIVE_DATE REG_RATE
SWCGOVT08 2007/09/29 135.21
SWCGOVT08 2006/09/30 130.01 <- record I want to display
SWCGOVT08 2005/10/01 125.01
SWCGOVT08 2005/09/03 120.20
SWCGOVT08 2005/08/20 120.20


The codes is:
==============
DEFINE FILE HLD_FILE1
df_Today/YYMD = &YYMD;

END
-* *********************************
-*
TABLE FILE HLD_FILE1
PRINT HLD_FILE1.SUPPLIER_ID
HLD_FILE1.df_ACTIVE
HLD_FILE1.ACCOUNT_NUMBER
RATES.RATES.EFFECTIVE_DATE/MDY
RATES.RATES.REG_RATE/P12.2M AS 'REG RATE'
RATES.RATES.OT_RATE/P12.2M AS 'OT RATE'

BY HLD_FILE1.PO_NUMBER NOPRINT
BY HLD_FILE1.ACCOUNT_NUMBER NOPRINT
BY HIGHEST 1 RATES.RATES.EFFECTIVE_DATE NOPRINT
-*
WHERE RATES.RATES.EFFECTIVE_DATE LT df_Today;
-*
ON TABLE HOLD AS HLD_FILE2

This message has been edited. Last edited by: Guillo,

BY HIGHEST doesn't bring back the highest it just sorts the data in descending order. You might want to look into the MAX operator.

What is the value of the PO_NUMBER across the 5 fields?

quote:

BY HLD_FILE1.PO_NUMBER NOPRINT
BY HLD_FILE1.ACCOUNT_NUMBER NOPRINT
BY HIGHEST 1 VIP_RATES.VIP_RATES.EFFECTIVE_DATE NOPRINT
-

the coding is in fact correct IMHO, but the sequence of the by statements is wrong.

The BY HIGHEST 1 ... should be the first one

Thank you all for the quick responses.

Leah:
I'm not sure how to use the MAX function in this instance.

Albert:
The value of the PO number is the same for all the 10 records that should be returned, each with different effective dates.

Frank:
I tried placing the HIGHEST statement as the first and it returned on 1 of 10 records but the date was still not correct. I tried removeing the 2 other BY statements, results the same. I remove the 1 from the HIGHEST statment, results the same. The effective date that keeps coming back is the oldest record not the newest or the one that is LT today which is what I need.

quote:

Originally posted by Guillo:
Thank you all for the quick responses.

Leah:
I'm not sure how to use the MAX function in this instance.

Albert:
The value of the PO number is the same for all the 10 records that should be returned, each with different effective dates.

Frank:
I tried placing the HIGHEST statement as the first and it returned on 1 of 10 records but the date was still not correct. I tried removeing the 2 other BY statements, results the same. I remove the 1 from the HIGHEST statment, results the same. The effective date that keeps coming back is the oldest record not the newest or the one that is LT today which is what I need.

The HOLD file contains 10 records for different accounts_nbrs. Each account_nbr may have multiple rates in the rate table of which I need the latest/current rate where the effective_date is less than today.

With this input data:

ACCT_NBR EFF_DATE RATE
SWCGOVT08 2007/09/29 135.21
SWCGOVT08 2006/09/30 130.01 <- record I want to display
SWCGOVT08 2005/10/01 125.01
SWCGOVT08 2005/09/03 120.20
SWCGOVT08 2005/08/20 120.20
SWCGOVT04 2007/09/29 135.21
SWCGOVT04 2005/10/01 125.01 <- record I want to display
SWCGOVT04 2005/09/03 120.20
SWCGOVT04 2005/08/20 120.20

Used this focexec:

TABLE FILE HIGHRATE
PRINT *
BY ACCOUNT_NBR NOPRINT
BY HIGHEST 1 EFFECTIVE_DATE NOPRINT
WHERE EFFECTIVE_DATE LT '2007/08/28'
END

Produced the following:

ACCOUNT_NBR EFFECTIVE_DATE REG_RATE FILLER
----------- -------------- -------- ------
SWCGOVT04 2005/10/01 125.01 <- record I want to display
SWCGOVT08 2006/09/30 130.01 <- record I want to display

Not sure where you're going wrong, sorry.

Ah, I've never used the highest x (where x is the number), has that been valid for all versions of WebFOCUS. Some times you just stick to the old comfortable ways.

I agree the sort needs to include the account number as the 'by' just before the 'by' for the date.

I ususally just do a code compute and where total to get the record.

In English not code correct here.

compute wanted/a1 if account number eq last acount number then 'n' else 'y';

where total wanted eq 'y'

As they say there is more than one way to skin a cat.

I took a look at the JOIN statement in the code for the HOLD file and found that the orignial statement did not have the MULITPLE verb in it. The orginal statement read:

JOIN
HLD_FILE1.ACCOUNT_NUMBER IN HLD_FILE1
TO RATES.RATES.ACCOUNT_NUMBER IN RATES AS J006
END

The new codes reads:

JOIN
HLD_FILE1.ACCOUNT_NUMBER IN HLD_FILE1
TO MULTIPLE RATES.RATES.ACCOUNT_NUMBER IN RATES AS J006
END

The MULTIPLE verb seems to make all the rates record to be read and have the where condition applied. This produced the results I was after.

Those joins, they'll kill you every time.

Leah

Sum max.value
by sortcode

works well but the highest can also give you the top x by sortcode.

Thanks Frank, I think I learn something new on this forum every day and I've been at it over 14 years now.