Good morning,
I created a SQL statement which gives the following error: ORA-00918: column ambiguously defined.
We would like to see the amount of the invoice from the table INVOICE_LINEITEM. this shows which cost line it is. Then we want to have a left join to see additional information that is sometimes added. This information is in the table INVOICE_LINEITEM_REMARK. This table needs to be linked to INVOICE_LINEITEM. This must be done through 2 keys:
- INVOICE_GID
- LINEITEM_SEQ_NO
These two keys must both be equal to each other from the 2 tables for a match
CREATE TABLE INVOICE_LINEITEM
(
INVOICE_GID varchar(255),
LINEITEM_SEQ_NO int,
FREIGHT_CHARGE int
);
CREATE TABLE INVOICE_LINEITEM_REMARK
(
INVOICE_GID varchar(255),
LINEITEM_SEQ_NO int,
REMARK_QUAL_IDENTIFIER varchar(255),
REMARK_TEXT varchar(255)
);
INSERT INTO INVOICE_LINEITEM (INVOICE_GID, LINEITEM_SEQ_NO, FREIGHT_CHARGE) VALUES ('RSK.23400422', '1', '362');
INSERT INTO INVOICE_LINEITEM (INVOICE_GID, LINEITEM_SEQ_NO, FREIGHT_CHARGE) VALUES ('RSK.22401674', '1', '200');
INSERT INTO INVOICE_LINEITEM (INVOICE_GID, LINEITEM_SEQ_NO, FREIGHT_CHARGE) VALUEs ('RSK.22401770', '2', '450');
INSERT INTO INVOICE_LINEITEM_REMARK (INVOICE_GID, LINEITEM_SEQ_NO, REMARK_QUAL_IDENTIFIER, REMARK_TEXT) VALUES ('RSK.23400422', '1', 'ADD_INFOS' , 'CANCELLATION 512');
Result the query should give:
INVOICE_GID, FREIGHT_CHARGE, EXTRA_INFO
RSK.23400422, 362, CANCELLATION 512
RSK.22401674, 200,
RSK.22401770, 450,
The current query that gives the error:
SELECT
IL.INVOICE_GID,
TO_CHAR(
IL.FREIGHT_CHARGE, '999G999D99',
'NLS_NUMERIC_CHARACTERS = '',.'''
) AS "FREIGHT_CHARGE",
CASE WHEN ILR.REMARK_TEXT IS NULL THEN ' ' ELSE ILR.REMARK_TEXT END AS "EXTRA_INFO"
FROM
INVOICE_LINEITEM IL
LEFT JOIN INVOICE_LINEITEM_REMARK ILR ON (
IL.INVOICE_GID = ILR.INVOICE_GID
AND IL.LINEITEM_SEQ_NO = ILR.LINEITEM_SEQ_NO
)