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     [CLOSED] (FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] (FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING
 Login/Join
 
Alan
Gold member
posted
I'm attempting to retrieve highest-row data for employees where there are multiple rows keyed on EMPLID/EFFDT/EFFSEQ. This is the complete fex so there are no other verbs.

Has anyone encountered this?


TABLE FILE EMPLOYMENT_HISTORY_VW
BY HIGHEST EMPLID
BY HIGHEST 1 EFFDT
BY HIGHEST 1 EFFSEQ
PRINT FIRST_NAME
LAST_NAME
SSN
SEX
BIRTHDATE
SERVICE_DT
CMPNY_SENIORITY_DT
HIRE_DT
JOB_TITLE
DIVISION
WORK_LOCATION_CD
UNION_CD
ACQUISITION_CMPNY_CD/A3
FULL_PART_TIME
REG_TEMP
COMPANY
EMPL_STATUS
EEO1CODE
JOBCODE
WHERE EFFDT LE DT('20071231 00:00:00')
ON TABLE HOLD AS HIGHROWS
END
-EXIT
(FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING

This message has been edited. Last edited by: <Kathryn Henning>,


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
Alan
Gold member
posted Hide Post
TexasStingray,
Thanks, that worked.

Alan


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
dballest
Platinum Member
posted Hide Post
Hi Alan,

I was trying to search for FOC023 and your post came up. Unfortunately, I don't see the resolution to your issue. We have a similar error and would appreciate if you can share the fix. Thanks, Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
eric.woerle
Master
posted Hide Post
Dballest,

TexasStringray's response appears to be missing, but I would assume the problem is the order print statement versus by statements.

I would make sure that your request is ordered in the structure of

  PRINT / SUM field1
field2 
field3
BY FIELD4
BY FIELD5


If you are doing a multi-verb, then you need to make sure that the BY fields from the first verb
are included in the second verb and in the same order.

  SUM field1
field2 
field3
BY FIELD4
SUM FIELD6
FIELD7
BY FIELD4
BY FIELD5


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
dballest
Platinum Member
posted Hide Post
Thanks for the reply Eric. Yes, the report has multi-verb and are ordered correctly. Here's the code:

TABLE FILE THEDATA

SUM AVE.NEWMINS NOPRINT
BY STATUS_PCT

SUM NEWMINS AS ''
BY STATUS_PCT ROWS 'Running%' OVER 'Idle%' OVER 'PM%' OVER 'Setup%' OVER 'Maint%' OVER 'Other%' OVER 'Stop%' OVER 'Offline%' OVER 'Down%'

ACROSS DAYFMT AS ''
COMPUTE AVERC/D7.2=C1; AS 'Ave'
END
-RUN

-EXIT

We've opened up a case with IBI. This report works in 7.6.2 but not in 8.0.08M. Still waiting for the resolution.

Thanks,
Dan


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
eric.woerle
Master
posted Hide Post
dballest,

you have a compute after your across statement, with no by fields after it.... Are you trying to do a row total? I noticed the C1 coding in there.... It's been a long long time since I've done any recomputes that way.... Are you sure thats correct for a multi-verb?


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Alan B
Virtuoso
posted Hide Post
Eric, You have just jogged a memory. I think if you use:
TABLE FILE THEDATA
SUM AVE.NEWMINS NOPRINT
BY STATUS_PCT

SUM NEWMINS AS ''
ACROSS DAYFMT AS ''
COMPUTE AVERC/D7.2=C1; AS 'Ave'
BY STATUS_PCT ROWS 'Running%' OVER 'Idle%' OVER 'PM%' OVER 'Setup%' OVER 'Maint%' OVER 'Other%' OVER 'Stop%' OVER 'Offline%' OVER 'Down%'
END
-RUN
it may work.

The COMPUTE is fine as a final column after the ACROSS, but I have seen where the order of ACROSS/BY becomes important, though not tried in multi verb requests.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Martin vK
Platinum Member
posted Hide Post
Hi Dan,

Would it work if you add the ROWS OVER on the first BY as well? 

TABLE FILE THEDATA

SUM AVE.NEWMINS NOPRINT
BY STATUS_PCT ROWS 'Running%' OVER 'Idle%' OVER 'PM%' OVER 'Setup%' OVER 'Maint%' OVER 'Other%' OVER 'Stop%' OVER 'Offline%' OVER 'Down%'

SUM NEWMINS AS ''
BY STATUS_PCT ROWS 'Running%' OVER 'Idle%' OVER 'PM%' OVER 'Setup%' OVER 'Maint%' OVER 'Other%' OVER 'Stop%' OVER 'Offline%' OVER 'Down%'

ACROSS DAYFMT AS ''
COMPUTE AVERC/D7.2=C1; AS 'Ave'
END
-RUN


Maybe there is some code tightening in the new version requiring that the BY's are exactly the same in the multi-verb.

Martin.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report 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     [CLOSED] (FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING

Copyright © 1996-2020 Information Builders