Embedded SQL in RPGLE

165 pts.
Tags:
Embedded SQL
MySQL
RPGLE
SQL
SQL statements
I am trying to get some counts and totals from one file and put them in another. My SQL statement works fine with STRSQL, not in the RPGLE. Do I have to declare/set a cursor, exec sql and close the cursor just to put the total in another file?

C/EXEC SQL

C+ INSERT INTO outputfile

C+ SELECT USERNAME,

C+ COUNT(HORD#) as numord, SUM(QTY) as units

C+ FROM ORDHDR INNER JOIN ORDDTL

C+ ON ORDHDR.HORD# = ORDDTL.DORD#

C+ WHERE ORDDAT >= :BEGDATE AND ORDDAT < :ENDDATE

C+ GROUP BY USER

C+ ORDER BY USER

C/END-EXEC



Software/Hardware used:
rpg v5r4
ASKED: September 27, 2010  2:02 PM
UPDATED: September 29, 2010  10:59 PM

Answer Wiki

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

True, they are.

The problem was that I needed this:

C/EXEC SQL SET OPTION commit=*none, closqlcsr=*endmod
C/END-EXEC

I did not know. Still not sure exactly what the commit=*none does but it works.
Thanks
Nick

=================================================

<i>SET OPTION commit=*none</i>

That tells SQL that you aren’t using commitment control for this program. If you don’t use commitment control, then SQL is going to rollback any changes unless you tell it that commitment control shouldn’t be applied to these transactions. This will cause UPDATE, INSERT and DELETE statements to appear not to be working.

An alternative could be to COMMIT the changes.

Tom

I think Tom got it turned around
If SQL is using commitment control, then unless a commit is issued, the changes are rolled back.

OPTION commit=*none tells SQL that you don’t want to use commitment control and then the Changes are complete as the commands are issued.

In interactive Sql use F13 to check commit level — you probably have it set to none.
Phil

Discuss This Question: 7  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
    Select USERNAME Group by USER Order by USER These should all be the name of the same field!!!! Phil
    48,575 pointsBadges:
    report
  • Nickhutcheson
    True, they are. The problem was that I needed this: C/EXEC SQL SET OPTION commit=*none, closqlcsr=*endmod C/END-EXEC I did not know. Still not sure exactly what the commit=*none does but it works. Thanks Nick
    165 pointsBadges:
    report
  • Nickhutcheson
    The statement was good, but incorrect totals were generated. I tried the DISTINCT function on the order count. It looked real close. I found a doc on the net that discusses how to use group by in SQL. It goes over the problems of thinking you have correct results when using DISTINCT. It, in a nutshell, said to run the sql on the detail, get the results. then surround the clause with a select to the header with an inner join... Something like this: C/EXEC SQL C+ INSERT INTO POE350WK C+ SELECT DCPLNT C+ FROM OCRH C+ INNER JOIN C+ ( C+ SELECT DDCUSR, DDORD#, C+ SUM(DDQTOI) as units, C+ COUNT(DDORD#) AS NUMORD, C+ COUNT(*) AS lines, C+ FROM OCRI C+ WHERE DDODAT >= :BEGdate AND DCODAT < :ENDdate and C+ DDPART <> ' ' C+ GROUP BY DDCUSR C+ ) C+ ON DCORD# = DDORD# C/END-EXEC Comments?
    165 pointsBadges:
    report
  • carlosdl
    The statement was good, but incorrect totals were generated I suppose you mean that the statement didn't have syntax errors, but it is incorrect as incorrect results are being generated. Comments? Well, without knowing why incorrect totals are being generated, it is difficult to tell if the statement will solve the problem. Many times when someone needs to add the DISTINCT keyword to get the results he/she expects is because of incorrect join or where conditions. OTOH, the last statement you posted seems to insert a different set of values into a different table from different tables than the ones used in your original question. Are we still talking about the same problem ?
    65,110 pointsBadges:
    report
  • Nickhutcheson
    Yes, only the names have changed to protect the innocent.... Same concept, join the header to the detail by order#. Subset of data from the original sql (with the generic names); Entered By # Orders # Units ABLE 154 550 BETTY 4 10 CRAIG 418 2273 DAVID 8 80 2 ETHEL 3 6 GEORGE 52 66 HARRY 2 81 3 INGRID 1256 3516 JULIE 27 2369 LUCY 17 57269 10 Out to the right side, I have posted actual number of orders entered by the Rep. As you can see, the sql results show that David entered 8 orders for the month, where he really only entered 2 orders. Each order had 4 line items. The issue is the count of the orders from the header file. Here is a link to the mentioned document I found: http://www.sqlteam.com/article/how-to-use-group-by-in-sql-server I know this kind of thing can be done with SQL, I just haven't found the way to do it. I would like to get a good working model to use in the future at least. Thanks, Nick
    165 pointsBadges:
    report
  • carlosdl
    In this case, you shouldn't need a subquery. a COUNT(DISTINCT HORD#) should suffice.
    65,110 pointsBadges:
    report
  • philpl1jb
    That still doesn't work with the subselect for this case all you need is: C/EXEC SQL C+ INSERT INTO POE350WK C+ SELECT DDCUSR, C+ SUM(DDQTOI) as units, C+ COUNT(DISTINCT DDORD#) AS NUMORD,, C+ FROM OCRI C+ WHERE DDODAT >= :BEGdate AND DCODAT < :ENDdate and C+ DDPART <> ‘ ‘ C+ GROUP BY DDCUSR C+ ) C/END-EXEC The outer select would create a row for each order and your file would just contain the three fields.
    48,575 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