1

The Oracle 12c documentation about FETCH is telling the below

If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.

But in Oracle Live SQL i am able to run the below Query succesfully. (there we will have 2 columns returned in the result with same name "department_id")

select *
from hr.employees e
join hr.departments d
on (e.department_id = d.department_id)
fetch next 10 rows with ties
;

Could someone explain me if this is where we should expect the Oracle Error or it the documentation meant another scenario ?

Thanks

SD

1 Answer 1

0

Do not believe everything you read in Oracle documentation.

Try the below:

CREATE TABLE a (test NUMBER not null, test1 VARCHAR(6) not null, id VARCHAR(5) not null); 
CREATE TABLE b ( id VARCHAR(5) not null, lang VARCHAR(5) not null, test1 VARCHAR(6) not null, test2 VARCHAR(5) not null );

insert into a values(1, 'AA',10);
insert into a values(2, 'BB',20);
insert into a values(3, 'CC',30);
commit;

insert into b values(10, 'eng','AA','test2');
insert into b values(20, 'tamil','BB','test2');
commit;

Then:

1) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ;
2) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
3) SELECT aa.test, aa.test1, aa.id, bb.test1 as t1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
4) SELECT * FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;

1), 3) and 4) runs successfully on 12.1.0.2, 2) throws ORA-00918.

On 12.2.0.1, all 4 runs successfully.

The one thing to keep in mind: never rely on implicit behaviour you can not control. Use explicit, unique aliases.

1
  • Thanks for checking with 12.1.0.2. i was checking with Live SQL (so current release 12.2.0.1). Looks like that with 12.1.0.2 you cannot specify more than one column with the same name while using row limiting clause. No error using *. So mostly the doc is right but just not reflecting the latest releases.
    – SylvainN
    Feb 9, 2018 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.