RE: Making Matched Records with Primary File in Query an Outer joininSQL

Pete,

Can't you go into the query with WRKQRY and look at this screen:

Specify How to Join Files

Type comparisons to show how file selections are related, press Enter.
Tests: EQ, NE, LE, GE, LT, GT

Field Test Field
T01.SEQNBR EQ T02.SEQNBR
T01.SEQNBR EQ T03.SEQNBR


Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Pete Helgren
Sent: Friday, October 12, 2007 9:01 AM
To: Midrange Systems Technical Discussion
Subject: Re: Making Matched Records with Primary File in
Query an Outer joininSQL

"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

--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change
list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.