How come when I do the following query, I get the correct results, showing one record per loc for the highest effdt....

TABLE FILE LOCATION_TBL
PRINT BUILDING
BY LOCATION
BY HIGHEST 1 EFFDT_LOC
END

... but if I do a simple join to another table like below, I get the error (FOC002) A WORD IS NOT RECOGNIZED: 1

JOIN LOCATION IN LOCATION_TBL
TO LOCATION IN TGT_EMP_CURRENT
END
TABLE FILE LOCATION_TBL
PRINT BUILDING
BY LOCATION
BY HIGHEST 1 EFFDT_LOC
END

Even if I just say "BY HIGHEST" instead of "BY HIGHEST 1", I still get the same syntax error. What do I need to do differently just because I'm doing a join? Thanks, Brad

I think your problem is that the JOIN should
all be on one line, or the word JOIN on a line
by itself when it has an END, e.g.
JOIN x in y to xx in yy as one

or JOIN
x in j to xx in yy as one
end

(use Dev Studio to avoid these problems)

I think you just may be missing the AS ??? on your join statement.

I've tried all suggestions with the same results. However, if I substitute HIGHEST with TOP, the query works. It makes me wonder where else TOP and HIGHEST may differ, as they are claimed to be synonyms in documentation.

I hope that someone can try this at their own installation, (we're on 5.3.3, accessing UDB v8 tables on Unix) as I could see this causing some frustration, especially since Report Painter inserts HIGHEST for descending sort. By the way, I also get the error if I don't use a ranking number (BY HIGHEST fldname).

Thanks, Brad

Try adding the following SET statement and running the BY HIGHEST code again:
SET FIELDNAME=NOTRUNC

If that doesn't work, I'd be curious to see the SQL tracing for the BY HIGHEST code and the TOP code.

That works, but why is that needed for HIGHEST and not TOP? Would this be a consistent behavior that needs to communicated to all DevStudio users, or could it be a platform or query-specific thing? Thanks, Brad

I think as FOCUS parses the request it checks each word to see if it's an abbreviation of a field in the MFD. So when you do a "BY HIGHEST", FOCUS looks for a field name with an abbreviation of HIGHEST. If it finds one in the MFD it thinks that you are using HIGHEST twice within one BY phrase and throws the error. That might be why TOP works and HIGHEST does not.

Best guess anyway.

Codermonkey, your hunch is correct. We have a field called HIGHEST_EDUC_LVL - when I renamed it in the master file, the BY HIGHEST command worked without using NOTRUNC. Thanks for the info, it probably would have taken me forever to figure that out on my own.

My situation is similiar in that I too have a the join using "AS" and have put the "SET" option in but I still get "word is not recognized" when I use the Highest (or TOP) functions with a value: (ex: BY TOP 1 ...).

Where do I go from here?

Let's see your code.

This is the code and a sample of data to illustate my problem. I am trying to load the data into a hold file for further processing in the program.


SET FIELDNAME=NOTRUNC
TABLE FILE VIP_PO
PRINT PO_Nbr
NAME1
Status
WE_DATE
ADJ_N0
ACCT_NBR

BY PO_NBR
BY ACCT_NBR
BY WE_DATE
BY TOP 1 ADJ_NO

Po_nbr , name1, status, WE_DATE, ADJ_NO, ACCT_NBR
========= ======= == ======== == =======
BCDDD1234, COMP X, W, 03/15/06, 4, COXLBR01 *
BCDDD1234, COMP X, W, 03/15/06, 3, COXLBR01
BCDDD1234, COMP X, W, 03/15/06, 2, COXLBR01

BCEEE5555, COMP Z, W, 03/18/06, 3, COZLBR01 *
BCEEE5555, COMP Z, W, 03/18/06, 2, COZLBR01
BCEEE5555, COMP Z, W, 03/18/06, 1, COZLBR01

Please post the JOIN statement, that is where the problem might be.

Thanks.

Frances,
thanks for your patients.

JOIN
REMIT_NBR
IN PO
TO REMIT_NBR
IN SUPPLIER
AS J001
END

JOIN
PO_NBR
IN PO
TO PO_NBR
IN CLAIMS_HEADER
AS J002
END

JOIN
ACCT_NBR AND WE_DATE AND ADJ_NBR
IN PO
TO ACCT_NBR AND WE_DATE AND ADJ_NBR
IN LCLAIMS
AS J003
END

JOIN
ACCT_NBR
IN PO
TO ACCT_NBR
IN CLAIM_ACCTS
AS J004
END

If your code is as follows below, the TABLE FILE (VIP_PO) is not being done on the JOIN Host file (PO).

JOIN
REMIT_NBR IN PO TO REMIT_NBR IN SUPPLIER AS J001
END

JOIN
PO_NBR IN PO TO PO_NBR IN CLAIMS_HEADER AS J002
END

JOIN
ACCT_NBR AND WE_DATE AND ADJ_NBR IN PO
TO ACCT_NBR AND WE_DATE AND ADJ_NBR IN LCLAIMS AS J003
END

JOIN
ACCT_NBR IN PO TO ACCT_NBR IN CLAIM_ACCTS AS J004
END

SET FIELDNAME=NOTRUNC
TABLE FILE VIP_PO
PRINT PO_Nbr
NAME1
Status
WE_DATE
ADJ_N0
ACCT_NBR

BY PO_NBR
BY ACCT_NBR
BY WE_DATE
BY TOP 1 ADJ_NO
END

The VIP_PO and the PO table are one and the same, a hold out from my editing of the code when previously responding to your request.

I did redo the code but the "word is not recognized" error is still being displayed.

The BY TOP 1 ADJ_NO is giving the error?

I would remove the TOP 1 and trace the SQL (assuming the tables are DBMS).

Add the following to the code:

-*-- Do not access the data - speeds up debugging
SET XRETRIEVAL=OFF

-*-- Activate tracing
SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-RUN

Hopefully the messages generated from the trace may help in suggesting what might be wrong when using the TOP command.

Also, you could try qualifying the columns with the table name as prefix (EG. PO.ADJ_NBR).

Of course, this is all moot if there is a bug with TOP and HIGHEST.

The problem turned out to be that there where too many BY statements before the HIGHEST 1 function was called. I had 4 BY statements before the highest function causing it not recognize the function, but displaying an incorrect message.

What I did was create a few DEFINE items for the sort key items:

df_Claim_Date/A8YYMD = CLAIM_WE_DATE;
df_Sort_Key/A69=PO_NUMBER|ACCOUNT_NUMBER|df_Claim_Date;

I then used the df_Sort_Key in the by statement along with the highest function:

BY df_Sort_Key NOPRINT
BY HIGHEST 1 ADJUSTMENT_NUMBER NOPRINT

This generated the transactions with only the most currently adjusted records and no others. A second consideration was the inclusion of the ALL verb in the JOIN statement to make sure that all the records in a 1 to many relationship where read.

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