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,

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?

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

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 

remove the other join, and use the ACCOUNT_DATA_VIEW table in the join with the parent to test if it works fine.