2

I am attempting to view a view with:

select * from DISCOUNT_AMOUNTS;
ERROR at line 1: 
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

I then check the view to see what is there in view.

DESC DISCOUNT_AMOUNTS;
 Name                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
 LINE_ITEM_AUDIT_ID                                                                        NOT NULL NUMBER(18)
 CREATED_DATE                                                                              NOT NULL DATE
 SALE_AMOUNT_AMOUNT                                                                                 NUMBER
 SALE_PROMPT_PAY_AMOUNT_AMOUNT                                                                      NUMBER
 SALE_QUANTITY_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_RETAILER_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_MANUFACTURE_AMOUNT_AMOUNT                                                                     NUMBER
 SALE_REBATE_AMOUNT_AMOUNT                                                                          NUMBER
 SALE_TRADE_IN_AMOUNT_AMOUNT                                                                        NUMBER
 SALE_REWARD_RETL_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_MANU_PROM_AMOUNT_A                                                                     NUMBER
 SALE_REWARD_CC_AMOUNT_AMOUNT                                                                       NUMBER
 SALE_GIFT_VOUCHER_AMOUNT_AMOUN                                                                     NUMBER

I then check it directly with single quotes of

select 'SALE_GIFT_VOUCHER_AMOUNT_AMOUN' from DISCOUNT_AMOUNTS;

which returns successfully.

I then attempt:

SQL> alter view DISCOUNT_AMOUNTS compile;
View altered.

Reattempt

select * from DISCOUNT_AMOUNTS;
ERROR at line 1:
ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier

This is not making sense to me. I can compile, it exists in view, but it won't select in wild character search. What else can I try? It is 30 characters as well and running command as user who created the view.

This is the create or replace view statement. Am on 12.1, which is 30 characters I believe. 12.2 is 128 for identifiers.


CREATE OR REPLACE VIEW DISCOUNT_AMOUNTS
AS
SELECT
LINE_ITEM_AUDIT_ID,
CREATED_DATE,
SALE_AMOUNT_AMOUNT,
SALE_PROMPT_PAY_AMOUNT_AMOUNT,
SALE_QUANTITY_AMOUNT_AMOUNT,
SALE_RETAILER_AMOUNT_AMOUNT,
SALE_MANUFACTURE_AMOUNT_AMOUNT,
SALE_REBATE_AMOUNT_AMOUNT,
SALE_TRADE_IN_AMOUNT_AMOUNT,
SALE_REWARD_RETL_PROM_AMOUNT_A,
SALE_REWARD_MANU_PROM_AMOUNT_A,
SALE_REWARD_CC_AMOUNT_AMOUNT,
SALE_GIFT_VOUCHER_AMOUNT_AMOUN
FROM
(SELECT LINE_ITEM_AUDIT_ID,
DISCOUNT_TYPE_ID,
DISCOUNT_AMOUNT,
CREATED_DATE
FROM LINE_DISCOUNT_AUDIT
) PIVOT ( SUM(DISCOUNT_AMOUNT) AS AMOUNT FOR (DISCOUNT_TYPE_ID) IN ( 
1 AS SALE_AMOUNT, 
2 AS SALE_PROMPT_PAY_AMOUNT, 
3 AS SALE_QUANTITY_AMOUNT, 
4 AS SALE_RETAILER_AMOUNT, 
5 AS SALE_MANUFACTURE_AMOUNT, 
6 AS SALE_REBATE_AMOUNT, 
7 AS SALE_TRADE_IN_AMOUNT, 
8 AS SALE_REWARD_RETL_PROM_AMOUNT, 
9 AS SALE_REWARD_MANU_PROM_AMOUNT, 
10 AS SALE_REWARD_CC_AMOUNT, 
11 AS SALE_GIFT_VOUCHER_AMOUNT));
  • Are there any column level permissions on the base table (or view)? – a_horse_with_no_name Nov 1 at 6:49
  • 1
    Show us the view definition. Did you enclose the table alias/name in quotes when you created the view, and if so was it lower/mixed case? – Philᵀᴹ Nov 1 at 8:32
  • 1
    What version of database server and client are you using? Column names were limited to 30 characters until very recently so it might be that an older /client/ might have problems with them. – Phill W. Nov 1 at 11:22
  • This works perfectly: dbfiddle.uk/… – Philᵀᴹ Nov 1 at 14:53
  • Is there another object called LINE_DISCOUNT_AUDIT in the database? There may be a synonym in play here. Recreate the view and qualify LINE_DISCOUNT_AUDIT with the schema name (eg: YOURUSER. LINE_DISCOUNT_AUDIT) and see if it fixes things – Philᵀᴹ Nov 1 at 14:55
1

Your test case works perfectly as-is: DB Fiddle

Is there another object called LINE_DISCOUNT_AUDIT in the database? There may be a synonym in play here. Recreate the view and qualify LINE_DISCOUNT_AUDIT with the schema name (eg: YOURUSER. LINE_DISCOUNT_AUDIT).

Not the answer you're looking for? Browse other questions tagged or ask your own question.