CODEWITHSUNDEEP
phpdatabaseoracle-databasejoin
Bibas
Bibas

Reputation: 538

Why does DBMS used with join causes Ambiguous column name?

I have this request in Oracle that is used by a PHP file

$pub_request = oci_parse($conn, "
    select * from TP2_PUBLICITE p, TP2_PUBLICITE_MOT_CLE m
    where
        p.NO_PUBLICITE = m.NO_PUBLICITE and
        p.DATE_DEBUT_PUB < CURRENT_DATE and
        p.NB_CLICS_FAITS_PUB < p.NB_CLICS_PUB and
        m.MOT like :search
    order by DBMS_RANDOM.RANDOM fetch first 1 rows only
        ");

This snippet causes an error : ORA-00918: column ambiguously defined

When I remove the order by clauses, the request is valid and everything works fine.

Why ? And how can I use DBMS_RANDOM.RANDOM in this context ?

Upvotes: 0

Views: 49

Answers (1)

Popeye
Popeye

Reputation: 35920

Short description of your issue:

  • There must be two or more columns with same name in the select list from different table.

  • restriction on FETCH row limiting clause: whenever you use FETCH row limiting clause, it will throw an error in case there are multiple columns in the select list with same name.

  • try to give different aliases to the column names.

Upvotes: 1

Related Questions