Using CTE with Update on the iSeries

15 pts.
Tags:
AS/400 SQL
iSeries
iSeries AS/400
Anyone have examples of using a common table expression with an update of fields in a file?

Software/Hardware used:
iSeries AS400 SQL

Answer Wiki

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

I don’t know how a CTE might be used effectively in an UPDATE statement. From the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/db2/rbafzcomtexp.htm”>V7.1 SQL Reference</a>:<ul>
<li>The table name of a common table expression can only be referenced in the select-statement, INSERT statement, or CREATE VIEW statement that defines it.</li>
</ul>
The same wording is in earlier manuals.

Tom

Discuss This Question: 9  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
  • DoneThat
    Yup - just tried it and can't do it. But as I was testing & thinking about how I would use it - it WOULD be a very useful capability. Gary
    830 pointsBadges:
    report
  • TomLiotta
    I agree that it might be handy, though I haven't actually come up with a solid example. Code a couple of hypothetical UPDATE statements that would seem to show how it really would be useful. Then see what kind of pseudo-code you can come up with that would make it really work. It ain't trivial. (But that's why we rely on vendors in the first place.) Tom
    125,585 pointsBadges:
    report
  • DoneThat
    I KNEW you would ask that & I tossed my little test code. Anyway, for example, say I want to update a field in each customer's record that represents the total % that customer's sales $ is of the salesperson's total. I.E. How important is THAT customer to the salesperson. This is on the fly - don't get too critical... WITH SalespersonSalesTotals AS ( SELECT Slsno AS SSTSlsno, SUM(cm_Totsales) AS SSTTotsales FROM lib.cusmas GROUP BY Slsno ) UPDATE lib.cusmas SET cm_slsno_pct = cm_Totsales / (SELECT SSTTotsales FROM SalespersonSalesTotals WHERE SSTSlsno = cm_slsno) WITH NC; I have production code that uses the traditional subquery method but I have a hunch this would be a lot faster if it was implemented. And a CTE could get a lot more complex than a subquery. Gary
    830 pointsBadges:
    report
  • Dhascuba
    You're all correct, it can't be done on the iSeries.....yet., thanks for all the responses......Don.
    15 pointsBadges:
    report
  • TomLiotta
    @DoneThat : I don't see where the CTE helps. It would seem simpler if you used the SELECT from the CTE directly in the SET clause, but changing the "Slsno AS SSTSlsno" result set column to a HAVING Slsno = cm_slsno clause. The UPDATE shouldn't need two SELECTs when one might work. The creation of an UPDATE statement that could include a CTE is one thing. But creating one that is less complex than the syntax that is already available is different. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I need to come back to this after I get some sleep. I have to be able to reply better than I did, but it is far too late on a Saturday night... Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    I knew I was misreading what you were doing, but I was too tired to catch where I was getting off track. What you showed would be satisfied by a nested table expression (NTE?) more like this:
    UPDATE lib.cusmas
        SET cm_slsno_pct =
            cm_Totsales /
                         (SELECT SSTTotsales
                             FROM (
                                    SELECT Slsno AS SSTSlsno, SUM(cm_Totsales) AS SSTTotsales
                                        FROM lib.cusmas
                                        GROUP BY Slsno
                                  )
                             WHERE SSTSlsno = cm_slsno)
    WITH NC;
    I can't actually run it because I don't have those tables. I ran one that had nearly the same structure to see how the references fit together. Some of why I missed it before is from not quite seeing what the point of the update was. That is, I might see why someone would run such a query, but I don't see why a derived value would be stored physically in the database when it becomes inaccurate as soon as the next sale is recorded. But that is a quibble that doesn't address the structure of the update. A different update over different values could be much more conceptually valid. That means that the update is a valid example. What's missing might be the setting of multiple columns by the NTE. Perhaps that's what the OP is looking for. A CTE doesn't seem very useful for an UPDATE unless it is referenced multiple times setting different columns. A NTE would need to be specified for each column that was updated while a CTE is specified only one for the entire statement. I suppose that's why it's a "common" table expression. Tom
    125,585 pointsBadges:
    report
  • DoneThat
    Tom What you showed would be satisfied by a nested table expression (NTE?)/i You bet - I have tons of those in production. Sometimes I've had to break those out into separate statements and create temp files due to performance issues (reminder: I'm on V5R2!!!) My experience has been that a single complex CTE runs like lightning compared to multiple NTEs (talking about SELECT now - obviously!) The feature that would really make "update with CTE" great is the ability to build all the fields you need to update with in 1 pass. For instance, suppose I needed SSTTotsales, SSTTotsalesNY, SSTTotsalesCT, etc. Multiple NTEs bog down. A single CTE would create all the aggregations in one pass. And I DO have need to do this quite often.
    830 pointsBadges:
    report
  • TomLiotta
    Multiple NTEs bog down. A single CTE would create all the aggregations in one pass. That's pretty much what I was getting at with bits like "unless it is referenced multiple times". It's essentially a way of describing the 'solid example' that I was looking for. (Of course, at V5R2, there's not much hope.) Thanks for helping solidify the concept. 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