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>,

TexasStingray,
Thanks, that worked.

Alan

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

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

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

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, 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.

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.