Results 1 to 4 of 4

Thread: query questions

  1. #1
    Join Date
    Dec 2002
    Posts
    12

    Angry Unanswered: query questions

    Junior DB2 users with query questions.. TIA for anyone who answers!!


    (1) in Sybase or SQL-Server, it is easy to select records into a temporary work table without creating the temporary table before running the query, as below:

    select * into #temptable from tableA

    How can this be done in UDB?!


    (2) I was trying to do:

    insert into tableA select * from tableB order by col1

    Got an error complaining 'order'. Did I miss something in my SQL syntax?! or just simply that 'order by' can't be used in an 'Insert.. Select from' query?


    thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: query questions

    Originally posted by KenWhite

    (1) in Sybase or SQL-Server, it is easy to select records into a temporary work table without creating the temporary table before running the query, as below:

    select * into #temptable from tableA

    How can this be done in UDB?!

    You can use the common table construct:

    with temptable as (select ... from ...) select * from temptable, table2 ...

    In this case 'temptable' will only exist during execution of the statement.

    Alternatively you could create a summary table (materialized view):

    create table temptable as (select ... from...)




    (2) I was trying to do:

    insert into tableA select * from tableB order by col1

    Got an error complaining 'order'. Did I miss something in my SQL syntax?! or just simply that 'order by' can't be used in an 'Insert.. Select from' query?

    You can't use "ORDER BY..." there.

    Hope this helps.

    Nick

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605

    Re: query questions

    Hi,

    You have to use column names before using order by statement.

    Sample:
    insert into tableA select col1, col2, col3 from tableB order by col1

    Hope this helps,
    Grofaty

    Originally posted by KenWhite
    Junior DB2 users with query questions.. TIA for anyone who answers!!


    (1) in Sybase or SQL-Server, it is easy to select records into a temporary work table without creating the temporary table before running the query, as below:

    select * into #temptable from tableA

    How can this be done in UDB?!


    (2) I was trying to do:

    insert into tableA select * from tableB order by col1

    Got an error complaining 'order'. Did I miss something in my SQL syntax?! or just simply that 'order by' can't be used in an 'Insert.. Select from' query?


    thanks.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: query questions

    Originally posted by n_i

    You can't use "ORDER BY..." there.

    Originally posted by grofaty

    Sample:
    insert into tableA select col1, col2, col3 from tableB order by col1

    Actually, we're both wrong :-)

    I've just run this in v7.2 successfully:

    Code:
    insert into t2 select * from t1 order by f1

Posting Permissions

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