Re: Problem in Database Migration for Views

From: TheanKeong <theankeong_at_gmail.com>
Date: 7 Mar 2005 02:18:29 -0800
Message-ID: <1110190709.228249.291290@l41g2000cwc.googlegroups.com>

Hi Mark,
I am actually upgrading my DB from SyBase ASA to Oracle but since Oracle WorkBench tool only supports Sybase ASE, I need to convert the DB to ASE before going to Oracle. That is why my owner is DBO ...

Actually I am very new to Oracle, but I while finding workaround i noticed that Oracle only works for table selected columns and not derived columns.(in this case is Debit_Amount). I have been told from my friends that such derived columns cannot be referenced anywhere in the query. My thought is further strengthen when i rewrote my SQL to have only table selected columns to be referenced in my query.

create or replace view dbo."v_ar_sales_analysis" as select jd_date as Invoice_Date,
jd_comp_id as Company_ID,
(select comp_name from dbo.company where comp_id=jd_comp_id) as
Company_Name,

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 Amount
/*Instead of Credit_Amount - Debit_Amount*/

from dbo.jrn_dtl where((jd_source='SJ' and(jd_debit is null or jd_debit=0))

or(jd_source='DC' and(jd_debit is null or jd_debit=0))
or(jd_source='CC' and(jd_credit is null or jd_credit=0)))  and
jd_comp_id=any(select uc_comp_id from  dbo.user_company where

uc_au_cd='session_user');

the query workselect jd_date as Invoice_Date, jd_comp_id as Company_ID,
(select comp_name from dbo.company where comp_id=jd_comp_id) as
Company_Name,

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 Amount
/*Instead of Credit_Amount - Debit_Amount*/

from dbo.jrn_dtl where((jd_source='SJ' and(jd_debit is null or jd_debit=0))

or(jd_source='DC' and(jd_debit is null or jd_debit=0))
or(jd_source='CC' and(jd_credit is null or jd_credit=0)))  and
jd_comp_id=any(select uc_comp_id from  dbo.user_company where

uc_au_cd='session_user');

The query works fine ...
Can you confirm my thoughts or I am missing something else ...

Kwan Received on Mon Mar 07 2005 - 04:18:29 CST