Re: column aliases in views

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. Received on Thu Jan 11 2007 - 06:45:24 CST