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

Oracle version ?

A stab in the dark: does the replacement of the two "account" with NVL(get_value(hostname, '/system/accounting/name'), 'undefined') in the CASE clause help in correcting the error? Received on Thu Jan 11 2007 - 06:41:21 CST