Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Join with multiple virtual fields

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Join with multiple virtual fields
 Login/Join
 
Virtuoso
posted
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
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


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Join with multiple virtual fields

Copyright © 1996-2020 Information Builders