Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] (FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] (FOC023) 'BY' FIELDS FROM PREVIOUS VERB ARE MISSING
 Login/Join
 
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, 2007Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2013Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 154 | Registered: March 29, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.