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] Business View Join Not Working

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Business View Join Not Working
 Login/Join
 
Member
posted
I'm copying the same join I created in a test procedure into a new business view. The last join in my business view (one-to-one, left outer join) is not pulling the correct information. I need the ACCOUNT_DATA_VIEW.AM_ACCOUNT_NAME to pull different information as the ACCOUNT_MASTER.AM_ACCOUNT_NAME field - but each field is pulling the same information. I wonder if the last table is pulling the same information because the fields are named the same??

Has anyone experienced this before and what was the resolution?

Here's my Text View Code from the business view:
FILENAME=JOIN_TEST, $
  SEGMENT=TRANS_FACTS, CRFILE=ZPDW/TRANS_DATA, CRINCLUDE=ALL, $
  SEGMENT=MAJOR_XREF, SEGTYPE=KU, PARENT=TRANS_FACTS, CRFILE=ZPDW/MAJOR_XREF, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=TRANS_FACTS.MAJOR_ID EQ MAJOR_XREF.MAJOR_ID;, $
  SEGMENT=ACCOUNT_DATA_VIEW, SEGTYPE=KU, PARENT=MAJOR_XREF, CRFILE=ZPDW/ACCOUNT_DATA_VIEW, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=MAJOR_XREF.ACCOUNT_ID EQ ACCOUNT_DATA_VIEW.ACCOUNT_ID;, $
  SEGMENT=ACCOUNT_MASTER, SEGTYPE=KU, PARENT=ACCOUNT_DATA_VIEW, CRFILE=ZPDW/ACCOUNT_MASTER, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=PARENT_ACCOUNT_ID EQ ACCOUNT_MASTER.ACCOUNT_ID;, $ 

This message has been edited. Last edited by: FP Mod Chuck,


WebFocus 8105, Windows 2016, HTML
 
Posts: 10 | Registered: September 11, 2017Report This Post
Virtuoso
posted Hide Post
What kind of dbms are your tables in?
If they're relational, can you run a report with the problem showing us the SQL Trace?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by spp_123:
I need the ACCOUNT_DATA_VIEW.AM_ACCOUNT_NAME to pull different information as the ACCOUNT_MASTER.AM_ACCOUNT_NAME field - but each field is pulling the same information. I wonder if the last table is pulling the same information because the fields are named the same??


How do you access these fields, with fully qualified name or just the field name ?

Maybe sharing also your code (TABLE FILE JOIN_TEST...END) may help us helping you


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
The test procedure that includes the joins works - it's the business view that doesn't. The tables are in an MS SQL Server 2008.

Here's the procedure code, in case it helps to see. The J2.ACCOUNT_MASTER.ACCOUNT_NAME field is exporting the same data as the J1.ACCOUNT_DATA_VIEW.ACCOUNT_NAME in the business view.

JOIN
INNER TRANS_FACTS.TRANS_FACTS.MAJOR_ID IN TRANS_FACTS TO UNIQUE
MAJOR_XREF.MAJOR_XREF.MAJOR_ID IN MAJOR_XREF TAG J0 AS J0
END
JOIN
LEFT_OUTER J0.MAJOR_XREF.ACCOUNT_ID IN TRANS_FACTS TO UNIQUE
ACCOUNT_DATA_VIEW.ACCOUNT_DATA_VIEW.ACCOUNT_ID IN ACCOUNT_DATA_VIEW TAG J1
AS J1
END
JOIN
LEFT_OUTER J1.ACCOUNT_DATA_VIEW.PARENT_ACCOUNT_ID IN TRANS_FACTS TO UNIQUE
ACCOUNT_MASTER.ACCOUNT_MASTER.ACCOUNT_ID IN ACCOUNT_MASTER TAG J2 AS J2
END
TABLE FILE TRANS_FACTS
BY  J1.ACCOUNT_DATA_VIEW.ACCOUNT_NAME
BY  J2.ACCOUNT_MASTER.ACCOUNT_NAME 


WebFocus 8105, Windows 2016, HTML
 
Posts: 10 | Registered: September 11, 2017Report This Post
Gold member
posted Hide Post
remove the other join, and use the ACCOUNT_DATA_VIEW table in the join with the parent to test if it works fine.


WebFOCUS 8
Windows, All Outputs
 
Posts: 71 | Registered: January 23, 2018Report 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] Business View Join Not Working

Copyright © 1996-2020 Information Builders