RE: Trouble with an outer join

["Paul Nelson" <nelsonp@xxxxxxxxxxxxx>]

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.