SQL Error : column ambiguously defined with subquery

Question

I'm trying to make a paged display, I've had succes before with this pattern, but this one doesn't work for some reason:

This works,

select e.data,e.onorar,d.data,d.suma 
from contract_j e 
join rata d on e.id_cj = d.id_cj 
join persoana l on l.id_p = e.id_avocat

but this doesnt

select * from (
select a.*, rownum as rnum from (

select e.data,e.onorar,d.data,d.suma 
from contract_j e 
join rata d on e.id_cj = d.id_cj 
join persoana l on l.id_p = e.id_avocat) a 

where rownum <= :maxrow) 
where rnum >= :minrow

It says column ambiguously defined at line 2

Why? I can't figure it out..

score 5 · Accepted Answer · answered Jan 04 '14 at 12:38

5

data column is in SELECT 2 times referring different tables in inner query, whereas in outer query it went ambiguous! So just add suitable alias in inner query to be unique

select * from (
select a.*, rownum as rnum from (

select e.data as e_data,e.onorar,d.data as d_data,d.suma 
from contract_j e 
join rata d on e.id_cj = d.id_cj 
join persoana l on l.id_p = e.id_avocat) a 

where rownum <= :maxrow) 
where rnum >= :minrow

answered Jan 04 '14 at 12:38

Maheswaran Ravisankar

17,652
6
47
69

how can I select only a.e_data rather than a.*? – Saikat Biswas Jan 04 '17 at 07:39
@SaikatBiswas you can just replace `a.*` by `a.e_data` – Maheswaran Ravisankar Jan 04 '17 at 10:11
In a similar example, it was not working.. anyway.. it is working.. thanks – Saikat Biswas Jan 04 '17 at 11:48

SQL Error : column ambiguously defined with subquery

1 Answers1