0

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 ?

Bibas
  • 498
  • 1
  • 4
  • 17
  • Have you looked at this? https://stackoverflow.com/questions/9868409/how-to-get-records-randomly-from-the-oracle-database. Note that there are several concerns about the load this sort of query can put on the database, as the *entire* table must be accessed and each row assigned a random number before selecting which ones to return. – pmdba Dec 05 '20 at 04:15

1 Answers1

1

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.

Popeye
  • 35,427
  • 4
  • 10
  • 31