CODEWITHSUNDEEP
oracle-database
DWrath299
DWrath299

Reputation: 3

Oracle Invalid Identifier Error: ORA-00904 In inserting subquery

I can't figure out what is the problem that is causing it. I have tried renaming "amount" I have tried changing what data type is transferred through, I tried just removing it, but then it says that End_Date is invalid once I do that. Here is the code.

INSERT INTO price
( price_id
, item_id
, price_type
, active_flag
, start_date
, end_date
, amount
, created_by
, creation_date
, last_updated_by
, last_updated)

(SELECT price_s1.nextval
,      il.Item_ID
,      il.Price_Type
,      il.Active_Flag
,      il.Start_Date
,      il.End_Date
,      il.amount
,      1
,      SYSDATE
,      1
,      SYSDATE 
FROM    (SELECT i.item_id AS "Item_ID"
        , af.active_flag AS "Active_Flag"
        , cl.common_lookup_id AS "Price_Type"
        , cl.common_lookup_type AS "Price_Desc"
        ,    CASE    
             WHEN (TRUNC(SYSDATE) - 30) > TRUNC(i.release_date) THEN TRUNC(i.release_date) + 31
             ELSE TRUNC(i.release_date)
             END AS "Start_Date"
        ,    CASE
             WHEN (TRUNC(SYSDATE)-30) > TRUNC(i.release_date) AND active_flag = 'N' THEN
             TRUNC(i.release_date) + 30
             ELSE   
                null
             END AS "End_Date"
        ,    CASE
                 WHEN active_flag = 'N' AND cl.common_lookup_type = '1-DAY RENTAL' THEN 3
                 WHEN active_flag = 'N' AND cl.common_lookup_type = '3-DAY RENTAL' THEN 10
                 WHEN active_flag = 'N' AND cl.common_lookup_type = '5-DAY RENTAL' THEN 15
                 WHEN i.release_date+1 > (SYSDATE-1) AND cl.common_lookup_type = '1-DAY RENTAL' THEN 3
                 WHEN i.release_date+1 > (SYSDATE-1) AND cl.common_lookup_type = '3-DAY RENTAL' THEN 10
                 WHEN i.release_date+1 > (SYSDATE-1) AND cl.common_lookup_type = '5-DAY RENTAL' THEN 15
                 WHEN active_flag = 'Y' AND i.release_date +1 < (SYSDATE-1) THEN cl.common_lookup_code
             END AS "amount"

             FROM     item i CROSS JOIN
                (SELECT 'Y' AS active_flag FROM dual
                 UNION ALL
                 SELECT 'N' AS active_flag FROM dual) af CROSS JOIN
                (SELECT '1' AS rental_days FROM dual
                 UNION ALL
                 SELECT '3' AS rental_days FROM dual
                 UNION ALL
                 SELECT '5' AS rental_days FROM dual) dr INNER JOIN
                 common_lookup cl ON dr.rental_days = SUBSTR(cl.common_lookup_type,1,1)
        WHERE    cl.common_lookup_table = 'PRICE'
        AND      cl.common_lookup_column = 'PRICE_TYPE'
        AND NOT (active_flag ='N' AND (TRUNC(SYSDATE)-30) < TRUNC(i.release_date))
        ORDER BY 1, 2, 3) il);

I get the error at

,      il.amount

and this is what it says

Error report -
SQL Error: ORA-00904: "IL"."AMOUNT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Any help would be appreciated, if you need more info, just ask. Thanks

Upvotes: 0

Views: 1679

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

By using quotes for alias names you specify a particalur case sensitive name.

CASE ... END AS "amount"

specifies the alias name to be "amount" in lowercase. If you want to access it, use excatly the same spelling. E.g. "Amount" would be invalid.

You are trying to access

il.amount

and as there are no quotes, you say: "I don't care about upper/lower case". That works when accessing table and column names directly or when using an alias name without quotes. But as you used "amount" before, amount cannot be found (you were lucky if it would; I guess it could happen with an alias name with all upper chars, as Oracle uses upper chars for its names internally, but you wouldn't want to rely on this).

I suggest you just remove the quotes. Try to avoid them whenever possible. Use names that work without quotes (which you are actually already doing; none of your alias names contains any blank, leading digit or whatever).

Upvotes: 2

Related Questions