Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2002
    Posts
    6

    Unanswered: DB2 errors on this SQL idiom: insert into foo2 select * from foo order by bar;

    DB2 SQL gives an error on this idiom:

    insert into foo2 select * from foo order by bar;

    A script that shows the error would be something like this:

    drop table foo;
    create table foo (bar int);
    insert into foo values (1);
    insert into foo values (10);
    insert into foo values (5);
    insert into foo values (2);
    insert into foo values (1);
    insert into foo values (6);
    insert into foo values (0);
    select * from foo order by bar;
    drop table foo2;
    create table foo2 (bar int);
    insert into foo2 select * from foo order by bar;
    select * from foo2;

    The error on the "insert into" is:

    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0104N An unexpected token "by" was found following "ect * from foo order".

    Expected tokens may include: "GROUP". SQLSTATE=42601

    What am I missing?

    John N.

  2. #2
    Join Date
    Mar 2002
    Posts
    6
    I should also say: The insert into . . . select * from . . . works fine without the "order by" clause.

    And the whole thing works just fine on Oracle and MySQL.

  3. #3
    Join Date
    Oct 2001
    Posts
    68
    Hello, It works fine here too. I'm using 7 on AIX and the command shell as follows:

    Code:
    $ db2 "create table foo (bar int)"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into foo values(1),(10),(5),(2),(1),(6),(0)"
    DB20000I  The SQL command completed successfully.
    $ db2 "select * from foo order by bar"                 0)"
    $ db2 "select * from foo order by bar"
    
    BAR
    -----------
              0
              1
              1
              2
              5
              6
             10
    
      7 record(s) selected.
    
    $ db2 "create table foo2 (bar int)"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into foo2 select * from foo order by bar"
    DB20000I  The SQL command completed successfully.
    $ db2 "select * from foo2 order by bar"
    
    BAR
    -----------
              0
              1
              1
              2
              5
              6
             10
    
      7 record(s) selected.
    
    $ db2level
    DB21085I  Instance "db2inst1" uses DB2 code release "SQL07024" with level
    identifier "03050105" and informational tokens "DB2 v7.1.0.60", "s020313" and
    "U481406".
    Are you trying to do this programatically? or from a command prompt/window because sometimes the "*" gets interpretted by the OS. Use " like I do above and you'll be fine.

    BTW I also confirmed operation when foo2 is created as follows:
    Code:
    $ db2 "create table foo2 like foo"
    DB20000I  The SQL command completed successfully.
    Hope this helps,

    WP

  4. #4
    Join Date
    Mar 2002
    Posts
    6
    Thanks, WPSullivan, but it still doesn't work on my system. Read on.

    This is on Windows 2K, DB2 7.1FP3 (i.e., 7.2). I was using the GUI "Command Center," but it also doesn't work from the command-line DB2 CLP with our without quotations. The error is exactly the same. I should try it programmatically from JDBC, which is what I'll try next.

    That's interesting that it works on AIX. Our AIX box is currently being re-built, but I will try it there once it's back up.

    It seems quite fishy that it would work on AIX but not NT.

    Another reader wrote to me privately and said that since the physical storage of rows is not under user control from SQL, the "insert... select... order by..." idiom shouldn't be guaranteed anyway. This is true, since the basis for the relational model is sets, and the items in the set have no order, which is why you are not guaranteed getting the rows back in any particular order for any given query.

    Even so, it's interesting that: (1) It does seem to work on AIX; (2) it's a normal idiom in Oracle and MySQL; (3) there's the remaining question of why this command:

    insert into foo select * from foo2 order;

    is even accepted, since the bare order at the end is not legal SQL.

    I should also note that the SQL syntax diagrams given in official IBM documentation suggests that that the query embedded in an insert is general. I cannot find any rule that says that it can't have an "order by" clause.

  5. #5
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    We can't use order by clause in the statement like the one which you have insert query.
    That is a sort of constraint on the select query to be used in a sub select of a insert statement.
    Prashant Dahalkar
    Prashant

  6. #6
    Join Date
    Oct 2001
    Posts
    68
    Gentlemen, Windows XP 7.2.4 Start -> Programs -> IBM DB2 -> Command Line Processor

    C:\PROGRA~1\SQLLIB\BIN>db2 connect to sample user db2admin using ********

    Database Connection Information

    Database server = DB2/NT 7.2.4
    SQL authorization ID = DB2ADMIN
    Local database alias = SAMPLE


    C:\PROGRA~1\SQLLIB\BIN>db2 "create table foo (bar int)"
    DB20000I The SQL command completed successfully.

    C:\PROGRA~1\SQLLIB\BIN>
    C:\PROGRA~1\SQLLIB\BIN>db2 "insert into foo values(1),(10),(5),(2),(1),(6),(0)"
    DB20000I The SQL command completed successfully.

    C:\PROGRA~1\SQLLIB\BIN>db2 "create table foo2 (bar int)"
    DB20000I The SQL command completed successfully.

    C:\PROGRA~1\SQLLIB\BIN>db2 "insert into foo2 select * from foo order by bar"
    DB20000I The SQL command completed successfully.

    C:\PROGRA~1\SQLLIB\BIN>db2 "select * from foo order by bar"

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.


    C:\PROGRA~1\SQLLIB\BIN>db2 "select * from foo2 order by bar"

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.


    C:\PROGRA~1\SQLLIB\BIN>
    As you can see the "idiom" works. Can you please try this in a command window and cut and paste the actual output for me to look at.

    WP

  7. #7
    Join Date
    Mar 2002
    Posts
    6
    WPSullivan -- what actual version of DB2 are you running? And Fix pack? 7.1FP2? 7.2? Etc.?

  8. #8
    Join Date
    Oct 2001
    Posts
    68
    This is the XP Pro output from last time. This DB2 has the latest FP6 applied to it, but I also ran your test at home. The results are attached.
    Database Connection Information

    Database server = DB2/NT 7.2.4
    SQL authorization ID = DB2ADMIN
    Local database alias = SAMPLE
    This is my development desktop Win 2K Pro:
    [code]
    P:\PROGRA~1\SQLLIB\BIN>db2 connect to sample user db2admin
    Enter current password for db2admin:

    Database Connection Information

    Database server = DB2/NT 7.2.3
    SQL authorization ID = DB2ADMIN
    Local database alias = SAMPLE


    P:\PROGRA~1\SQLLIB\BIN>
    P:\PROGRA~1\SQLLIB\BIN>db2 "create table foo (bar int)"
    DB20000I The SQL command completed successfully.

    P:\PROGRA~1\SQLLIB\BIN>db2 "insert into foo values(1),(10),(5),(2),(1),(6),(0)"

    DB20000I The SQL command completed successfully.

    P:\PROGRA~1\SQLLIB\BIN>db2 "create table foo2 (bar int)"
    DB20000I The SQL command completed successfully.

    P:\PROGRA~1\SQLLIB\BIN>db2 "insert into foo2 select * from foo order by bar"
    DB20000I The SQL command completed successfully.

    P:\PROGRA~1\SQLLIB\BIN>db2 "select * from foo order by bar"

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.


    P:\PROGRA~1\SQLLIB\BIN>db2 "select * from foo2 order by bar"

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.


    P:\PROGRA~1\SQLLIB\BIN>db2level
    DB21085I Instance "DB2" uses DB2 code release "SQL07023" with level identifier

    "03040105" and informational tokens "DB2 v7.1.0.55", "n011211" and "WR21294".


    P:\PROGRA~1\SQLLIB\BIN>ver
    Microsoft Windows 2000 [Version 5.00.2195]
    [/quote]

    Again, try to cut/paste your actual input/output and it might help.

    WP

  9. #9
    Join Date
    Apr 2002
    Posts
    1
    by needs to be proceeded by the keywork ORDER

  10. #10
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    i cut&paste the script and ran it on my test machine... and it works fine...

    here is the results:
    =======================================
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.

    DB20000I The SQL command completed successfully.
    DB20000I The SQL command completed successfully.

    BAR
    -----------
    0
    1
    1
    2
    5
    6
    10

    7 record(s) selected.

    =======================================

    i am using Microsoft Windows 2000 [Version 5.00.2195]
    and DB2 UDB v7.1 fp5

    i think fixpak 5 fixed this problem. this is from the APARLIST:


    IY25484 INSERT WITH SELECT WITH AN ORDER BY CLAUSED WAS NOT SUPPORTED. QP WAS INSERTING 1 ROW AT A TIME. VERY SLOW.


    cheers!
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  11. #11
    Join Date
    Apr 2002
    Posts
    13
    Hello,

    I think the basic problem here is the "*" specified in the SQL SELECT statement. On UNIX platforms (and I hope this is true with other O/S as well), "*" has special meaning. So you must enclose the complete SQL within double quotes ("") and it should solve the problem.


    Thanks
    Anil.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •