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     Join with descending sort - syntax error

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Join with descending sort - syntax error
 Login/Join
 
Gold member
posted
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
 
Posts: 59 | Location: Minneapolis | Registered: September 01, 2004Report This Post
Platinum Member
posted Hide Post
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, 2003Report This Post
Master
posted Hide Post
I think you just may be missing the AS ??? on your join statement.
 
Posts: 865 | Registered: May 24, 2004Report This Post
Gold member
posted Hide Post
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, 2004Report This Post
Platinum Member
posted Hide Post
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, 2005Report This Post
Gold member
posted Hide Post
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, 2004Report This Post
Platinum Member
posted Hide Post
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, 2005Report This Post
Gold member
posted Hide Post
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, 2004Report This Post
Member
posted Hide Post
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? Confused
 
Posts: 25 | Registered: March 15, 2006Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Member
posted Hide Post
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, 2006Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Member
posted Hide Post
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, 2006Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Member
posted Hide Post
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, 2006Report This Post
Expert
posted Hide Post
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, 2005Report This Post
Member
posted Hide Post
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, 2006Report 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     Join with descending sort - syntax error

Copyright © 1996-2020 Information Builders