Unable to join two oracle tables

Question

i have 2 tables. First table SEC_SEAL_LOG with columns:

DATA_ADD,
DATA_AREA,
SEAL_NUMBER,
DATA_SEALING,
DATA_UNPLUG,
SORRUPTED.
SEC_WRITING_OFF_SEALS

second table with columns:

DATA, SEAL.

I want to put these 2 tables together, but I cannot understand where I have the error, I will be grateful for your help.

select DATA_ADD,
       DATA_AREA,
       SEAL_NUMBER,
       DATA_SEALING,
       DATA_UNPLUG,
       СORRUPTED,
       Data
from SEC_SEAL_LOG,SEC_WRITING_OFF_SEALS
where  (data_area = (select data_area 
                     from SEC_USERS_LIST 
                     where login = LOWER(:APP_USER) 
                     and SEAL_NUMBER = SEAL 
                    )
            or 20 >= (select u.role 
                      from SEC_users_list u 
                      where u.login = lower(:APP_USER)
                    )
        ) 
and СORRUPTED = 'Так'  
and SEAL_NUMBER = SEAL
ORDER BY  data_add DESC

I amd getting this error

ORA-20999: Failed to parse SQL query!
ORA-06550: line 7, column 4: ORA-00918: column ambiguously defined

If you are getting an Error, ALWAYS show us what the error is, all the error message please not a summary of it — RiggsFolly
You really should be using JOIN rather that from SEC_SEAL_LOG,SEC_WRITING_OFF_SEALS — RiggsFolly
" column ambiguously defined" That why you should always use fully qualified tables or use aliases in the SQL language in general... Or you write SELECT table_name.columns_name FROM table_name ("fully" qualified as the database name also can be included which makes it SELECT database_name.table_name.columns_name FROM table_name) or use SELECT alias_name.column_name FROM table_name AS alias_name (aliased) — Raymond Nijland
something like that yes, but i would also follow @RiggsFolly suggestion about using JOIN instead of the old comma JOIN.. Topicstarter i assume you didn't notice your SQL is a Cartesian Product between SEC_SEAL_LOG and SEC_WRITING_OFF_SEALS before that result is filterd ? i am pretty sure that query could be (re)written better if you share the data and expected results... See Why should I provide a Minimal Reproducible Example for a very simple SQL query? — Raymond Nijland

bk_32 bk_32 · Accepted Answer · 2019-10-09T14:43:42

The error Column Ambiguously Defined occurs when a column name is present in more than one table, and you have failed to specify which table.

You are doing that in this line: and SEAL_NUMBER = SEAL (which you have twice).

From which table to you want to compare that SEAL value?

Write it as SEC_SEAL_LOG.SEAL or SEC_WRITING_OFF_SEALS.SEAL or whatever table name you are trying to compare this value from, and it will get rid of the Column Ambiguously Defined error.

Unable to join two oracle tables

1 Answers