I have done a create or replace view called creditcard1. If I do a "select * from creditcard1" it retrieves the data just fine. If I try to do a statement where I am listing the column names it doesn't recognize them.
create or replace view creditcard1 as
select
pidm
,tbraccd_term_code as "Term"
,tbraccd_detail_code as "Detail_Code"
,tbbdetc_desc as "Detc_Desc"
,tbraccd_tran_number as "Tran_Number"
,DECODE(tbbdetc_type_ind, 'C', tbraccd_amount, 0.00) as "Charge_Amount"
,DECODE(tbbdetc_type_ind, 'P', tbraccd_amount, 0.00) as "Payment_Amount"
,tbraccd_activity_date as "Activity_Date"
,tbraccd_desc as "TBRACCD_DESC1"
,MAX (tbracct.TBRACCT_DETC_EFF_DATE)
KEEP (DENSE_RANK LAST order by (tbracct.TBRACCT_DETC_EFF_DATE)) max_detc_eff_date
from creditcard, tbraccd, tbbdetc, tbracct
where pidm = tbraccd_pidm
and tbr_chg_tran_num = tbraccd_tran_number
and tbbdetc_detail_code = tbraccd_detail_code
and pidm = '20'
and tbraccd_detail_code = tbracct_detail_code
group by
pidm
,tbraccd_term_code
,tbraccd_detail_code
,tbbdetc_desc
,tbraccd_tran_number
,DECODE(tbbdetc_type_ind, 'C', tbraccd_amount, 0.00)
,DECODE(tbbdetc_type_ind, 'P', tbraccd_amount, 0.00)
,tbraccd_activity_date
,tbraccd_desc
order by pidm, tbraccd_tran_number
Then I try to run:
create or replace view creditcard2 as
select
creditcard1.pidm
,creditcard1.Term
,creditcard1.Detail_Code
,creditcard1.Detc_Desc
,creditcard1.Tran_Number
,creditcard1.Charge_Amount
,creditcard1.Payment_Amount
,creditcard1.Activity_Date
,creditcard1.TBRACCD_DESC1
,creditcard1.max_detc_eff_date
,tbracct_b_fund_code as "Charge_Fund"
,tbracct_b_orgn_code as "Charge_Orgn"
,tbracct_b_acct_code as "Charge_Acct"
from creditcard1, tbracct
where creditcard1.Detail_Code = tbracct_detail_code
and creditcard1.max_detc_eff_date = tbracct_detc_eff_date
and get this error message:
ORA-00904: "CREDITCARD1"."DETAIL_CODE":invalid identifier
I have checked the spelling - in fact I just did a cut and paste so there wouldn't be spelling errors. When I do the "select * from creditcard1" it lists the column name of "Detail_Code" - the same column that the error message says doesn't exist. If I try just a select statement instead of "create or replace view creditcard2 as", I get the same error. The creditcard1 view is coming from a view I did called creditcard and it didn't have any problems recognizing the column names from the original view of creditcard.
If I comment out the where clause, it just gives the same error on a different column name - if I comment out that column name, then it errors on the next column name.
I am using SQL through TOAD against an Oracle 9i database.
Any ideas on why it won't recognize the columns from the view I created?
I am doing the creditcard2 view to pull in additional data for tbracct_b_fund_code, tbracct_b_orgn_code and tbracct_b_acct_code because I was getting multiple rows in the results that I didn't want. In the table TBRACCT there can be multiple rows per tbracct_detail_code. I only want to choose data from the row with the most recent tbracct_detc_eff_date. The MAX statement does that, but if I also select tbracct_b_fund_code in the same statement, it pulls a row for each different value that exists in tbracct_b_fund_code (even if the tbracct_detc_eff_date is not the most recent). So - if there was some way to code the creditcard1 view to also include tbracct_b_fund_code, tbracct_b_orgn_code and tbracct_b_acct_code without getting the duplicate data that would be helpful also.
Thanks,
Wendy Hope