using SQL in RPG

115 pts.
Tags:
AS/400
RPG
SQL
I want to use the following 2 SQL statements in 1 RPG program and have the results print out. How can i accomplish this task? Not sure how to do even 1 SQL statement and have it print, let alone 2. Select RNSREGN region, COUNT(RNSINVN) #Orders, SUM(RNSQTY) #Books, SUM(RNSTOT$) $$ from WFILES.RNSORDP WHERE RNSREGN > 0 GROUP BY "RNSREGN" Select BOHREGN region, COUNT(BOHINVN) #Orders, SUM(BOHQTY) #Books, SUM(BOHDTTOT) $$ from WFILES.BOHEDR WHERE BOHREGN > 0 GROUP BY "BOHREGN"

Software/Hardware used:
ibm i

Answer Wiki

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

phil & tom thanks for your input.     i will look at the examples and try to figure it out.

 

tom,  it is for ILERPG.  i have not heard of Query Manager.  i am fairly new to SQL.  that might be a better way of doing it.   i am NOT asking you to write the program for me.    i can write the program in ILE RPG, just thought it might be easier to user SQL.

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
  • philpl1jb
    Each of these SQL's are likely to select multiple rows.  For each of these you will need to:  Declare a cursor Open the cursorFetch rows from the cursor into the host fields  Here is an example:  http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fsqlp%2Frbafycursorexamp.htm  and another example:  http://www.itjungle.com/fhg/fhg051309-story02.html   
    50,465 pointsBadges:
    report
  • philpl1jb
    I wonder what happened to my first answer....  It said that since these each can return multiple rows they each require a cursor, the basic process is:  - Declare cursor- Open cursor- fetch a row at a time into host variables or host data structure until no more rows exist.  here is an example  http://www.itjungle.com/fhg/fhg051309-story02.html
    50,465 pointsBadges:
    report
  • TomLiotta
    First, simplify your two SELECT statements:  
    SELECT RNSREGN, COUNT(RNSINVN), SUM(RNSQTY), SUM(RNSTOT$) FROM WFILES/RNSORDP WHERE RNSREGN > 0 GROUP BY RNSREGN SELECT BOHREGN, COUNT(BOHINVN), SUM(BOHQTY), SUM(BOHDTTOT) FROM WFILES/BOHEDR WHERE BOHREGN > 0 GROUP BY BOHREGN
      There will be no reason to rename your columns because that will happen when SQL places the values in the RPG program variables that you will define.   Also, you probably should change your database so that you don't have a field named "RNSTOT$", but you might not have any choice for that. You don't want characters such as "$" as part of a name. It can work, but it can be a problem someday. The sooner it is fixed, the better.   And actually all of those names should be changed. They are all too short to be very useful. Again, they will work; but better names will be useful in the future.   It's not clear why you would want to do this in RPG. If you are only SELECTing rows and printing them, it would be far better to do it with Query Manager. Usually you would put SQL statements into a program only if you want the program to process the values. But you aren't doing any processing. You're just copying the values from the table to a printer.   Also, you didn't say if you must use OPM RPG or ILE RPG. The names from your tables look like they are intended for the old OPM RPG. If that is what you need, you should tell us.   Other than that, Phil's comments are right. Look at examples that are already provided in the Information Center first. Then show us what you have written for a program. We'll help get you past any errors.   However, we don't write programs for you.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    i have not heard of Query Manager.   First, it's a very good idea to learn embedded SQL in a programming language such as RPG (ILE version is best, but any embedded SQL experience is better than none.)   In the long run, though, the understanding that "embedded" SQL is best used when there is a need for a programming language to manipulate the values. Simple transfers of values from one file to another, or anything that can be done cleanly by SQL itself, aren't well suited for it.   As for Query Manager, your system might have the full user interface installed or it might only have the run-time support. Either way, printing your queries is easy. To determine which options are available to you, try to run the STRQM command on a command line. If you are shown the Query Manager menu, you have the full user interface.   Post your result back here for more info.   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