Re: column aliases in views

From: Filipe David Borba Manana <fdmanana_at_cern.ch>
Date: Thu, 11 Jan 2007 13:51:18 +0100
Message-ID: <eo5bs6$aoc$1@cernne03.cern.ch>

Tried this new version:

CREATE OR REPLACE VIEW vwaccounting
AS
SELECT hostname,

        NVL(get_value(hostname, '/system/accounting/name'), 'undefined') AS accountname,

        clustername,
        hwmodel,
        get_value(hostname, '/hardware/vendor') AS landbhw,
        NVL(NVL(get_value(hostname, '/hardware/harddisks/hda/capacity'), 
get_value(hostname, '/hardware/harddisks/sda/capacity')), 0) AS disksize,
        NVL(get_value(hostname, '/hardware/cpus/0/si2k') + 
get_value(hostname, '/hardware/cpus/1/si2k'), 0) AS power,
        state,
        CASE WHEN fngetwarrantyend(hostname) > current_date THEN 

accountname || ' total warr-in' ELSE accountname || ' total warr-out' END AS warranty
FROM CDB.VWHOST It also doesn't work :(
Same error: invalid identifer "accountname"

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