Re: How to use UPPER in a view ??? (Scalar Character Functions)

From: Chris Hamilton <chrish_at_cmprime.att.com>
Date: 1997/03/26
Message-ID: <01bc3a0c$e92d2600$2d89b687@chrish-pc.cis.att.com>#1/1

Jens U. Veigel <jens_at_deutschware.com> wrote...

> The select statement works. When I try to do it in a view like below,
>
> CREATE VIEW stencil_p as
> select UPPER (slot_num), UPPER (apn_pb_num), UPPER
> (apn_pba_num), UPPER (pb_alias_num), UPPER (pba_alias_num), UPPER
> (dsr), thickness, UPPER (work_holder)
> from ssd.stencil
> where requirements ='P'
> and status = 'R'
>
> I get the following error:
>
> select UPPER (slot_num), UPPER (apn_pb_num) ........
> *
> ORA-00998: must name this expression with a column alias
>
> What does it mean ??? Thanks for taking the time to read it !!

When you create a view, the columns that make up the view must have valid names just like any column in a table. Otherwise, Oracle assumes that "UPPER(slot_num)" is the name of the column, which is an invalid name. There are two ways of doing this.

First way (alias the columns before the SELECT clause) . . .

create view stencil_p
  (slot_num, apn_pb_num, work_holder)
as select upper (slot_num),

              upper(apn_pb_num),
              upper(work_holder)

    from ssd.stencil
    where requirements = 'P'
    and status = 'R';

Second way (alias the columns in the SELECT clause) . . .

create view stencil_p
as select upper (slot_num) slot_num,

              upper(apn_pb_num) apn_pb_num,
              upper(work_holder) work_holder

    from ssd.stencil
    where requirements = 'P'
    and status = 'R'

Hope this helps!

Chris

---

Chris Hamilton -- chrish_at_cmprime.att.com DBA, AT&T WorldNet Service - Lincroft, NJ http://www.serve.com/cowpb/chamilton.html Received on Wed Mar 26 1997 - 00:00:00 CST