sql insert into question - IBM AS400

This is a discussion on sql insert into question - IBM AS400 ; I have a file called lets say A with 50 records, out of this file there are 5 fields that I need to duplicate but I need to change one field which is profile. Could I declare the file in ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: sql insert into question

  1. sql insert into question

    I have a file called lets say A with 50 records, out of this file
    there are 5 fields that I need to duplicate but I need to change one
    field which is profile. Could I declare the file in ILERPG and then
    use SQL to add the records to the file using SQL INSERT?? I could
    find the 5 based on profle being equal to ID scenario and then insert
    accordingly. I am trying to find the easiest way to do this.


  2. Re: sql insert into question

    iseriesflorida wrote:
    > I have a file called lets say A with 50 records, out of this file
    > there are 5 fields that I need to duplicate but I need to change one
    > field which is profile. Could I declare the file in ILERPG and then
    > use SQL to add the records to the file using SQL INSERT?? I could
    > find the 5 based on profle being equal to ID scenario and then insert
    > accordingly. I am trying to find the easiest way to do this.
    >


    You don't need to use an RPG file specification statement.

    Let's say there are five fields, one of which (field1) is profile, and
    you want to insert a new record for user JONES based on the existing
    record for profile SMITH. Then do this:

    insert into A
    select 'JONES', field2, field3, field4, field5
    from A
    where field1 = 'SMITH'

  3. Re: sql insert into question

    On Oct 16, 11:29*am, Jonathan Ball wrote:
    > iseriesflorida wrote:
    > > I have a file called lets say A with 50 records, out of this file
    > > there are 5 fields that I need to duplicate but I need to change one
    > > field which is profile. Could I declare the file in ILERPG and then
    > > use SQL to add the records to the file using SQL INSERT?? *I could
    > > find the 5 based on profle being equal to ID scenario and then insert
    > > accordingly. *I am trying to find the easiest way to do this.

    >
    > You don't need to use an RPG file specification statement.
    >
    > Let's say there are five fields, one of which (field1) is profile, and
    > you want to insert a new record for user JONES based on the existing
    > record for profile SMITH. *Then do this:
    >
    > insert into A
    > select 'JONES', field2, field3, field4, field5
    > * *from A
    > * where field1 = 'SMITH'


    Could you get fancy and pass a parm to field1??

  4. Re: sql insert into question

    iseriesflorida wrote:
    > On Oct 16, 11:29 am, Jonathan Ball wrote:
    >> iseriesflorida wrote:
    >>> I have a file called lets say A with 50 records, out of this file
    >>> there are 5 fields that I need to duplicate but I need to change one
    >>> field which is profile. Could I declare the file in ILERPG and then
    >>> use SQL to add the records to the file using SQL INSERT?? I could
    >>> find the 5 based on profle being equal to ID scenario and then insert
    >>> accordingly. I am trying to find the easiest way to do this.

    >> You don't need to use an RPG file specification statement.
    >>
    >> Let's say there are five fields, one of which (field1) is profile, and
    >> you want to insert a new record for user JONES based on the existing
    >> record for profile SMITH. Then do this:
    >>
    >> insert into A
    >> select 'JONES', field2, field3, field4, field5
    >> from A
    >> where field1 = 'SMITH'

    >
    > Could you get fancy and pass a parm to field1??


    Of course. The parameter is an RPG variable. In fact, you could use
    two parameters: one for the new profile and one for the existing
    profile. In RPG IV notation (but not necessarily correct spacing):


    d NewProfile s 10
    d OldProfile s 10

    c/exec sql
    c+ insert into A
    c+ select :NewProfile, field2, field3, field4, field5
    c+ from A
    c+ where field1 = :OldProfile
    c/end-exec


    Note that if the value contained in OldProfile doesn't exist in the
    file, no row will be inserted. You could check field SQLSTT (SQL state)
    for value '02000' to see if no row matching the value in OldProfile was
    found (or value '00000' if a normal result, i.e. row was inserted).
    You'd have to check for some other value if you tried to insert a
    NewProfile value that already existed and there was a unique constraint
    on the file.

+ Reply to Thread