Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Business View Join Not Working
Go
New
Search
Notify
Tools
Reply
  
[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, 2017Reply With QuoteReport 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 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2106 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport 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, 2017Reply With QuoteReport This Post
Silver 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: 40 | Registered: January 23, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Business View Join Not Working

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.