Re: Problem with Computed Column

Hi Haximus,

I have another View SQL which is a more simplified one compared to my previous example

create or replace view dbo.v_ar_sales_analysis as select jd_date as Invoice_Date,
NVL(jd_debit,0.0) as Debit_Amount,
NVL(jd_credit,0.0) as Credit_Amount,
(Credit_amount - Debit_Amount) as Calc_Amount -> Problem occurs in
this line
from dbo.jrn_dtl
where (jd_source='SJ' and(jd_debit is null or jd_debit=0)

This view is being simplified to focus on the problem. In my jrn_dtl table, I have columns of jd_debit,jd_credit,jd_date. The compute columns of Debit_Amount,Credit_Amount are derived using the NVL function. However when I try to derive the Calc_Amount column, I get the error of Invalid Identifier Debit_Amount.

However when i did the following, the SQL seems able to be executed

create or replace view dbo.v_ar_sales_analysis as select jd_date as Invoice_Date,
NVL(jd_debit,0.0) as Debit_Amount,
NVL(jd_credit,0.0) as Credit_Amount,
(NVL(jd_credit,0.0) - NVL(jd_debit,0.0)) as Calc_Amount -> not refering
derived column
from dbo.jrn_dtl
where (jd_source='SJ' and(jd_debit is null or jd_debit=0)

I think the same theory might apply to my example earlier whereby the column which gives me the error is a derived column (or subselected from another table). I have some friends telling me that such columns cannot be referenced in Oracle Query.. How true is this.

If not, is there any workaround I need to do to make it work...

Can I mail u directly to better explain my problem. I am really facing this big issue here because most (60%) of the DB I am migrating from ASA is having such scenario not to mention most of our datawindows in PowerBuilder has subselects.

Pls advice
Kwan Received on Mon Feb 28 2005 - 04:43:41 CST