Re: View and group by

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 13 Dec 1999 13:09:11 -0500
Message-ID: <1dda5s06uu10flku3m07tbru0b62338u84_at_4ax.com>

A copy of this was sent to "Michal Piasecki" <michal.piasecki_at_cr-media.com.pl> (if that email address didn't require changing) On Mon, 13 Dec 1999 17:33:09 GMT, you wrote:

>I have no Idea how to create view and keep all relations in my query
>I need to keep order in my result by the value of NM field so:
>
>CREATE VIEW "MICHAL".SOMEVIEW ( C1, C2 )

                               ^^^^^^^^^^ name the columns from the select, they

are BOTH NM - that is the problem. has nothing to do with group by...

>AS
>SELECT BRANZE.NM, PODBRANZE.NM
>FROM BRANZE, PODBRANZE
>WHERE (BRANZE.IDBRANZY=PODBRANZE.IDBRANZY)
>GROUP BY BRANZE.NM, PODBRANZE.NM
>
>Line 2 error - duplicate name of the column.
>
>- there's written in the Oracle8 Complete Reference that
>even if there are no group functions in the select clause
>, the group by can still consolidate rows so it generally
>should work as order but it's not. What can I do in
>that case?

beware == group by <> order by. for example:

SQL> create table emp as select * from scott.emp where rownum <= 5; Table created.

SQL> create index emp_idx on emp(job,ename); Index created.

SQL> select ename, job from emp where ename >chr(0) and job >chr(0)   2 group by ename, job ;

ENAME JOB
---------- ---------

smith      CLERK
jones      MANAGER
allen      SALESMAN
martin     SALESMAN
ward       SALESMAN

indexes, particular options, optimizer mode, NLS settings, many things can make it so group by <> order by as above.

>
>
>I appreciate any help.
>_________________________________________________
>Michal Piasecki
>CR Media
>tel: (+48)(022)690-68-62
>e:mail - michal.piasecki_at_cr-media.com
>

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Mon Dec 13 1999 - 19:09:11 CET