Re: Help needed in query/400

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

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.