AS/400 query join

9665 pts.
Tags:
AS/400 administration
AS/400 Query
AS/400 Query Select Records
V6R1
I have 2 files I am joining. The primary file is an employee master. The secondary is a history file with the employees time punches. Using a type 1 join it works fine. The user wants a change to the query to show the employees who did not punch in for a specific day as well, which would be a type 2 join. Running the query doesn't give the results I am expecting.  I do have a prompt for the user for the record select in the query definition where they change the desired date. The date exists in the secondary file. Now when the user runs the qry, it still only returns matches to that date. It does not return any primary records that are not found with data for that date. Is this because of the date selection? Is there any way around this without havig to write a program? This is on an AS/400 running V6R1M0

Software/Hardware used:
i-series V6R1M0

Answer Wiki

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

Hi,

If I got your requirement correctly, then I believe below query can help you:-

SELECT EMP_NAME FROM MASTER A WHERE NOT EXISTS (SELECT * FROM SECONDARY B WHERE A.EMP_ID = B.EMP_ID AND B.DATE = YOUR_DATE)

Regards
Piyush

Discuss This Question: 12  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
  • Mariodlg
    I think we need to know your query and the structure of both files in order to help you. Please tell us a little more about it. Regards.
    2,790 pointsBadges:
    report
  • ToddN2000
    let me clarify things a bit more. Using the as/400 command WRKQRY we want to produce an edit report. The employee master (primary) is joined to the punch detail (secondary) by the employee# with a type 2 join. Then on the select records option we are selecting punch records equal to the date entered by the user prompt. It will return employee records from the punch file that match but not the records that have on matching date record.
    9,665 pointsBadges:
    report
  • carlosdl
    Is SQL involved here ? If so, can someone please clarify what a 'type 2 join' is ?
    69,045 pointsBadges:
    report
  • philpl1jb
    No Carlos, not SQL .. this is query/400 ... a menu driven system. Todd, As you described it you should get all matched records plus any employee records with nulls for the secondary file fields. So how does query/400 work anyway... if it joins and then selects, those records have joins but are subsequently excluded by the select... so if that's how it works, create two queries, the first selects records from the secondary file for your date and outputs to a temporary file, the second joins the primary file with the temporary file with option 2. Phil
    49,720 pointsBadges:
    report
  • carlosdl
    Thanks Phil. Good to see you around.
    69,045 pointsBadges:
    report
  • TomLiotta
    SQL (e.g., a QM query) could get this done fairly easily. But for Query/400, I can't think of anything better than Phil's answer. One potential possibility is to create a VIEW over the punch detail that selects for date, then create the type 2 over employee master and the view. The view could take the place of an initial Query/400 query. Tom
    125,585 pointsBadges:
    report
  • Mariodlg
    I agree with Phil's solution too. For clarify to the readers, type 2 join is for selecting matching records with the primary file.
    2,790 pointsBadges:
    report
  • philpl1jb
    Hi Carlos. Now that i've thoujght about it, I agree with me too!! That's why we were always writing those multiple queries. In Sql we would do a left join to a subselect. from the punch detail file. That looks like one query but works just like the two AS/400 queries that I described previously. Phil
    49,720 pointsBadges:
    report
  • Kepn
    Keep the date selection for the stamped in users as it is, but add OR and state that you wan to see records where there is no info from the history file, you can test on the date field and search for records that has either 0 (if numeric) or your default date value for the application. Then it will show you all of the records corresponding to the given date from the transaction file, but also all of the records from the employee file where there is no match. If you have 100 employees and only 10 of them check in on a given date, the qry will return the other 90, but there will be no info from the transaction file for them. If that is what you want? As there is no date in the employee file, it will always return all of the records that has no check ins.
    20 pointsBadges:
    report
  • ToddN2000
    [...] AS/400 query join [...]
    0 pointsBadges:
    report
  • ToddN2000
    [...] AS/400 query join [...]
    0 pointsBadges:
    report
  • Modiyooch
    2 step query. First query only selects the date you are interested in and places that in a work file. Second query selects unmatched data. Do not use any fields in the second file for selection.
    540 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