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

I think that is one of those cases where ANZQMQRY is going to throw you
some warnings that RTVQMQRY is not going to tell you exactly what you saw
in Query/400.

select * from pete1
1....+....2....+....
KEY1 DATA1
1 1
1 2
1 3
2 1
2 5
End of data ******

select * from pete2
1....+....2....+....
KEY2 DATA2
1 10
2 11
3 12
4 13
End of data ******

select * from pete3
1....+....2....+....3....+....4....+
KEY3 DATA3A DATA3B
1 20 30
3 22 32
4 23 33
5 24 34
End of data ********

select * from pete4
.1....+....2....+....3....+....4....+
KEY4A KEY4B DATA4
2 41 51
3 42 52
4 43 53
5 44 54
End of data ********

select * from pete5
1....+....2....+....
KEY5 DATA5
1 60
2 61
5 64
End of data ******

select key1, data1,
key2, data2,
key3, data3a, data3b,
key4a, key4b, data4,
key5, data5
from pete1 left outer join pete2 on data1=key2
left outer join pete3 on data1=key3
left outer join pete4 on data1=key4a
left outer join pete5 on data1=key5
where key1<1000
order by key1
1....+....2....+....3....+....4....+....5....+....6....+....7....+...
KEY1 DATA1 KEY2 DATA2 KEY3
1 1 1 10 1
1 2 2 11 -
1 3 3 12 3
2 1 1 10 1
2 5 - - 5
End of data ********
.8....+....9....+...10....+...11....+...12....+...13....+...14..
DATA3A DATA3B KEY4A KEY4B
20 30 - -
- - 2 41
22 32 3 42
20 30 - -
24 34 5 44
******* End of data ********
..+...16....+...17....+...18....+...19
DATA4 KEY5 DATA5
- 1 60
51 2 61
52 - -
- 1 60
54 5 64
******** End of data ********


Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Pete Helgren <Pete@xxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/11/2007 11:59 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
Making Matched Records with Primary File in Query an Outer join in SQL






I have a query that works just as I want it to. I used the RTVQMQRY
command to retrieve the source and then I modified the source using
interactive SQL to try to "mimic" the Query in SQL. I am *close* but
not quite there.

I used "Matched Records with Primary File" in the query because I want
the missing records to show up as blanks in the query output. The query
produces the desired results. When I run the SQL, I get the same
records, sans the "missing" ones. It is a complex query joining 5
files, some of which have 5 key fields to join. What I can't figure out
is what the join syntax is in the query. It must be using an outer join
but are all the 5 files using left outer join syntax? e.g

select (fields)
from
file 1
left outer join file2 on
(file1 and file2 field selections)
left outer join file3 on
(file1 and file3 field selections)'
left outer join file 4 on
(file 1 and file4 field selections)
where (field selections)
order by (whatever)

Any way to see what Query/400 is using?

Thanks,

Pete Helgren
--
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 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.