Oracle / PHP“无效标识符”错误

Question

I'm really not sure what's going on at this point. I'm trying to create a view, and when I run the query below:

$view = oci_parse($conn, '
                CREATE OR REPLACE VIEW 
                    V_ORD_HISTORY
                AS 
                SELECT
                    ARCUSTO.ID,
                    ORDERS.ID,
                    ORDERS.PONO,
                    ORDERS.ORDERNO,
                    ORDERS.DATE_TAKEN,
                    ORDERS.ARCUSTO_ID,
                    ORD_DETAIL.ORDERS_ID,
                    ORD_DETAIL.ID,
                    ORD_DETAIL.TOTAL_QTY_ORD,
                    ORD_DETAIL.CUMM_SHIPPED,
                    ORD_DETAIL.DISCOUNT,
                    ORD_DETAIL.UNIT_PRICE
                FROM
                    ARCUSTO a,
                    ORDERS  o,
                    ORD_DETAIL d,
                    ARINVT i
                WHERE
                    a.ID = o.ARCUSTO_ID
                AND
                    d.ORDERS_ID = o.ID
            ');

I get the error:

ORA-00904: "ORD_DETAIL"."UNIT_PRICE": invalid identifier

And if I try to delete ORD_DETAIL.UNIT_PRICE from the end of the select statement, then it tells me that ORD_DETAIL.DISCOUNT is now the invalid identifier. Whichever column is last in the list becomes the invalid identifier. If I remove the prefix to determine which table, then I get column ambiguously defined errors.

Any ideas??

EDIT 1

I've now changed the code to the following:

$view = oci_parse($conn, '
                CREATE OR REPLACE VIEW 
                    V_ORD_HISTORY
                AS 
                SELECT
                    a.ID as CUSTOMER_ID,
                    o.ID as ORD_ID,
                    o.PONO,
                    o.ORDERNO,
                    o.DATE_TAKEN,
                    o.ARCUSTO_ID,
                    d.ORDERS_ID,
                    d.ID as DETAIL_ID,
                    d.TOTAL_QTY_ORD,
                    d.CUMM_SHIPPED,
                    d.DISCOUNT,
                    d.UNIT_PRICE
                FROM
                    ARCUSTO a,
                    ORDERS  o,
                    ORD_DETAIL d,
                    ARINVT i
                WHERE
                    CUSTOMER_ID = o.ARCUSTO_ID
                AND
                    ORD_ID = d.ORDERS_ID
            ');

But now it's saying that ORD_ID is an invalid identifier. This its the last line of the SQL statment.

Answer 1

How about trying :

CREATE OR REPLACE VIEW 
                    V_ORD_HISTORY
                AS 
                SELECT
                    a.ID as CUSTOMER_ID,
                    o.ID as ORD_ID,
                    o.PONO,
                    o.ORDERNO,
                    o.DATE_TAKEN,
                    o.ARCUSTO_ID,
                    d.ORDERS_ID,
                    d.ID as DETAIL_ID,
                    d.TOTAL_QTY_ORD,
                    d.CUMM_SHIPPED,
                    d.DISCOUNT,
                    d.UNIT_PRICE
                FROM
                    ARCUSTO a,
                    ORDERS  o,
                    ORD_DETAIL d,
                    ARINVT i
                WHERE
                    a.id= o.ARCUSTO_ID
                AND
                    o.id = d.ORDERS_ID