I need to join two Oracle tables on SIC code (US Standard Industry Code), a four-character numeric code.

The parent table has SIC_CODE as a four-character foreign key, while in the dimension table the code is split into a pair of primary key fields, each covering two chacaters. Try as I might, I am unable to get WF to accept a "traditional" join (one without the FILE keyword).

I tried something like

JOIN 
SIC1 WITH SIC_CODE AND SIC2 WITH SIC_CODE IN CUSTOMER_TABLE
TO UNIQUE
SIC_CODE IN SIC_TABLE
AS J1
END

followed by

DEFINE FILE CUSTOMER_TABLE
SIC1/A2 WITH SIC_CODE = EDIT(SIC_CODE,'99$$');
SIC2/A2 WITH SIC_CODE = EDIT(SIC_CODE,'$$99');
END

and several variations. All were rejected as invalid JOIN syntax.

Any suggestions?

[Yes, I was able to do it with a condition-based join (what IBI insists on labelling a "conditional" join). Just surprised that one cannot combine the multiple key feature and the virtual field feature in the older JOIN syntax.

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

quote:

JOIN
SIC1 WITH SIC_CODE AND SIC2 WITH SIC_CODE IN CUSTOMER_TABLE
TO UNIQUE
SIC_CODE IN SIC_TABLE
AS J1
END

followed by
DEFINE FILE CUSTOMER_TABLE
SIC1/A2 WITH SIC_CODE = EDIT(SIC_CODE,'99$$');
SIC2/A2 WITH SIC_CODE = EDIT(SIC_CODE,'$$99');
END

Are you not inverting?
I sort of understood that in the target table there were 2 fields and in the source table only 1.
Shouldn't it be:

JOIN 
SIC1 WITH SIC_CODE AND SIC2 WITH SIC_CODE IN CUSTOMER_TABLE
TO UNIQUE
SIC_CODE1 AND SIC_CODE2 IN SIC_TABLE
AS J1
END

followed by
DEFINE FILE CUSTOMER_TABLE
SIC1/A2 WITH SIC_CODE = EDIT(SIC_CODE,'99$$');
SIC2/A2 WITH SIC_CODE = EDIT(SIC_CODE,'$$99');
END
  

Or maybe:

 
JOIN 
SIC1 AND SIC2 WITH SIC_CODE IN CUSTOMER_TABLE
TO UNIQUE
SIC_CODE1 AND SIC_CODE2 IN SIC_TABLE
AS J1
END
 

Worth a try...

i hope you find an answer to this. We've always had to hold the '2 field' table's contents, concatenate the 2 fields to one and join it to the other.

Other thoughts:
Create a DB view of the '2 field' table.
Redefine the master file to concatenate the 2 fields to one.

- ABT

I thought something like this was possible for relational tables:

JOIN SIC_CODE IN CUSTOMER_TABLE
  TO SIC_CODE1 AND SIC_CODE2 IN SIC_TABLE AS J1
END

If that doesn't work, and depending on what data is needed from the dimension table, you could try something like this:

JOIN SIC1 WITH SIC_CODE IN CUSTOMER_TABLE
  TO ALL SIC_CODE1 IN SIC_TABLE AS J1
END
-*
DEFINE FILE CUSTOMER_TABLE
 SIC1/A2 = EDIT(SIC_CODE,'99$$');
 SIC2/A2 = EDIT(SIC_CODE,'$$99');
 SIC_DESCRIPTION/A?? = IF (SIC2 EQ SIC_TABLE.SIC_CODE2) THEN SIC_TABLE.DESCRIPTION ELSE '';
END
-*
TABLE FILE CUSTOMER_TABLE
 SUM ....
  MAX.SIC_DESCRIPTION
 BY ....
END

quote:

Originally posted by j.gross:
I need to join two Oracle tables on SIC code (US Standard Industry Code), a four-character numeric code.

The parent table has SIC_CODE as a four-character foreign key, while in the dimension table the code is split into a pair of primary key fields, each covering two chacaters. Try as I might, I am unable to get WF to accept a "traditional" join (one without the FILE keyword).

I tried something like

JOIN 
SIC1 WITH SIC_CODE AND SIC2 WITH SIC_CODE IN CUSTOMER_TABLE
TO UNIQUE
SIC_CODE IN SIC_TABLE
AS J1
END

followed by

DEFINE FILE CUSTOMER_TABLE
SIC1/A2 WITH SIC_CODE = EDIT(SIC_CODE,'99$$');
SIC2/A2 WITH SIC_CODE = EDIT(SIC_CODE,'$$99');
END

and several variations. All were rejected as invalid JOIN syntax.

Any suggestions?

Which table holds the split sic code field and which the combined one? Your example is rather confusing in that respect as it appears to deal with combined codes in both tables.

I think what you mean to do is this:

JOIN
 SIC WITH SIC1 IN SIC_CODE TO
 SIC_CODE IN CUSTOMER_TABLE
AS J1
END
DEFINE FILE SIC_CODE
  SIC/A4 WITH SIC1 = SIC1 || SIC2;
END
TABLE FILE SIC_CODE
...
END

The idea is to combine the split fields into a single combined one, but since you can only use a virtual field (using WITH) in the parent table you have to swap the join around.

That's not always applicable, unfortunately. It'd be great if WF would allow virtual fields on both sides of the join, but that does have a significant performance impact.

WEP-

The parent table has a "combined" 4-char code, while the dimension table has it "split" into a pair of 2-char codes. I defined the pair of component codes w/r/t the parent table, and figured I could handle it quite directly in a JOIN with WITH and AND.

(confusing sentence, but you know what I mean.)


I tried it three ways -- inserting WITH after both defined fields; after only the first; and after only the second. All three were rejected. Once it sees WITH it chokes on AND; and vice-versa.

So I abandoned that route ("traditional" JOIN syntax) and, reluctantly, went for the "conditional" Join.

-- I was reluctant, only because I inherited this program and it has a long series of JOINs, all of which used traditional JOIN syntax; I was chary of going mix-and-match on the joins. But that turns out not to be a problem.

You never described what your dimension table looks like though, so I had to guess what fields were forming the split char-code.

The idea is to combine the split code in the dimension table into a single 4-char code using WITH. That way you don't need to AND multiple WITHs together, which isn't supported.

You do need to swap the join around for that though, since having WITH in the right-hand side of a JOIN is also not supported (I can see why).
Having to swap a complicated multiple join around is probably not desirable in your case, it will likely change the behaviour of the join.

As I understand it, the traditional JOIN syntax has these limitations because the resulting query plans would be quite inefficient anyway. I think that a JOIN is performed by sequentially scanning through the top table in the join and looking up the fields to combine with each record in the indices of the child tables.
If such a child table is joined on an unindexed field (and virtual fields would fall in that category), a sequential scan of the child table is required to find all the matching records in it. Unless that table is small, that's not going to perform well - provided that the engine accepts the plan at all.

Remember, that's how I understand joins are performed by WebFOCUS. I am not involved with development of FOCUS; it's all just a black box to me, as it is for you. I do know a thing or two about how databases perform joins though.

If I understand this correctly, your conditional join is probably not performing too well...

To turn this into an efficient join, the best route is to change things at the database level. Either arrange for a split field in the top table or arrange for a combined (indexed!) field in the dimension table.