I'm having a problem with a procedure that gets a count of records into a hold table then joins to that hold table later to include the count. When I try running the procedure, the server crashes and I don't get anything.

Here is the code:
DEFINE FILE LNK_CRM_LEAD_CHILD
HLeadKey/I11=LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_LEAD_KEY;
END
TABLE FILE LNK_CRM_LEAD_CHILD
SUM
CNT.DST.LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_CHILD_KEY AS 'ChildCnt'
BY LOWEST LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HLeadKey
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HC3 FORMAT FOCUS INDEX 'LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HLeadKey'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-* MAIN
JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
SAT_CRM_LEAD.SAT_CRM_LEAD.HUB_CRM_LEAD_KEY IN SAT_CRM_LEAD TAG J0 AS J0
END
JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
HC3.SEG01.HLeadKey IN HC3 TAG J4 AS J4
END
TABLE FILE HUB_CRM_LEAD
PRINT
HUB_CRM_LEAD.HUB_CRM_LEAD.LEAD_ID
J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG
J0.SAT_CRM_LEAD.LAST_NAME
J0.SAT_CRM_LEAD.FIRST_NAME
-* childcnt below is the problem field, take it out and the report works
J4.SEG01.CHILDCNT
BY LOWEST HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY
WHERE ( J0.SAT_CRM_LEAD.DATE_ENTERED GE DT(2012-02-12 12:00:00AM) ) AND ( J0.SAT_CRM_LEAD.DATE_ENTERED LE DT(2012-02-13 12:00:00AM) );
WHERE J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG EQ 'Y';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

If I take the child count out of the PRINT statement, the report works. So there must be some problem with the index on the hold file. The normal name is too long, I get an error saying the segment name is too long, so I have used a define to get a shorter segment name for the index but that still doesn't work.

I would appreciate any help I can get.

Thanks,

Dave

This message has been edited. Last edited by: Kerry,

Hi Dave,

Can you try this way. Maybe this would work for you.

Here is the code:

TABLE FILE LNK_CRM_LEAD_CHILD
SUM
CNT.DST.LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_CHILD_KEY AS 'ChildCnt'
BY LOWEST LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_LEAD_KEY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HC3 FORMAT FOCUS INDEX HUB_CRM_LEAD_KEY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

-* MAIN

JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
SAT_CRM_LEAD.SAT_CRM_LEAD.HUB_CRM_LEAD_KEY IN SAT_CRM_LEAD TAG J0 AS J0
END

JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
HC3.SEG01.HUB_CRM_LEAD_KEY IN HC3 TAG J4 AS J4
END

TABLE FILE HUB_CRM_LEAD
PRINT
HUB_CRM_LEAD.HUB_CRM_LEAD.LEAD_ID
J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG
J0.SAT_CRM_LEAD.LAST_NAME
J0.SAT_CRM_LEAD.FIRST_NAME
-* childcnt below is the problem field, take it out and the report works
J4.SEG01.CHILDCNT
BY LOWEST HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY
WHERE ( J0.SAT_CRM_LEAD.DATE_ENTERED GE DT(2012-02-12 12:00:00AM) ) AND ( J0.SAT_CRM_LEAD.DATE_ENTERED LE DT(2012-02-13 12:00:00AM) );
WHERE J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG EQ 'Y';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Let me know if it works.

Regards,
Venkat

or other option is please change your define field name to Uppercase HLEADKEY/I11 and also in other places too.

When I use the natural name for the index, hub_crm_lead_key, I get this error:
(FOC36385) LONG SEGMENT/INDEX NAMES CAN NOT BE USED WITH SUFFIX=FOC

Have you tried the other option that I mentioned?

It just finished, I get the message about an unknow error:
Unknown error occurred. Agent on reporting server EDASERVE may have crashed or request was halted by the operator. Please investigate reporting server log.

I suggest you stop the music after the initial HOLD, and use ?FF, ? FILE and ? FDT to see what it created.

Removing the qualification in the INDEX clause may solve your problem: what gets indexed is the field stored in the HOLD file, not the incoming data column.

I'm not sure what you mean by removing the qualification in teh INDEX clause.

I did the ?FF etc. and here is what i see:
?FF
0 NUMBER OF RECORDS IN TABLE= 418136 LINES= 418136
FILENAME= HC3
HLEADKEY E01 I11
CHILDCNT E02 I5
HUB_CRM_CHILD_KEY E03 I11

? FILE:
0 NUMBER OF RECORDS IN TABLE= 418136 LINES= 418136
0 STATUS OF FOCUS FILE: D:\ibi\srv77\wfs\edatemp\ts000091\hc3.foc ON 03/22/2012 AT 16.56.20
0 ACTIVE DELETED DATE OF TIME OF LAST TRANS
SEGNAME COUNT COUNT LAST CHG LAST CHG NUMBER
SEG01 418136 03/22/2012 16.56.20 418136
*INDEXES*
HLEADKEY 03/22/2012 16.56.20 418136
0 TOTAL SEGS 418136
TOTAL CHAR 5017632
TOTAL PAGES 2672
LAST CHANGE 03/22/2012 16.56.20 418136

and ? FDT:
0 NUMBER OF RECORDS IN TABLE= 418136 LINES= 418136
0 DIRECTORY:\ibi\srv77\wfs\edatemp\ts000091\hc3.foc ON 03/22/2012 AT 16.57.29
DATE/TIME OF LAST CHANGE: 03/22/2012 16.57.29
0 SEGNAME LENGTH PARENT START END PAGES LINKS TYPE
1 SEG01 4 1 2672 1687 1
HLEADKEY 2 2671 985 NEW

Thanks in advance for any help, I've tried everything I can think of. I can PRINT the hold file after it is held but i can't join to it.

Dave

j.gross is correct that the file.segname qualification in your INDEX statement is misleading because your DEFINEd field, HLeadKey, is not actually part of that file or segment. As a DEFINE, it exists only in the WF internal matrix. The statement does work as coded, but it should be:

ON TABLE HOLD AS HC3 FORMAT FOCUS INDEX HLeadKey

More importantly, I think you may be confusing WF in your JOIN back to the HOLD file with use of the MULTIPLE key word. There should be only one occurrence of each key value in the FOCUS HOLD file, so the JOIN should be unique. Also, as Venkat suggests and the ? FF/FILE/FDT queries indicate, WF generally likes to use upper case (a hold-over from mainframe days). Try this:

JOIN
 HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO UNIQUE HC3.SEG01.HLEADKEY IN HC3 TAG J4 AS J4
END

This message has been edited. Last edited by: Dan Satchell,

Thanks Dan, I've tried using ASNAMES to get a shorter index name for the hold table and now I am able to join to it. However, the results are not correct and I don't understand how or why.

There are 109 records I get when I run the procedure without the child count column. When I include the child count, i get only 59 records and the parent names disappear, that is, they are blank.

Here is the code I am now using:
SET ASNAMES = ON
-* child counts
TABLE FILE LNK_CRM_LEAD_CHILD
SUM
CNT.DST.LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_CHILD_KEY AS 'CHILDCNT'
BY LOWEST LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_LEAD_KEY AS HKEY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HC3 FORMAT FOCUS INDEX HUB_CRM_LEAD_KEY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END
-* MAIN
JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
SAT_CRM_LEAD.SAT_CRM_LEAD.HUB_CRM_LEAD_KEY IN SAT_CRM_LEAD TAG J0 AS J0
END
JOIN
LEFT_OUTER HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO UNIQUE
HC3.SEG01.HKEY IN HC3 TAG J1 AS J1
END
TABLE FILE HUB_CRM_LEAD
PRINT
HUB_CRM_LEAD.HUB_CRM_LEAD.LEAD_ID
J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG
J0.SAT_CRM_LEAD.LAST_NAME
J0.SAT_CRM_LEAD.FIRST_NAME
J1.SEG01.CHILDCNT
BY LOWEST HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY
WHERE ( J0.SAT_CRM_LEAD.DATE_ENTERED GE DT(2012-02-12 12:00:00AM) )
AND ( J0.SAT_CRM_LEAD.DATE_ENTERED LE DT(2012-02-13 12:00:00AM) );
WHERE J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG EQ 'Y';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END

You could try adding SET ALL=ON to your code, but I don't think that is the issue. I question whether HKEY is actually being indexed. I would go back to using a DEFINE to shorten the field name for the INDEX statement.

In a FOCUS file, segment names are limited to 8 characters. This means that when you issue:

  
ON TABLE HOLD AS ABC FORMAT FOCUS INDEX SOMENAME

you should not use a fieldname longer than 8 characters.
So DEFINE a new name for the field you want to use as an INDEX.

This is what I think you should do with your code:

  
-* Use CAPITALS for field names which will be segment names
DEFINE FILE LNK_CRM_LEAD_CHILD
HLEADKEY/I11=LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_LEAD_KEY;
END
TABLE FILE LNK_CRM_LEAD_CHILD
SUM 
CNT.DST.LNK_CRM_LEAD_CHILD.LNK_CRM_LEAD_CHILD.HUB_CRM_CHILD_KEY AS 'CHILDCNT'
BY LOWEST HLEADKEY
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD AS HC3 FORMAT FOCUS INDEX HLEADKEY 
END
-* MAIN
JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO MULTIPLE
SAT_CRM_LEAD.SAT_CRM_LEAD.HUB_CRM_LEAD_KEY IN SAT_CRM_LEAD TAG J0 AS J0
END
-* JOIN TO, and not JOIN TO MULTIPLE
JOIN
HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY IN HUB_CRM_LEAD TO 
HLEADKEY IN HC3 TAG J4 AS J4
END
TABLE FILE HUB_CRM_LEAD
PRINT 
HUB_CRM_LEAD.HUB_CRM_LEAD.LEAD_ID
J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG
J0.SAT_CRM_LEAD.LAST_NAME
J0.SAT_CRM_LEAD.FIRST_NAME
-* childcnt below is the problem field, take it out and the report works
-* No need to use the segment name
J4.CHILDCNT
BY LOWEST HUB_CRM_LEAD.HUB_CRM_LEAD.HUB_CRM_LEAD_KEY
WHERE ( J0.SAT_CRM_LEAD.DATE_ENTERED GE DT(2012-02-12 12:00:00AM) ) AND ( J0.SAT_CRM_LEAD.DATE_ENTERED LE DT(2012-02-13 12:00:00AM) );
WHERE J0.SAT_CRM_LEAD.DSS_CURRENT_FLAG EQ 'Y';
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

This message has been edited. Last edited by: Danny-SRL,

I believe the limit on index field names is currently 12 characters. In any case, HUB_CRM_LEAD_KEY is definitely too long.

Dan,
I agree, definitely too long.

In wf77ddlang.pdf:
For a FOCUS data source, the segment name may consist of up to eight characters. Segment
names for an XFOCUS data source may have up to 64 characters