|
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 AMcan
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
even use and/or convert your *QRYDFNs to a degree - there are someManager
limitations there.
If you can use the STRSQL command, you can use what is called Query
- QM actually means Query Management, NOT Query Manager. QM is ahttp://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415212.p
cross-platform technology that IBM has - it's on mainframes, too.
"Query Manager Use" is a manual that is very helpful - find it at
dfhttp://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/books/sc41
or
5212.pdfto
"Query Management Programming" gives you maybe more than you ever wanted
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 athttp://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/books/sc415703.p
dfhttp://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/books/sc41
or
5703.pdfto
Enjoy!
Vern
-------------- Original message --------------
From: "Elvis Budimlic"
Emily, Chuck said it best.you
I'd just add that SQL is not a reporting tool and to do what you desire
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
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
lista known, first creating a *QRYDFN definining one of the joins in theresults.
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
listproblem now is that
Elvis - I tried your suggestion and I'm now getting three rows. The
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
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
To post a message email: MIDRANGE-L@xxxxxxxxxxxxlist
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
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
