RE: Trouble with an outer join

["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...
> <ed; output rewritten>
>  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?
> <ed; output rewritten>
> 01, 'Bundled Twenties', 600.00, 01, 'Loose Twenties'  , 100.00
> - , -                 , -     , 01, 'Loose Twenties'  ,  60.00
> - , -                 , -     , 01, 'Loose Twenties'  , 180.00