Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem with Computed Column
"TheanKeong" <theankeong_at_gmail.com> wrote in message
news:1109565058.254124.119130_at_g14g2000cwa.googlegroups.com...
> Hi,
> I am a newbie in Oracle and I am currently facing a problem with
> migrating my SQL statement to Oracle. The statement looks like this
>
> create or replace view dbo."v_crm_events_detail_analysis" as
> (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,
>
> (case when Pd_type='C' then 'Customer'
> when Pd_Type='P' then 'Prospect' end) as Party_type,
>
> FROM dbo.Party
>
> I got an error indicating INVALID identifier PD_TYPE. The error happens
> in my Case Section. Is it that Oracle cannot identified COmputed
> Columns in such circumstance or I am doing things wrongly?
> Or is there any workaroung ?
[1] you're getting invalid identifier because you need to specify the table
alias, i.e. Pd_Type.Pd_type
[2] better off to use the DECODE function instead of case as it's simpler:
DECODE(Pd_Type.pd_type,'C','Customer','P','Prospect')
[3] you should probably be using a join and not sub-selects
[4] supply your table definitions next time, it makes sorting these things
out much easier
Received on Mon Feb 28 2005 - 03:01:13 CST