Re: column aliases in views

From: Filipe David Borba Manana <fdmanana_at_cern.ch>
Date: Thu, 11 Jan 2007 14:15:37 +0100
Message-ID: <eo5d9q$bh7$1@cernne03.cern.ch>

Charles Hooper wrote:

> 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.

>> 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"
>>
> 
> 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.
> 

Replacing accountname with
NVL(get_value(hostname,'/system/accounting/name'), 'undefined') works, although is not very elegant.

Thanks Received on Thu Jan 11 2007 - 07:15:37 CST