ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

INSERT woes

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • INSERT woes

    I'm trying to populate file EXTRACT from file ITEMS, but only include active items that have a positive balance on hand in file INVENTORY. Files EXTRACT and ITEMS have identical fields since EXTRACT was created by copying ITEMS, but without copying data.

    INSERT into EXTRACT
    SELECT * from ITEMS a
    inner join
    INVENTORY b
    ON a.item# = b.item#
    WHERE a.IID = 'Active' AND
    b. ONHAND > 0 ;

    Running this SQL gives me error code SQL0117 Statement contains wrong number of values.

    Looking at details for SQL0117, it looks as if I may need to list all fields shared by files ITEMS & EXTRACT, but that is 340 fields to specify and I'm lazy enough that I'd rather not do that unless I have to. Is that my problem or is there something else I am missing or doing incorrectly?

  • #2
    Try this

    Code:
    insert into extract
    select * from items
    where iid = 'Active' and item# in (select item# from inventory where onhand > 0)
    ;
    As a note, the reason your sql doesn't work is because when you select * from joined tables, the sql engine selects the fields from all the tables in the join(s).

    ...so you could probably change your sql to the following and it should work
    Code:
    INSERT into EXTRACT
    SELECT a.* from ITEMS a
    inner join
    INVENTORY b
    ON a.item# = b.item#
    WHERE a.IID = 'Active' AND
    b. ONHAND > 0 ;
    Walt
    Last edited by wegrace; August 6, 2018, 03:39 PM.

    Comment


    • #3
      The problem is that the SELECT * selects all of the fields from ITEMS and INVENTORY - but you're only wanting fields from ITEMS. To stop this you qualify the * with the correlation value you used for ITEMS (a). By adding the 'a.' you tell SQL to only use the fields in ITEMS.

      Code:
      INSERT into EXTRACT 
      SELECT a.* from ITEMS a 
      inner join 
      INVENTORY b 
      ON a.item# = b.item# 
      WHERE a.IID = 'Active' AND 
      b. ONHAND > 0 ;

      Comment


      • #4
        Yes! That did it. Thank you Walt and Rocky.

        Comment

        Working...
        X