Using Query on 4 different files

5 pts.
Tags:
AS/400 File Sharing
AS/400 Query
AS/400 Reports
Query
Hi i have four different files each containing a Customer name ,number and sales amounts ,each file is for a different quarter of the year ,i would like to join the four files so that i can produce a report which lists across the page by quarter the sales for each customer,however not all the customers with a sales figure in the first quarter(file,column) necessarily have an entry in any/all of the other three quarters(files,columns)

I cannot find a satisfactory method to join all 4 files so that i get all the information from each of the 4 files on my Report.

Any Help Appreciated

Cheers



Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

How large are the files?
If you must use Query, and there is no field can be used for your join, you can just do CPYF and merge them all into one file and do a Query over that one.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    You don't say what query product you're using, but it seems reasonable to assume that you mean 'Query for iSeries' (or Query/400). I can't quite see a good way to do it with Query/400, though I wouldn't be too surprised if someone had an idea. For the most part, I can't think of a good reason to use JOIN for this in the first place. To me, this seems much more like a UNION that a JOIN. Here's a simple SQL way to express the structure:
    with qsum (qname, q1, q2, q3, q4) as (
         SELECT CNAME, CDTA as Q1 , 0 as Q2, 0 as Q3, 0 as Q4 FROM cdta1
           union
         SELECT CNAME, 0 as Q1, CDTA as Q2 , 0 as Q3, 0 as Q4 FROM cdta2
           union
         SELECT CNAME, 0 as Q1, 0 as Q2, CDTA as Q3 , 0 as Q4 FROM cdta3
           union
         SELECT CNAME, 0 as Q1, 0 as Q2, 0 as Q4, CDTA as Q3  FROM cdta4
        )
    select qname, sum(q1), sum(q2), sum(q3), sum(q4)
      from qsum
      group by qname
    You have four tables -- CDTA1 through CDTA4. The CDTA1 table has a name and an amount for the first period. The other three tables have names and amounts for the remaining three periods. Since there is no guarantee that any name will be in all four periods, there is no JOIN that can link everything together unless maybe you set up a very ugly combination of EXCEPTION JOINs in all directions. (And I'm not sure if that could be made to work.) Come to think of it, I suppose, for Query/400, you might create a multi-format LF over all of the periods that provided default zero values for the other three periods. You might then use that as input to your query and summarize the values. To me, it seems far easier not to use Query/400 at all and just do it directly with a Query Manager query instead. You should almost be able to plug your field names into the example above and run it. I can't think of any reason still to be using Query/400. Tom
    125,585 pointsBadges:
    report
  • Mariodlg
    Look for the uses of LEFT/RIGHT OUTER JOIN, that way you can list data that exist only in one file, so you could list all of your customers information regardless if any of them not appear in one of the files. Hope it complements the other answers.
    2,790 pointsBadges:
    report
  • TomLiotta
    Typo error correction -- this line:
         SELECT CNAME, 0 as Q1, 0 as Q2, 0 as Q4, CDTA as Q3  FROM cdta4
    ...should be:
         SELECT CNAME, 0 as Q1, 0 as Q2, 0 as Q3, CDTA as Q4  FROM cdta4
    Tom
    125,585 pointsBadges:
    report
  • Splat
    Query/400 will handle multiple files but to get all of your customers on the report you will need as a primary file one containing all of your customers. Is there a reason you can't use your customer master file as the primary?
    6,895 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following