Re: Making Matched Records with Primary File in Query an Outer joinin SQL

"Pete, make sure you double check the joins being done in query vs. the joins being done in SQL. "

There's the rub. I don't know what the joins are in the query because all I have is a list of "where " comparisons. That is I have "Where field1=field2 and field1 = field2 " etc instead of "from file1 join file2 on field1= field2 join file3 on field1 = field3" That is what is leaving me scratching on head on a " Matched Records with Primary File" option in query. There are no joins listed in the query from RTVQMQRY.

I guess to restate this: If there WAS a way to see the exact syntax (SQL Syntax) from a query, replicating that in SQL would be a snap. As it is, when you have a complex query that produces the desired results in Query/400, you basically have to start from scratch in rebuild that query in SQL. In my particular case I have a query that outputs in a way I expect in Query/400 but replicating that same result in SQL had been devilishly difficult.

I wonder how the new Web Query product is going to migrate existing Query/400 queries (if it indeed does do that)? They must have a better tool than RTVQMQRY .

Pete

Wilt, Charles wrote:

Note that one thing to keep in mind is the join fields being specified in Query or SQL

There's two options in both:
F1 joined to F2
F2 joined to F3

Or

F1 joined to F2
F1 joined to F3

The join chosen changes the results. For instance, with Chucks example data slightly modified (F1 has
1-10, F2 has 1-5, and F3 has 2,4,6,8 ) option 1 returns:

SEQNBR SEQNBR SEQNBR
1 1 -
2 2 2
3 3 -
4 4 4
5 5 -
6 - -
7 - -
8 - -
9 - -
10 - -

Whereas option 2 returns
SEQNBR SEQNBR SEQNBR
1 1 -
2 2 2
3 3 -
4 4 4
5 5 -
6 - 6
7 - -
8 - 8
9 - -
10 - -

Pete, make sure you double check the joins being done in query vs. the joins being done in SQL.
HTH,
Charles