| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: column aliases in views
Filipe David Borba Manana wrote:
> Charles Hooper wrote:
> > Filipe David Borba Manana wrote:
> >> I'm trying to create this view:
> >>
> >> CREATE OR REPLACE VIEW vwaccounting
> >> (
> >> hostname,
> >> accountname,
> >> clustername,
> >> hwmodel,
> >> landbhw,
> >> disksize,
> >> power,
> >> state,
> >> warranty
> >> )
> >> AS
> >> SELECT hostname,
> >> NVL(get_value(hostname, '/system/accounting/name'), 'undefined')
> >> AS account,
> >> clustername,
> >> hwmodel,
> >> get_value(hostname, '/hardware/vendor'),
> >> NVL(NVL(get_value(hostname, '/hardware/harddisks/hda/capacity'),
> >> get_value(hostname, '/hardware/harddisks/sda/capacity')), 0),
> >> NVL(get_value(hostname, '/hardware/cpus/0/si2k') +
> >> get_value(hostname, '/hardware/cpus/1/si2k'), 0),
> >> state,
> >> CASE WHEN fngetwarrantyend(hostname) > current_date THEN account
> >> || ' total warr-in' ELSE account || ' total warr-out' END
> >> FROM CDB.VWHOST
> >>
> >>
> >> But I always get the error which says that "account" is an invalid
> >> identifier. I also tried to use the identifier "accountname", defined in
> >> the view's header, and I got the same error.
> >>
> >> Is it not possible to use column aliases in a view definition?
> >>
> >> many thanks
> >
> > A quick check:
> > SELECT
> > *
> > FROM
> > V$RESERVED_WORDS
> > WHERE
> > KEYWORD='ACCOUNT';
> >
> > KEYWORD LENGTH RESERVED RES_TYPE RES_ATTR RES_SEMI DUPLICATE
> > ACCOUNT 7 N N N N N
> >
> > It is generally a good idea to avoid using keywords listed in the
> > V$RESERVED_WORDS view for table, view, column names, or other objects
> > or for as aliases. If you remove all instances of the word "ACCOUNT"
> > in the view definition, do you still receive an error when using
> > "accountname"?
> >
> > It appears as though the view will retrieve 10 columns, but you have
> > named only 9. Does this work?:
> > CREATE OR REPLACE VIEW vwaccounting
> > AS
> > SELECT
> > hostname,
> > NVL(get_value(hostname, '/system/accounting/name'), 'undefined')
> > accountname,
> > clustername,
> > hwmodel,
> > get_value(hostname, '/hardware/vendor') landbhw,
> > NVL(NVL(get_value(hostname, '/hardware/harddisks/hda/capacity')
> > disksize,
> > get_value(hostname, '/hardware/harddisks/sda/capacity')), 0) power,
> > NVL(get_value(hostname, '/hardware/cpus/0/si2k') +
> > get_value(hostname, '/hardware/cpus/1/si2k'), 0) state2,
> > state,
> > CASE WHEN fngetwarrantyend(hostname) > current_date THEN account || '
> > total warr-in' ELSE account || ' total warr-out' END warranty
> > FROM
> > CDB.VWHOST;
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.
>
>
> >
That is not the same SQL statement that I posted for creating the view. I believe that I missed the NVL(NVL( in one of the lines, and miscounted the number of columns being returned by the view. Please continue using YOUR view definition as shown above.
Does the column "accountname" exist in the table CDB.VWHOST? Very likely, the answer is no. You cannot use a column alias used for one of the columns in a formula for another column. Instead, you must use the unaliased version. For the last column, replace accountname with: NVL(get_value(hostname, '/system/accounting/name'), 'undefined')
(When responding to posts, please add your comments below all existing text in the message.)
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Jan 11 2007 - 07:10:15 CST
 |
 |