ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Create File-Table "On-the-Fly" ?

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

  • Create File-Table "On-the-Fly" ?

    In MS SQL, I can create a new SQL table in a select statement:

    SELECT INTO NEW_TABLE
    from EXISTING TABLE
    WHERE FIELD_1 = 'A'

    MS SQL Dynamically creates the table with whatever fields are selected, giving them the same attributes.
    I find it helpful to quickly make a result table when troubleshooting, or testing/designing something pre-production.

    Is there an equivalent in IBMi SQL and/or through Navigator SQL ?

    Or do I have to spec it out through DDS first ?
    Last edited by MFisher; May 22, 2020, 09:05 AM.

  • #2
    I generally use the following
    PHP Code:
    create table libl/NEW_TABLE as (
       
    select from libl/EXISTING
       where FIELD_1 
    'A')
     
    with data 
    or the following if I just want a copy of the table in my dev library
    PHP Code:
    create table libl/NEWTAB like libl/OLDTAB 

    Comment


    • #3
      Thanks, Your first example works!. I usually want a table that has a subset of fields, or a mix of fields from multiple tables from a JOIN.

      A better example would be

      Code:
      SELECT A.F1, A.F2, B.F5, B.F6
      INTO NewTable
      From Table_A
      Join Table_B on A.Key = B.Key
      Where A.Code = '123'
      Last edited by MFisher; May 22, 2020, 09:51 AM.

      Comment


      • #4
        Definitely the "create table as... with data" in that case - you can use any kind of select statement and selecting specific fields instead of all will create the table with just the result fields.

        Very useful tool

        Comment

        Working...
        X