Re: Views (again) in 8.1.7

Hi,

Its an error not immediately obvious.

You created an alias for your field using quotes and LOWERCASE field name!
So, when you do a desc on your table, you'll get this.

SQL> set linesize 80
SQL> desc v_tot

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 FLD1                                               NUMBER
 tot                                                NUMBER

Therefore, when you do a normal selection, the Oracle data dictionary assumes an uppercase tot. So it never find it by default.

SQL> select fld1,tot from v_tot;
select fld1,tot from v_tot

            *
ERROR at line 1:
ORA-00904: invalid column name

If you do the following, it works.

SQL> select fld1, "tot" from v_tot;

      FLD1 tot
---------- ----------

         1          5
         4         11
         7         17

However, it is not normal or advisable to create field names in this manner so just recreate your view with the field alias not in quotes.

SQL> create view v_tot as select fld1,fld2+fld3 tot from table1;

Viola! ;-)

M

"Maarten" <mmg_brouwers_at_hotmail.com> wrote in message news:<oPzE8.31715$48.2542923_at_zwoll1.home.nl>...
> Hi All,
>
> I looked in the archives but found no solution for my simple problem.
> I receive an error when I select the TOTAL column from my view.
> How can I resolve this???
>
> create view v_tot as select fld1,fld2+fld3 "tot" from table1;
>
> select fld1,tot from v_tot;
>
> the error is on: tot
>
> All help is greatly appreciated.
> Maarten
Received on Thu May 16 2002 - 11:24:49 CEST