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

From: <qazmlp1209_at_rediffmail.com>
Date: 18 Jan 2007 07:58:57 -0800
Message-ID: <1169135937.484601.166510@m58g2000cwm.googlegroups.com>

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? Received on Thu Jan 18 2007 - 09:58:57 CST