I'm trying to join ALLACTS to NBTBLOT and then to NBTBAF (see below). It is dropping the values from table c. I tried putting a hold file after the first join but it didn't like my index of 4 fields so I tried to do a define concatenating the 4 fields but it didn't like that either. Without the index, it's so slow it won't return results. I also tried joining ALLACTS to NBTBLOT and then NBTBLOT to NBTBAF but it didn't like that either. I know I could do things like this in the old version. Does anyone have any suggestions? Thanks.

JOIN ACCOUNTID IN ALLACTS
TO ALL ACCOUNTID IN NBTBLOT AS J1
END
JOIN ACCOUNTID AND CUSIPID AND ASOFDATE AND AMSSOURCE IN ALLACTS
TO ACCOUNTID AND CUSIPID AND ASOFDATE AND AMSSOURCE IN NBTBAF AS J6
END

Couple quick questions:
- By dropping values do you mean missing records from the report?
- What type of data? DB2? Oracle?
- You've confirmed the keys?
- What do you get when you issue a "? JOIN" command?

I did figure one more thing out. It's a one to many join and again a one to many join and I think that's my problem. Unfortunately, I've managed to put the 1st join to a hold and to add an index by concatenating the 4 fields, but it's much slower than doing the joins all together.

By dropping values do you mean missing records from the report? Ans .. I'm not missing records, just some records have spaces instead of the values expected.
- What type of data? Data is DB2
- You've confirmed the keys? Yes.
- What do you get when you issue a "? JOIN" command?
WEBFOCUS ERROR Server = EDASIT Error Message = 0 NUMBER OF RECORDS IN TABLE= 37 LINES= 37 JOINS CURRENTLY ACTIVE HOST CROSSREFERENCE FIELD FILE TAG FIELD FILE TAG AS ALL WH ----- ---- --- ----- ---- --- -- --- -- ACCOUNTID NBTBAUTH ACCOUNTID NBTBMF Y N ACCOUNTID ALLACTS ACCOUNTID NBTBLOT J1 Y N ACCOUNTID ALLACTS ACCOUNTID NBTBAF J6 N N

From your message it sounds like you don't have a single path for the join structure. When you do a "CHECK FILE ALLACTS" do you get something like this?

*************
*ACCOUNTID **
*CUSIPID **
*************
*************
I
+-----------------+
I I
I
02 I 03
.............. ..............
:ACCOUNTID :: :ACCOUNTID ::
: :: :CUSIPID ::
:............:: :............::
.............: .............:
JOINED NBTBLOT JOINED NBTBAF

If so, it might impact your results as it can't search both paths. How big of a performance hit would it be to flip the first join and join many to one and then one to many like this:

JOIN ACCOUNTID IN NBTBLOT
TO ACCOUNTID IN ALLACTS AS J1
END
JOIN ALLACTS.ACCOUNTID AND ALLACTS.CUSIPID AND ALLACTS.ASOFDATE AND ALLACTS.AMSSOURCE IN NBTBLOT
TO ACCOUNTID AND CUSIPID AND ASOFDATE AND AMSSOURCE IN NBTBAF AS J6
END

(This applies only if my first assumption was true regarding the join structure)

If this is not the case I suggest turning on tracing to see what SQL is getting generated under the covers.

You were correct. It was a one to many and then again a one to many join. So, I tried flipping like you suggested but it was still slow. We tried a multiple of other things and nothing sped it up. We then decided to use a table view on the mainframe and that sped things up significantly and pulled back the correct data. So, our problem is now resolved by using the db2 table view. Thanks for the suggestion.

In your second join, in which table are the join components CUSIPID, ASOFDATE, AMSSOURCE located -- in ALLACTS or in NBTBLOT?

using direct SQL will speed your joins significatly, I have found. the report painter is not so keen on it, though.