Join with descending sort - syntax error

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)

Posts: 226 | Registered: June 08, 2003

Ignored post by GCohen posted

TexasStingray

Master

posted

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

Posts: 865 | Registered: May 24, 2004

Ignored post by TexasStingray posted

InsideTGT

Gold member

posted

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

Posts: 59 | Location: Minneapolis | Registered: September 01, 2004

Ignored post by InsideTGT posted

codermonkey

Platinum Member

posted

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.

Posts: 118 | Location: DC | Registered: May 13, 2005

Ignored post by codermonkey posted

InsideTGT

Gold member

posted

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

Posts: 59 | Location: Minneapolis | Registered: September 01, 2004

Ignored post by InsideTGT posted

codermonkey

Platinum Member

posted

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.

Posts: 118 | Location: DC | Registered: May 13, 2005

Ignored post by codermonkey posted

InsideTGT

Gold member

posted

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.

Posts: 59 | Location: Minneapolis | Registered: September 01, 2004

Ignored post by InsideTGT posted

Member

posted

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?

Posts: 25 | Registered: March 15, 2006

Ignored post by Guillo posted

Expert

posted

Let's see your code.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

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

Posts: 25 | Registered: March 15, 2006

Ignored post by Guillo posted

Expert

posted

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

Thanks.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

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

Posts: 25 | Registered: March 15, 2006

Ignored post by Guillo posted

Expert

posted

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

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

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.

Posts: 25 | Registered: March 15, 2006

Ignored post by Guillo posted

Expert

posted

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.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Member

posted

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

Posts: 25 | Registered: March 15, 2006

Ignored post by Guillo posted