Dear Intelligencia,

I am a WebFocus newbie (as I'm sure you'll be able to tell by my post!) and I am having trouble updating a WebFocus PDF report.

I added a small bit of code to an existing JOIN that would result in retrieving 2 rows into a HOLD table (I'll call twoRowHoldTbl) instead of just 1 row. I've confirmed that the result is twoRowHoldTbl with 2 rows (as I expected). Then when I ran it, only the first row was being processed.

So, further down in the code, where all previous HOLD tables were being joined into one HOLD table for PRINT, I changed the JOIN statement for twoRowHoldTbl to include the term 'ALL' as follows:
"JOIN colName IN prtTable TO ALL twoRowHoldTbl.colName IN twoRowHoldTbl AS J1" and when I ran it this way I got the following error down on the BY statement for a twoRowHoldTbl column:
"(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH"

What the heck am I doing wrong?

Thanks very much in advance for your help!
Sam

It would help if you would post the join code. The error you are getting is because you are sorting on fields in two different paths in the join structure. Adding the 'all' to get all the data can do this to you. Have you added the new fields wanted to the sort with BY; do you need to do so if you did?

Thanks so much for responding, Leah! I'll do my best to answer the questions. First, the code...


Here's the JOIN code for the twoRowHoldTbl. I know this works because when I separated it out in its own fex & ran it, it worked.

JOIN
RESVCOMN.RSV_CMMN_ORG AND RESVCOMN.RSV_CMMN_RSV_NO IN RESVCOMN TO ALL
RESCHREL.RSVCHRREL_ORG AND RESCHREL.RSVCHRREL_RSV_NO IN RESCHREL AS J3
END
JOIN
RESCHREL.RSVCHRREL_ORG AND RESCHREL.RSVCHRREL_CHAR_COD IN RESVCOMN TO
RESCHAR.RSV_CHAR_ORG AND RESCHAR.RSV_CHAR_CODE IN RESCHAR AS J4
END
TABLE FILE RESVCOMN
SUM
RSV_CHAR_ORG
RSV_CHAR_DESC
RSVCHRREL_RSV_NO
BY
RSV_CHAR_CODE
WHERE ( RSV_CMMN_ORG EQ '104' );
WHERE ( RSV_CMMN_RSV_NO EQ &PARMRESNO );
ON TABLE HOLD AS P4_ALC
-*ON TABLE SET ONLINE-FMT HTML
END


Here's the JOIN code that is just before the sort.

JOIN P4CONF.RSV_CMMN_RSV_NO IN P4CONF TO ALL
P4_ALC.RSVCHRREL_RSV_NO IN P4_ALC AS J161
END


Here's the BY code where it is bombing:

PRINT
RSV_CMMN_RSV_NO NOPRINT
DUMMY AS ' '
RSCRSV_START_DATE/trwrMDYY AS ' '
FROMHOURS2 AS ' '
LBLTO AS ' '
TOHOURS2 AS ' '
BY
RSCTYPCD_DESC NOPRINT
BY
ASMH_AMOUNT NOPRINT
BY
RSV_CHAR_DESC NOPRINT (this is where the error occurs)
BY
RSV_CMMN_RSV_NO NOPRINT
BY
RSC_NAME NOPRINT
BY
RSCRSV_START_DATE NOPRINT
BY
RSCRSV_FNC_STRTIME NOPRINT
...
ON RSV_CHAR_DESC SUBHEAD
" "
" It is your responsibility to contact ... "
WHEN RSV_CHAR_DESC EQ 'Alcohol';
ON RSV_CHAR_DESC SUBHEAD
" "
" User is responsible ...
"to notify park staff. "
" "
" <+0>Release and Indemnity Form Due Date: DISPLAY_DATE"
WHEN RSV_CHAR_DESC EQ 'Liability Letter Required';



There is not a new field wanted in the BY statement, it's the same field, however, as a result of my change, it now has 2 values instead of just one.

Thanks again!!!
Sam

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

Try putting

JOIN CLEAR *

Just before the join for the report.

Also, do you create P4CONF as well?

Greetings Leah,

Thanks so much for helping me! A senior associate of mine came and sat with me yesteray and together we got this working. Here are the changes I made that finally worked:

1. Removed the 1st of the 2 JOIN statements just leaving the JOIN between the table with the Description Codes and the table with the Descriptions
2. Added a DEFINE FILE just below that JOIN where I created 2 FLAG variables and set their values based on the Descriptions read in.
3. Made the DEFINE FILE name and TABLE FILE name match the name of the table with the Description Codes & Dates I needed to reference later.
4. Left the ALL off of the later JOIN to P4CONF (as you recommended!)
5. Referenced the FLAG variables in two separate BY statements
6. Referenced the FLAG variables again in the ON and WHEN statements.

It worked!

Thanks again for responding with your helpful comments!!

Sincerely,
Sam

Sam,
Glad to hear it is working for you. I find I learn something new almost every day hear on the forum.

Did any one else get the original post of this again today? It is dated the 28th Of January and showed up in my mail again today.

Hi Leah and all,

FYI, this topic was moved from Rules and Regulations to this one, so you may receive a notification email of "New Topic".

Cheers,
Kerry