Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6HDoWa56686 for ; Tue, 17 Jul 2001 09:50:32 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6DH0la59101 for ; Fri, 13 Jul 2001 13:00:47 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f6DGXQ907108 for pgsql-sql@postgresql.org; Fri, 13 Jul 2001 12:33:26 -0400 (EDT) (envelope-from news) From: mrink@avaya.com (Matt Rinkenberger) X-Newsgroups: comp.databases.postgresql.sql Subject: using custom column names in a group by clause Date: 13 Jul 2001 09:33:23 -0700 Organization: http://groups.google.com/ Lines: 26 Message-ID: <3612a608.0107130833.58902406@posting.google.com> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200107/220 X-Sequence-Number: 4016 The following select sets two custom column names using the same original column name. When I try to include a custom column name in a group by clause I get ORA-00904: invalid column name from Oracle. How can I access a column name I created in the group by clause? Here's the SQL: DISP is the column causing the error select userid,matlid,wsid, substr(note,instr(note,'Status') + 8,(instr(note,',',1,3) - (instr(note,'Status') + 8))) as DISP, sum(substr(note,instr(note,'Qty') + 5,(instr(note,',',1,1) - (instr(note,'Qty') + 5)))) as QTY, action from ise_prodordlog where userid = '4500925' and action in ('CompleteItem','UndoCompleteItem') and actiontime >= to_date('06/20/2001 00:00:00','MM/DD/YYYY HH24:MI:SS') and actiontime < to_date('06/30/2001 00:00:00','MM/DD/YYYY HH24:MI:SS') group by userid,matlid,wsid,DISP,action order by userid,matlid,wsid,DISP,action; Matt Rinkenberger Avaya, Inc. mrink@avaya.com