Re: Help needed in query/400

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Murali

Not easily done in Query/400 - SQL is the answer.

Assume FILEA and FILEB in library VERN with the fields below:

SELECT filea.account, filea.exch, filea.initmgn,
coalesce(fileb.account,' '), coalesce(fileb.exch, ' '),
coalesce(fileb.initmgn, 0) FROM vern/filea left outer join
vern/fileb on filea.account = fileb.account and filea.exch =
fileb.exch UNION SELECT coalesce(filea.account,' '),
coalesce(filea.exch, ' '), coalesce(filea.initmgn, 0),
fileb.account, fileb.exch, fileb.initmgn FROM vern/filea right
outer join vern/fileb on filea.account = fileb.account and
filea.exch = fileb.exch

Add an ORDER by if needed, not sure how you'd need to handle that.

Good luck
Vern

At 04:49 PM 8/5/2004, you wrote:
> My primary file is as below
>
>
>              ACCOUNT   EXCH   INITMGN
>
> 000001 XX2102201  22          .00
>
> 000002 XX2102203  22          .00
>
> 000003 X98000210  22      18,000,000.00
>
> 000004 X98000210  24       158,960,000.00
>
> 000005 X98000310  24      50,000,000.00
>
> 000006 X98010000  22     662,993,333.00
>
> My secondary file is as below:
>
>               ACCOUNT   EXCH       INITMGN
>
> 000001 XX2102201   22                  .00
>
> 000002 XX2102203  22                    .00
>
> 000003 X98000210  22            18,000,000.00
>
> 000004 X98000210 24            158,960,000.00
>
> 000005 X98000310 24           36,000,000.00
>
>
>
> The type of join mentioned is Specify Type of join
>
>
> Type choice, press Enter.
>
>
> Type of join . . . . . . . . 1 1=Matched records
>
> 2=Matched records with primary file
>
> 3=Unmatched records with primary file
>
> Specify How to join Files
>
>
> Type comparisons to show how file selections are related, press
>
> Tests: EQ, NE, LE, GE, LT, GT
>
>
> Field Test Field
>
> T01.ACCOUNT EQ T02.ACCOUNT
>
> T01.EXCH EQ T02.EXCH
>
>
> Define Result Fields
>
>
> Type definitions using field names or constants and operators, press Enter.
>
> Operators: +, -, *, /, SUBSTR, ||, DATE...
>
>
> Field        Expression                                           Column 
>             Heading Len
>
> FLUCT     T01.INITMGN-T02.INITMGN     FLUCTUATION
>
>
>
>
> IMYDAY    T01.INITMGN IM-YDAY
>
>  Field       Expression                         Column Headin
>  IMTODAY     T02.INITMGN                        IM-TODAY
>
>
> ================================================================================= 
>
> The 6th record
>
> "000006 X98010000 22 662,993,333.00 " This record is not coming in the 
> final file.
>
> This query is designed to take only the matched records from primary and 
> secondary file to produce the final output file. 6th record in primary 
> file is not coming
>
> My final file is coming like below: But I want this final file should pick 
> 6th record as well, can I achieve this with query?
>
>              ACCOUNT            EXCH      IM-YDAY                 I 
> M-TODAY                               FLUCTUAT
>
> 000001 
> XX2102201           22                .00                              .00
>
> 000002 
> XX2102203          22                 .00                              .00
>
> 000003 
> X98000210         22               18,000,000.00           18,000,000.00
>
> 000004 
> X98000210         24              158,960,000.00            158,960,000.00
>
> 000005 
> X98000310         24               50,000,000.00            36,000,000.00 
>                          14,000,000
>
> I want the final file to be as below:
>
>               ACCOUNT  EXCH              IM-YDAY 
> IM-TODAY                FLUCTUATION
>
> 000001 
> XX2102201       22                  .00                                    .00
>
> 000002 
> XX2102203       22                  .00                                    .00
>
> 000003 
> X98000210         22               18,000,000.00 
> 18,000,000.00
>
> 000004 
> X98000210         24                158,960,000.00 
> 158,960,000.00
>
> 000005 
> X98000310          24               50,000,000.00 
> 36,000,000.00               14,000,000
>
> 000006 
> X98010000         22                662,993,333.00 
> 00.00                                   662,993,333.0
>
>
>
> will I be able to get records either in primary/secondary  that donot have 
> matched records too?
>
>
>
>
>
> --
> 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.