Re: Similar to alias, but to be reusable in all clauses of SQL: SELECT

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 18 Jan 2007 08:14:45 -0800
Message-ID: <1169136883.359443@bubbleator.drizzle.com>

qazmlp1209_at_rediffmail.com wrote:
> DA Morgan wrote:

>> Use a WITH query.

>
> I tried to use it. But, it does not seem to work for me. I tried for
> something similar to the following:
> -------
> WITH
> mycolumn as
> (SELECT substr(to_char(column-a_to_number(column-a)),1,17) from
> mytable)
> SELECT mycolumn, column-b from mytable where mycolumn=112020200000000;
> -------
>
> The following error is reported:
> -------
> ERROR at line 2:
> ORA-00904: "MYCOLUMN": invalid identifier
> -------
>
> Based on a short reading from
> http://www.dba-oracle.com/t_sql99_with_clause.htm, it looks like it is
> must to use atleast one aggregate function (E.g. sum()) at the WITH
> clause. My SQL statement might be failing due to this constraint. If
> yes, are there any other ways of fulfilling my requirement?

The SQL statement you wrote makes 'mycolumn' the equivalent of a table: Not a column. Look at this example posted by Michel Cadot a few months back:

INSERT INTO t2
WITH rn AS (

   SELECT rownum rn
   FROM dual
   CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1)) SELECT pname
FROM t1, rn
WHERE rn <= cases
ORDER BY pname;

Not "WITH rn" and later "FROM t1, rn"

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jan 18 2007 - 10:14:45 CST