Results 1 to 7 of 7

Thread: SQL CRTDUPOBJ

  1. #1
    Senior Member ekempter's Avatar
    Join Date
    Mar 2000
    Location
    San Francisco, CA
    Posts
    1,064

    SQL CRTDUPOBJ

    What is the equivalent of CRTDUPOBJ in OS/400 SQL? The iSeries doesn't appear to support temporary tables (meaning that it won't create the table on the fly (JIT) as in INSERT INTO temp_table 2 SELECT * FORM original_table. Thanks in advance for any assistance.

  2. #2
    New Member
    Join Date
    Mar 2005
    Location
    Louisville, KY
    Posts
    13

    Re: SQL CRTDUPOBJ

    ekempter:

    Well, if you are running your SQL statements interactively (meaning from STRSQL from the command line), you can press F13, select 1. Change session attributes and SELECT ouput "3=File" to store the results of your interactive select in a table defined on this screen.

    If you want to pass the command in an SQL, you could do the following:

    CALL QCMDEXC ("commandstring", LENGTH)

    Be sure to use 15,5 formatting for the length.

    For example, interactively from STRSQL, you could do the following:

    CALL QCMDEXC("wrkactjob",0000000009.00000)

    So, you could conceivably do a CRTDUPOBJ in the parameter of CALL QCMDEXC.

    Just my two cents worth.

    I am sure there is a better way.

    Roby
    Robert A. Wayne, Jr.
    Nexgen Software Technologies, Inc.
    wayne@nexgensoftware.com

    Xe SP23 HP-UX 11 Oracle 9i

  3. #3
    Senior Member ekempter's Avatar
    Join Date
    Mar 2000
    Location
    San Francisco, CA
    Posts
    1,064

    Re: SQL CRTDUPOBJ

    Thanks for your response. I am attempting to do this using a RUNSQLSTM. I am hoping that someone will be able to provide an SQL statement to achieve this.

  4. #4
    New Member
    Join Date
    Mar 2005
    Location
    Louisville, KY
    Posts
    13

    Re: SQL CRTDUPOBJ

    You shouldn't have any problem doing this in RUNSQLSTM, either.

    Create a source file member such as the following:

    *************** Beginning of data ***************
    0001.00 CALL QSYS.QCMDEXC('WRKACTJOB',0000000009.00000)
    ****************** End of data ******************
    Then simply run: RUNSQLSTM SRCFILE(lib/file) SRCMBR(mbr)

    Should work fine ;-).

    Here is a more complex example:

    *************** Beginning of data **************************************************
    0001.00 CREATE TABLE QTEMP.TEST1 (FIELD1 CHAR(25));
    0002.00 INSERT INTO QTEMP.TEST1 SELECT 'GARBAGE' FROM SYSIBM.SYSDUMMY1;
    0003.00 CALL QSYS.QCMDEXC('CRTDUPOBJ OBJ(TEST1) FROMLIB(QTEMP) OBJTYPE(*FILE) NEWOBJ(TES
    0004.00 T2)',0000000064.00000);
    0005.00 INSERT INTO QTEMP.TEST2 SELECT * FROM QTEMP.TEST1;
    ****************** End of data ************************************************** ***

    And run with....

    RUNSQLSTM SRCFILE(NEXGENRW/QTXTSRC) SRCMBR(TEST2) COMMIT(*NONE) NAMING(*SQL) OUTPUT(*PRINT)

    Of course, you can use *SYS naming (default) and use the same in your text file.

    Hope this helps.

    Roby
    Robert A. Wayne, Jr.
    Nexgen Software Technologies, Inc.
    wayne@nexgensoftware.com

    Xe SP23 HP-UX 11 Oracle 9i

  5. #5
    Senior Member ekempter's Avatar
    Join Date
    Mar 2000
    Location
    San Francisco, CA
    Posts
    1,064

    Re: SQL CRTDUPOBJ

    That should work! Thanks Rudy!

  6. #6
    Member Z_Cader's Avatar
    Join Date
    Mar 2002
    Location
    BC, Canada
    Posts
    77

    Re: SQL CRTDUPOBJ

    you can try this.

    1. create table lib/targettable like lib/sourcetable - This create an empty table
    2. insert into lib/targetable select * from lib/sourcetable - This will populate the targettable.
    Thanks,

    8.12/8.96,8.11/8.94.L1,8.9,ERP8,Xe,B7332, i5/iSeries V5R2/V5R3/i5, IXS, AXS, DB2/400, Oracle 8/9, Co-A73c12, Citrix, Win Websphere 5.0.2, i5/iSeries JAS, AIX, Unix, OAS 10g.

  7. #7
    Senior Member ekempter's Avatar
    Join Date
    Mar 2000
    Location
    San Francisco, CA
    Posts
    1,064

    Re: SQL CRTDUPOBJ

    I'll give that a try. Thanks Z.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.