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

From: William Robertson <williamr2019_at_googlemail.com>
Date: 18 Jan 2007 03:01:22 -0800
Message-ID: <1169118082.509986.50880@v45g2000cwv.googlegroups.com>

Brian Peasland wrote:
> qazmlp1209_at_rediffmail.com wrote:
> > I have to use only a converted form of a column value (E.g.
> > SUBSTR(TO_CHAR(my_function(Column-1)),1,17))) in all the clauses of my
> > SQL statement i.e. where, having, group by, order by clauses.
> > The statement looks ugly if I put the samething in al the places. How
> > can I make it to mention in only one place, so that I can reuse it in
> > other parts of the SQL statement. I suppose I cannot use the column
> > alias as it can be reused only at the Order by clause.
> >
> > A quick look at the PL/SQL tutorial did not fetch me anything related
> > to it. Can anybody help here?
> >
>
> Create a view then query from the view.
>
>
> HTH,
> Brian

Or use an inline view.

SQL> SELECT banana

  2       , LOWER(banana)
  3       , LENGTH(banana)
  4  FROM   ( SELECT RPAD(REPLACE('BNN','N','AN'),6,'A') banana
  5           FROM   dual )

  6 ORDER BY banana;

BANANA LOWER( LENGTH(BANANA)
------ ------ --------------
BANANA banana 6

1 row selected. Received on Thu Jan 18 2007 - 05:01:22 CST