CODEWITHSUNDEEP
sqloracle-database
ghalib
ghalib

Reputation: 21

Select error : ORA-00918: column ambiguously defined

I am trying to get some data from 2 synonyms by specific BENEFICIARY_CIVIL_NUMBER but I get an error

SELECT n.NAME_A, m.MOTHER_NAME_A
FROM S_S_CSPF_NRS_PERSON_V N, S_S_CSPF_NRS_MOTHER_V  M
WHERE BENEFICIARY_CIVIL_NUMBER = 1111111

Upvotes: 0

Views: 1940

Answers (4)

ghalib
ghalib

Reputation: 21

its working in this way thanks a lot guys 

 select n.NAME_A ,m.MOTHER_NAME_A from S_S_CSPF_NRS_PERSON_V n
     ,S_S_CSPF_NRS_MOTHER_V m  
where n.BENEFICIARY_CIVIL_NUMBER = 111111
     and M.BENEFICIARY_CIVIL_NUMBER= 1111111

Upvotes: 0

Igoranze
Igoranze

Reputation: 1486

Question: I am getting an ORA-00918 error on my SQL select statement that joins two tables. How do I correct this ORA-00918 error?
Answer: The Oracle docs note this on the ora-00918 error::\ ORA-00918 column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

When ORA-00918 is thrown, you have a column which has been ambiguously defined. If a column name in a join is referenced ambiguously, it exists in multiple tables.

Column names which occur in multiple tables should be prefixed when it is referenced by its table name. Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM . Oracle documentation which reference ORA-00918 give the following example: - If tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period) source

So, tho you define n.NAME_A, m.MOTHER_NAME_A with a prefix n or m, you don't define BENEFICIARY_CIVIL_NUMBER which probably causes the error.

To fix this, add a prefix to the BENEFICIARY_CIVIL_NUMBER!

Upvotes: 0

cipley
cipley

Reputation: 1112

My guess is that the BENEFICIARY_CIVIL_NUMBER column exists in both S_S_CSPF_NRS_PERSON_V and S_S_CSPF_NRS_MOTHER_V.

You need to specify it too. Either:

where n.BENEFICIARY_CIVIL_NUMBER = 1111111

or:

where m.BENEFICIARY_CIVIL_NUMBER = 1111111

Also, what is BENEFICIARY_CIVIL_NUMBER data type? Varchar?

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I think you need to use an alias with the BENEFICIARY_CIVIL_NUMBER column in your WHERE clause. I will assume that this column belongs to the S_S_CSPF_NRS_PERSON_V table, so the query should be:

SELECT n.NAME_A, m.MOTHER_NAME_A
FROM S_S_CSPF_NRS_PERSON_V n, S_S_CSPF_NRS_MOTHER_V m
WHERE n.BENEFICIARY_CIVIL_NUMBER = 1111111

You are currently doing a cross join between the two tables. If this is what you intended, then leave it as is. If you intended an INNER JOIN, then why not make it explicit:

SELECT n.NAME_A, m.MOTHER_NAME_A
FROM S_S_CSPF_NRS_PERSON_V n
INNER JOIN S_S_CSPF_NRS_MOTHER_V m
    ON n.col1 = m.col2
WHERE n.BENEFICIARY_CIVIL_NUMBER = 1111111

Upvotes: 2

Related Questions