Hi,

I have searched Focalpoint but I could not find an anwser so now I am asking for help.

In table BB_COURSE_MAIN I have a column called BATCH_UID defined as A64V with a value like
'201010-12345'. I then create two virtual columns off this column as BB_TERM defined A6V with a value like '201010' and BB_CRN defined A5V with a value like '12345'.

I then use these two new virtual columns to join with a table called SSBSECT. The column SSBSECT_TERM_CODE is defined as A6V with a value like '201010' and column SSBSECT_CRN defined as A5V with a value like '12345'.

Below is my join statement using Dev Studio:

JOIN
BB_COURSE_MAIN.BB_COURSE_MAIN.BB_TERM AND BB_COURSE_MAIN.BB_COURSE_MAIN.BB_CRN
WITH BB_TERM AND BB_CRN IN BB_COURSE_MAIN TO UNIQUE SSBSECT.SSBSECT.SSBSECT_TERM_CODE
AND SSBSECT.SSBSECT.SSBSECT_CRN IN SSBSECT TAG J2 AS J2
END

When I save, I get the following error message - 'Error Parsing MASTER File BB_COURSE_MAIN (0)'

Is there any way (or other ways) for me to join these two tables?

Thank you.

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

The WITH clause is invalid - specify only one WITH column in the JOIN statement.

I *think* an alternative is to use the WITH clause in the two virtual field DEFINE statements and then NOT use the WITH in the JOIN.

Someone will correct me if I am wrong...

From reading the documentation, it appears you require the WITH (with only one column):

JOIN
BB_COURSE_MAIN.BB_COURSE_MAIN.BB_TERM AND BB_COURSE_MAIN.BB_COURSE_MAIN.BB_CRN
WITH BB_COURSE_MAIN.BB_COURSE_MAIN.BATCH_UID IN BB_COURSE_MAIN TO 
UNIQUE SSBSECT.SSBSECT.SSBSECT_TERM_CODE 
AND SSBSECT.SSBSECT.SSBSECT_CRN IN SSBSECT TAG J2 AS J2
END

The problem with joining variable length alpha strings is that what you see as the value is not the actual value. The first part of a variable length string actually contains the length of the string, followed by the value of the string. So for example, your A64V field BATCH_UID has a value 012201010-12345. So when you DEFINE fields from that, you need to be aware that the first few characters are not part of your actual string value, and also that the new variable length fields you're creating also have those additional characters which may or may not JOIN correctly to the values in your cross-reference table.

I try to completely avoid using variable length alpha fields when doing joins - they just cause more problems than they're worth.

Darin, in my DB2 environment, I added

SQL DB2 SET VARCHAR OFF

to edasprof.prf. When I generate metadata, no variable length columns are generated. Does this solve the problem with JOIN's on variable length columns?

Yes. That gets rids of those AnV formats in the WF metadata, but doesn't seem to cause any negative side effects for the RDBMS.

Darin,

Thanks for confirmation. Let's hope kingfish1 gets some insight to his problem from the postings here.

Francis.

Francis and Darin,

I am using Oracle so I will look into my edasprof.prf to turn the varchar off. The reason I use AnV now is because without the V, the column connections don't work so I include it. I will get back here with my findings.

Thanks.

Hi,

I put the line SQL SQLORA SET VARCHAR OFF in my edasprof.prf file and re-create the metadata. Sure enough the AnV is gone leaving the BATCH_UID defined as A64. When I join the tables it does not have the WITH anymore but I still get the Parsing error.

JOIN
BB_COURSE_MAIN.BB_COURSE_MAIN.BB_TERM AND BB_COURSE_MAIN.BB_COURSE_MAIN.BB_CRN
IN BB_COURSE_MAIN TO UNIQUE SSBSECT.SSBSECT.SSBSECT_TERM_CODE
AND SSBSECT.SSBSECT.SSBSECT_CRN IN SSBSECT TAG J2 AS J2
END

Do you have any other ideas?

Thanks.

if you try to write a request against each of these tables individually do you get any parsing errors? Do you have any additional DEFINEs in a DEFINE statement or in the Master File? Other than that, we would probably need to see the Master file for each table.

No, I don't get any parsing error if I write a request against the tables using other fields. I only get the parsing error when I try to use the two define virtual fields below:
BB_TERM A6 EDIT( BATCH_UID, '999999$$$$$$' ) and
BB_CRN A5 EDIT( BATCH_UID, '$$$$$$$99999' )
in the BB_COURSE_MAIN master file

In the SSBSECT table:
SSBSECT_TERM_CODE defined as A6
SSBSECT_CRN defined as A5

I am thinking it has something to do with the virtual columns.

Thanks.

Please post the Masters and the DEFINE and maybe the ACX files.

BB_COURSE_MAIN Table:

FILENAME=bb_course_main, SUFFIX=SQLORA , $
SEGMENT=BB_COURSE_MAIN, SEGTYPE=S0, $
FIELDNAME=BATCH_UID, ALIAS=BATCH_UID, USAGE=A64, ACTUAL=A64, MISSING=ON, $
DEFINE BB_TERM/A6 WITH PK1=EDIT ( BATCH_UID,'999999$$$$$$'); $
DEFINE BB_CRN/A5 WITH PK1=EDIT ( BATCH_UID,'$$$$$$$99999'); $

ACX:
SEGNAME=BB_COURSE_MAIN, TABLENAME=SJCD.BB_COURSE_MAIN, CONNECTION=DEVL,
GUI_PARAMS='0,100,24,148,72,278,160', KEYS=0, $


SSBSECT Table:

FILENAME=SSBSECT, SUFFIX=SQLORA ,
REMARKS='Section General Information Base Table', $
SEGMENT=SSBSECT, SEGTYPE=S0, $
FIELDNAME=SSBSECT_TERM_CODE, ALIAS=SSBSECT_TERM_CODE, USAGE=A6, ACTUAL=A6,
DESCRIPTION='This field is not displayed on the form (page 0). It defines the Term code for the course section information. It is derived from the Key Block Term.', $
FIELDNAME=SSBSECT_CRN, ALIAS=SSBSECT_CRN, USAGE=A5, ACTUAL=A5,
DESCRIPTION='This field is not displayed on the form (page 0). It will display the Course Reference Number (CRN) assigned to this course section when it was initially added.', $

ACX:
SEGNAME=SSBSECT, TABLENAME=SATURN.SSBSECT, CONNECTION=PROD, KEYS=2, $

Thanks.

No need for the WITH PK1 in the DEFINEs in the Master File. You are already referencing a real field (BATCH_UID) so that can be removed. However, if you are using those fields to join to SSBSECT you still have to have a WITH in the JOIN command because it is still a define-based JOIN.

IBI support said that Dev Studio version 7610 will fix the Parsing error. I am going to upgrade to 7610.

I learn a lot from this forum.

Thanks.

I wonder what the parsing problem is in versions prior to v7.6.10, and am slightly alarmed there is a problem.

I did see that error when using a newer DevStudio version against and older version of the WF server. Just a bug, methinks. Fixed in 7.6.10 as kingfish mentioned.