• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • The Powerful SQL Upsert

    January 27, 2015 Ted Holt

    As you well know, a common task in business computing is to update an entity (customer, vendor, purchase order line, etc.) that exists, but add the entity if it doesn’t exist. In RPG this requires two operations–an update and a write–within a conditional statement. In SQL one statement handles the whole shebang. Here’s how it works.

    Here’s some RPG III code that illustrates the situation.

    FCUSTF   UF  E           K        DISK
     . . .  code omitted
    C           CUSKEY    KLIST
    C                     KFLD           COMP
    C                     KFLD           ACCT
    C*
     . . .  more code omitted
    C*                                              HILOEQ
    C           CUSKEY    CHAINCUSTREC              99
    C*
     . . 
    …

    Read more
  • Everybody Likes Shortcuts! Part 2, Playing With Blocks

    January 27, 2015 Susan Gantner

    In my last tip on RSE shortcuts, I talked about shortcuts that help you find your way as you navigate through source members. Eventually, you find the place where you need to work with the code. So in this tip, I’ll concentrate on shortcuts to help with that, and specifically working with blocks of code.

    First let’s talk about selecting a block of code. There are many ways: You could drag your mouse over the block or hold down the Shift key and use the cursor arrow down key to select the lines for the block. The one I’ve

    …

    Read more
  • Faking Create Or Replace Table

    January 20, 2015 Ted Holt

    The new CREATE OR REPLACE feature of SQL has been most helpful to me. It works for aliases, functions, masks, permissions, procedures, sequences, triggers, variables and views. It would be nice if it worked for tables, especially when I’m developing a new application. Here’s a workaround.

    The trick is to use a dynamic compound statement. If you’re not familiar with these, I recommend you read Michael Sansoterra’s excellent article on that subject.

    One of the things that dynamic compound statements let you do is include conditional logic, which is what we need. We need a way to drop a table

    …

    Read more
  • Regurgitating Data With DB2 For i Data Change Table References

    January 20, 2015 Hey,

    Four Hundred Guru:

    I’ve been reading your SQL articles in hopes of finding a solution to a problem. I need to insert the same data into two tables at the same time in detail form to one table and in summary form to the other. Is such a thing possible?

    I have an incoming transaction table that I want to insert into another table. The incoming transaction table has multiple columns and some of those columns are quantities (integer) fields or amount (with a decimal) fields. I want SQL to run an INSERT INTO… SELECT FROM type statement to

    …

    Read more
  • Everybody Likes Shortcuts! Part 1, Navigation

    January 20, 2015 Susan Gantner

    I’ve seen a particularly sharp increase in the use of RDi (or Rational Developer for i) by RPGers this year. I suspect that has a lot to do with the fact that it contains the only editor that supports the new V7 all-free-format version of the RPG language. Even among shops that are not planning to make use of free form D specs right away, this limitation seems to have made people realize that the writing was on the wall and that SEU’s day had come and gone.

    One way that I gauge the interest in RDi is in the

    …

    Read more

Next Articles

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle