Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Insert Into .. (select .. Order By) Giving Error

    Hi Guys

    Following statement working fine without any error.

    insert into empdept(select empno,ename,job,dname,dept.deptno
    from emp,dept
    where emp.deptno = dept.deptno)

    If I add ORDER BY empno to above select statement returning "missing right parenthesis" error.

    insert into empdept(select empno,ename,job,dname,dept.deptno
    from emp,dept
    where emp.deptno = dept.deptno
    ORDER BY empno)
    returning ORA-00907 missing right parenthesis.

    Is it valid statement, does oracle support order by clause with INSERT..SELECT ..ORDER BY statement.

    Please let me know ASAP.


    Thanks
    Ramesh

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    By what purpose is ORDER BY useful or valid on INSERT?
    Within an RDBMS one row has no inherent relationship to another row.
    Any ORDER BY depends upon which column is specified in the SELECT only.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Location
    Belgium
    Posts
    25
    Hi,

    You cannot specify an ORDER BY clause in the select when you insert in a table.
    If you really want to have ordered, then create a temporary view and insert in the table from this view instead of the table.

    Normally, you didn't receive anymore this error.

    Regards,
    Xavier

  4. #4
    Join Date
    Oct 2005
    Posts
    4
    Thanks for your suggestion.

    Ramesh

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    use indexes for ordering.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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