Re: Problem with Computed Column

On 28 Feb 2005 01:00:44 -0800, "TheanKeong" <theankeong_at_gmail.com> wrote:

>Hi again,
>While searching for the solution i bumped thru this statement in one of
>Oracle related website
>
>alias - provides a different name for the column expression and causes
>the alias to be used in the column heading. The AS keyword is optional.
>The alias effectively renames the select list item for the duration of
>the query. The alias can be used in the ORDER BY clause, but not other
>clauses in the query
>
>could it be the source of my problem ?

Yeah, that's it. You can't refer to aliases in the same SELECT. But you can refer to aliases of an inline view from the outside (if that's an unfamiliar term: that is a select in the FROM list). So you could rewrite your query as:

create or replace view dbo."v_crm_events_detail_analysis" as select Pd_Type,
(case when Pd_type='C' then 'Customer'

          when Pd_Type='P' then 'Prospect' end) as Party_type from -- and here comes that inline-view
(select pd_type from dbo.party_dtl where pd_id=(select ceh_pt_id from dbo.crm_events_hdr where ceh_id=ced_ceh_id) and pd_type in('C','P')) as
Pd_Type,
FROM dbo.Party)

Jaap. Received on Tue Mar 01 2005 - 11:54:37 CST