Re: Problem with Computed Column

TheanKeong wrote:

> 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 ?
>
> Thanks
> Kwan

If you don't have a copy of Tom Kyte's book "Expert one-on-one Oracle" this would be a good time to get it. Pay special attention to the first three chapters.

This portion of what you wrote makes no sense.

where ceh_id=ced_ceh_id) and pd_type in('C','P')) as Pd_Type,

How can you alias something in the WHERE clause? What would it mean?

(case when Pd_type='C' then 'Customer'

          when Pd_Type='P' then 'Prospect' end) as Party_type,

FROM dbo.Party

CASE? CASE without context. Should this be a SELECT? And if not the alias, once again, is without context.

I would suggest that whatever product you think you know or are trying to migrate from is substantially unlike Oracle. Either post the original or spend some time at http://tahiti.oracle.com and learn the SQL syntax for Oracle.

PS: Without a version number any help you receive will be at best a guess.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)