Please help with SQL query

2505 pts.
Tags:
AS400 - Embedded SQL
I have a file which has 6 different account numbers. For each of this account number, I need to check in File1 which has account number, security and Quantity. I need to sum up the qty field for a combination of Acc num and security. Once I get the security, I need to search in another file say File2 for ISIN vaue corresponding to the security which I got from File1. Please help me to write a SQL query for this. I need to write the output in a summary file.

Answer Wiki

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

Discuss This Question: 5  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
  • carlosdl
    Please specify the platform you want to do this on.  Also, it would be a good idea to post some example data, and the results you would want to get from it.
    69,835 pointsBadges:
    report
  • carlosdl
    Ok, now I see the AS400 tag.  The request for example data is still valid, though.
    69,835 pointsBadges:
    report
  • RamvishakRamesh
    I have an account file with 6 different account numver say A,B,C,D,E anf FI have another file say file1 with account number, security and Quantity.A        X         2A       X          1A        Y        1A         Y       3B          X       1B          X       2I have another file say file2 which has security as the key and value ISINX         1122Y          1233I need to create a summary file which has account number, security, Qty and ISINA          X      3  1122A          Y      4   1233B           X      3   1122
    2,505 pointsBadges:
    report
  • RamvishakRamesh
    I have an account file with 6 different account numver say A,B,C,D,E and F. I have another file say file1 with account number, security and Quantity.A X 2 A X 1 A Y 1 A Y 3 B X 1 B X 2I have another file say file2 which has security as the key and value ISINX 1122 Y 1233I need to create a summary file which has account number, security, Qty and ISINA X 3 1122 A Y 4 1233 B X 3 1122
    2,505 pointsBadges:
    report
  • TomLiotta
    You mentioned in your other thread that you don't yet know SQL. Until you learn the basics of SQL, it won't be reasonable to write a query for you. This is especially true because you could be using embedded SQL in ILE RPG.   The actual solution wouldn't use RPG at all. It starts with a SELECT that retrieves account numbers from you FILE1. It is expanded to JOIN on account number to FILE2. Then grouping functions are used to define the grouping for your summary values and to do the summations. Then all of that feeds an INSERT to place the full result set into your summary table.   It becomes a single SQL statement. But you need to know the basic SQL statements and how they work before the solution will be useful to you in your studies.   If you want to try to create an example, we can help with problems. But we can't give the answer.   Tom
    125,585 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