Using the Join assistant I created a multi-column join with a virtual (DEFINE'd) field on the left-hand table. However, on re-opening it in the join assistant, I get a parse error?!?

Here's what the Join assistant generated:

-* File join_virtual.fex
JOIN
 UVSCME.UVSCME.SC AND UVSCME.UVSCME.PG AND UVSCME.UVSCME.PGNODE
 AND UVSCME.UVSCME.PA AND UVSCME.UVSCME.PANODE AND UVSCME.UVSCME.ME
 AND UVSCME.UVSCME.MENODE AND AVTESTMETHOD WITH UVSCME.UVSCME.MENODE IN uvscme
 TO MULTIPLE UVSCMECELL.UVSCMECELL.SC AND UVSCMECELL.UVSCMECELL.PG
 AND UVSCMECELL.UVSCMECELL.PGNODE AND UVSCMECELL.UVSCMECELL.PA
 AND UVSCMECELL.UVSCMECELL.PANODE AND UVSCMECELL.UVSCMECELL.ME
 AND UVSCMECELL.UVSCMECELL.MENODE AND UVSCMECELL.UVSCMECELL.CELL IN uvscmecell
 TAG J0 AS J0
 END

To make that a bit easier on the eyes (and breaking it for the assistant entirely - when does IBI learn to write robust parsers?):

JOIN
     UVSCME.UVSCME.SC
 AND UVSCME.UVSCME.PG
 AND UVSCME.UVSCME.PGNODE
 AND UVSCME.UVSCME.PA
 AND UVSCME.UVSCME.PANODE
 AND UVSCME.UVSCME.ME
 AND UVSCME.UVSCME.MENODE
 AND AVTESTMETHOD WITH UVSCME.UVSCME.MENODE IN uvscme
TO MULTIPLE
     UVSCMECELL.UVSCMECELL.SC
 AND UVSCMECELL.UVSCMECELL.PG
 AND UVSCMECELL.UVSCMECELL.PGNODE
 AND UVSCMECELL.UVSCMECELL.PA
 AND UVSCMECELL.UVSCMECELL.PANODE
 AND UVSCMECELL.UVSCMECELL.ME
 AND UVSCMECELL.UVSCMECELL.MENODE
 AND UVSCMECELL.UVSCMECELL.CELL IN uvscmecell
TAG J0 AS J0
END

When I double-click on the join in the procedure viewer, I get a popup box containing the message:

Parsing ...
     4   AND UVSCME.UVSCME.MENODE AND AVTESTMETHOD WITH UVSCME.UVSCME.MENODE IN uvscme
---------------------------------------------------^ error at WITH 
3 lines, 1 errors.

If I try to add a new DEFINE box to the procedure viewer, after selecting the master for UVSCME, I get another error:

Error Parsing MASTER File UVSCME (FOC205) THE DESCRIPTION CANNOT BE
FOUND FOR FILE NAMED: WITH
(FOC1517) UNRECOGNIZED COMMAND AND
UVSCMECELL.UVSCMECELL.PGNODE AND
(FOC1517) UNRECOGNIZED COMMAND AND
UVSCMECELL.UVSCMECELL.PANODE AND
(FOC1517) UNRECOGNIZED COMMAND AND
UVSCMECELL.UVSCMECELL.MENODE AND
(FOC1517) UNRECOGNIZED COMMAND TAG J0 AS J0
(205)).

The goal here is to get a record from UVSCMECELL if, and only if, it has a cell with a matching value, while not filtering out records at the left side of the join if no such records exist on the right side.

The tables are both from the same Oracle 10g database. The fields are all of type VARCHAR2, with the exception of the few that end in NODE which are INTEGER fields.

Any suggestions?

This message has been edited. Last edited by: <Kathryn Henning>,

Apparently combining multiple join fields with one of them being DEFINE one is not going to work, as far as the UI is concerned.

Not knowing very well the data you have or the nature of your reporting need, I would suggest something like this:

1) JOIN only on the actual database fields you have access to. DEFINE-based joins are usually intended to help you join data sources that have no fields in common forcing you to come up with your own JOIN "key". As you already have a good set of fields you can use to do the actual JOIN, you can let go of the using a DEFINE field for joining. You'll get those records using regular filters instead

2) As you need all your records in "uvscme" regardless of matching ones in "uvscmecell", then you'll have to turn this into a LEFT_OUTER join.

3) As you are still interested only in records with "matching cells" (whatever that means) you can still use your DEFINE'd AVTESTMETHOD field as part of the WHERE section of your request. This would be easy if it wasn't for the fact that you're dealing with OUTER joins so more extra work may be needed ..

-* Left outer join without a DEFINE field
JOIN LEFT_OUTER
     UVSCME.UVSCME.SC
 AND UVSCME.UVSCME.PG
 AND UVSCME.UVSCME.PGNODE
 AND UVSCME.UVSCME.PA
 AND UVSCME.UVSCME.PANODE
 AND UVSCME.UVSCME.ME
 AND UVSCME.UVSCME.MENODE
 IN uvscme
TO MULTIPLE
     UVSCMECELL.UVSCMECELL.SC
 AND UVSCMECELL.UVSCMECELL.PG
 AND UVSCMECELL.UVSCMECELL.PGNODE
 AND UVSCMECELL.UVSCMECELL.PA
 AND UVSCMECELL.UVSCMECELL.PANODE
 AND UVSCMECELL.UVSCMECELL.ME
 AND UVSCMECELL.UVSCMECELL.MENODE
 IN uvscmecell
TAG J0 AS J0
END

-* DEFINE your "cell" field to be used for filtering only and make sure it allows MISSING values  

DEFINE FILE uvscme
AVTESTMETHOD/I2 MISSING ON = IF UVSCMECELL.UVSCMECELL.CELL IS MISSING THEN MISSING ELSE
                             IF your_define_cell_expression_here EQ UVSCMECELL.UVSCMECELL.CELL THEN 1 ELSE 0;
END

-* Retrieve records including only "valid" matching cells
TABLE FILE uvscme
PRINT uvscme_fields....
      uvscmecell_fields....
BY ....
-* Include only cells records that match the criteria
-* I attempt to check for "MISSING" values due to the outer join structure
WHERE (UVSCMECELL.UVSCMECELL.CELL IS MISSING OR AVTESTMETHOD EQ 1);
END

Of course this is just a high-level idea. You will need to tweak things a little (or perhaps more) to make this work and perhaps look into your SQLORA engine settings to make sure this all gets translated to proper SQL having the JOIN and filters handled by the database. SQL TRACE will be your best friend here.

You seem to be assuming that I'm looking for results that have no matching keys for (SC, PG, PGNODE, PA, PANODE, ME, MENODE). Those are quite rare. I do want those results too, but that's the easy part.

I'm looking for results that have matching join keys and that:

• either have a row with a value for the field CELL that matches 'avTestMethod'
  
• or have no such row.
  

The field CELL is only available on the right side of the JOIN (in UVSCMECELL).
I only want one record returned from UVSCMECELL; either the match with CELL EQ 'avTestMethod' or MISSING.

I most certainly do not want a whole bunch of records from UVSCMECELL where CELL has any other value than the one I'm looking for. That table contains over 18M rows containing quite a bit of data each.

The JOIN with the virtual field would have given me exactly what I need, but that doesn't work.
It looks like this isn't possible with FOCUS and I'm better off using straight SQL. It's an extra step to turn that into a report, but that beats bringing the database server down on its knees.

I don't see a show-stopper here. You did get the Join Assistant to generate valid, executable code. Granted it doesn't know how to read its own handwriting; does that matter?

If it does, try creating a business view that encompasses the join -- the Synonym tool's parser might be more robust.

It isn't more robust, as you can see from the result of trying to add a DEFINE block.

The FOCUS parser chokes on that JOIN definition as well, as soon as I add any meaningful code to that TABLE REQUEST:

-* File join_virtual.fex
JOIN LEFT_OUTER
 SC AND PG AND PGNODE AND PA AND PANODE AND ME AND MENODE AND AVTESTMETHOD WITH MENODE IN uvscme TO MULTIPLE
 SC AND PG AND PGNODE AND PA AND PANODE AND ME AND MENODE AND CELL IN uvscmecell TAG J0 AS J0
END
DEFINE FILE UVSCME
	AVTESTMETHOD/A20V WITH MENODE = 'avTestMethod';
END
TABLE FILE UVSCPA
PRINT *

WHERE SC EQ 'STS1345019M';
END

quote:

0 ERROR AT OR NEAR LINE 3 IN PROCEDURE join_virtual
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: WITH
(FOC1517) UNRECOGNIZED COMMAND SC AND PG AND PGNODE AND PA AND PANODE AND ME
AND MENODE AND CELL IN UVSCMECELL TAG J0 AS J0
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

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

I wonder if a JOIN with the WHERE clause would do the trick?

I used it recently to read data off an Oracle table. Perhaps it is worth trying.

Something along these lines:

JOIN FILE uvscme AT SC
     TO MULTIPLE
         FILE uvscmecell AT sc AS J2
         WHERE  ((uvscme.SC EQ uvscmecell.SC) AND (uvscme.PG EQ uvscmecell.PG) AND 
                 (uvscme.PGNODE EQ uvscmecell.PGNODE) AND (uvscme.PA EQ uvscmecell.PA) AND 
                 (uvscme.PANODE EQ uvscmecell.PANODE) AND (uvscme.ME EQ uvscmecell.ME) AND 
                 (uvscme.MENODE EQ uvscmecell.MENODE) AND (uvscmecell.CELL EQ 'avTestMethod')) ;
END

You are correct Twanette. There are rules for JOINS, and although the documentation doesn't specifically state you cannot use real and DEFINEd fields for a JOIN, the Syntax given does show that conclusion.

quote:

Syntax: How to Join Real Fields
The following JOIN syntax requires that the fields you are using to join the files are real fields declared in the Master File. This join may be a simple one based on one field in each file to be joined, or a multi-field join for data sources that support this type of behavior. The following syntax describes the simple and multi-field variations:
JOIN [LEFT_OUTER|INNER] hfld1 [AND hfld2 ...] IN hostfile [TAG tag1]
TO [UNIQUE|MULTIPLE]
crfield [AND crfld2 ...] IN crfile [TAG tag2] [AS joinname]
END

quote:

Syntax: How to Join From a Virtual Field to a Real Field
The DEFINE-based JOIN command enables you to join a virtual field in the host file to a real field in the cross-referenced file. The syntax is:
JOIN [LEFT_OUTER|INNER] deffld WITH host_field ...
IN hostfile [TAG tag1]
TO [UNIQUE|MULTIPLE]
cr_field IN crfile [TAG tag2] [AS joinname]
END

So the solution is a conditional JOIN

quote:

Syntax: How to Create a Conditional JOIN
The syntax of the conditional (WHERE-based) JOIN command is
JOIN [LEFT_OUTER|INNER] FILE hostfile AT hfld1 [WITH hfld2] [TAG tag1]
TO {UNIQUE|MULTIPLE}
FILE crfile AT crfld [TAG tag2] [AS joinname]
[WHERE expression1;
[WHERE expression2;
...]
END

So the question is why the Join tool allows this to happen in the first place, not the actual parsing of the resultant code.

I must have read over those details while searching for the reason my JOIN didn't work. Thanks for the details!

It's a shame that it's not possible to use at least a single constant virtual field in multi-field joins though, it makes sense to be able to do that and it translates to SQL quite easily too. Perhaps the problem there is that WebFOCUS is using implicit (SQL) joins instead of explicit ones.

A conditional JOIN would probably work, thanks for the suggestion.