RE: Trouble with an outer join

Vern, you're supposed to be on vacation. :-))

Paul Nelson
Office 512-392-2577
Cell 708-670-6978
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Friday, August 08, 2008 9:47 AM
To: Midrange Systems Technical Discussion
Subject: RE: Trouble with an outer join

Y'all

Allow me to add another vote for QMQRYs and QMFORMs - you can do so much
more with these than you can with Query/400 (the old name - sorry). You can
even use and/or convert your *QRYDFNs to a degree - there are some
limitations there.

If you can use the STRSQL command, you can use what is called Query Manager
- QM actually means Query Management, NOT Query Manager. QM is a
cross-platform technology that IBM has - it's on mainframes, too.

"Query Manager Use" is a manual that is very helpful - find it at

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415212.p
df

or

http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/books/sc41
5212.pdf

"Query Management Programming" gives you maybe more than you ever wanted to
know about the subject. But there are very important chapters on how QM
differs from *QRYDFNs, the problems you will encounter in that realm. Find
this manual at

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415703.p
df

or

http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/books/sc41
5703.pdf

Enjoy!
Vern
-------------- Original message --------------
From: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>

Emily, Chuck said it best.

I'd just add that SQL is not a reporting tool and to do what you desire

you

need a reporting tool. There are tons of them out there and Chuck's
suggestion for QMQRY are the best fit for you I think.
I'd also recommend Web Query, if you have access to it. It's very easy to
accomplish what you want using Web Query.

HTH, Elvis.

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Trouble with an outer join

The results described do not make a logical set; i.e. some rows of
data lose correlation, while one row from each of the join-to values
maintains correlation. As a report it might make apparent sense due to
ordering, but on a report, blanks versus null would likely suffice. The
function of replacing duplicated data on report lines is often called
/outlining/ and I believe that a QM Form can accomplish that. Query/400
can effect that with its /report break/ function, but its outlining is
available only on spooled output. IIRC QM Query supports outlining in
its form, and that applies to both its spooled and displayed output.
http://www.google.com/search?q=qmqry+outlining

An example of the outlined full outer join report with headings:

Q1 DESC1 SEL1 Q2 DESC2 SEL2
01 Bundled Twenties 600.00 01 Loose Twenties 100.00
01 Loose Twenties 60.00
01 Loose Twenties 180.00

For the above, break level one would be specified for the SEQ1,
DESC1, and SEL1 fields, with the /option/ to perform outlining. If
figuring out how to create the form is problematic, then if Query/400 is
a known, first creating a *QRYDFN definining one of the joins in the
union, and then using both RTVQMFORM and CRTQMFORM [optionally editing
that form in STRQM] might assist.

Regards, Chuck

Emily Smith wrote:

Carel - Thanks for the response, I tried it out and got the same

results.

Elvis - I tried your suggestion and I'm now getting three rows. The

problem now is that

The data from BRNDETLF is in each row...

01, 'Bundled Twenties', 600.00, 01, 'Loose Twenties' , 100.00
01, 'Bundled Twenties', 600.00, 01, 'Loose Twenties' , 60.00
01, 'Bundled Twenties', 600.00, 01, 'Loose Twenties' , 180.00

Is it possible to accomplish the following results?

01, 'Bundled Twenties', 600.00, 01, 'Loose Twenties' , 100.00
- , - , - , 01, 'Loose Twenties' , 60.00
- , - , - , 01, 'Loose Twenties' , 180.00

--

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