Column is in more than one table?

[John English <jo...@gmail.com>]

If I issue the following query:

SELECT t_time,facility FROM system_log,(SELECT time FROM system_log 
ORDER BY facility) AS x WHERE system_log.time=x.time

I get an error: "Column name 'FACILITY' is in more than one table in the 
FROM list."

Why does it consider Facility to be a column in table X? Is this correct 
behaviour, or is it a Derby bug?

-- 
John English

-- 
This email has been checked for viruses by AVG.
https://www.avg.com

Re: Column is in more than one table?

[John English <jo...@gmail.com>]

On Tue, 28 Sep 2021, 17:59 Rick Hillegas, <ri...@gmail.com> wrote:

> On 9/28/21 4:51 AM, John English wrote:
>
> If I issue the following query:
>
> SELECT t_time,facility FROM system_log,(SELECT time FROM system_log ORDER
> BY facility) AS x WHERE system_log.time=x.time
>
> I get an error: "Column name 'FACILITY' is in more than one table in the
> FROM list."
>
> Why does it consider Facility to be a column in table X? Is this correct
> behaviour, or is it a Derby bug?
>
> This looks like a bug to me, but one with an easy workaround:
>
> SELECT s.time,s.facility FROM system_log s,(SELECT time FROM system_log ORDER BY facility) AS x WHERE s.time=x.time;
>
> I already worked around it, I just wanted to know if I should submit a bug
report.

>

Re: Column is in more than one table?

[Rick Hillegas <ri...@gmail.com>]

On 9/28/21 4:51 AM, John English wrote:
> If I issue the following query:
>
> SELECT t_time,facility FROM system_log,(SELECT time FROM system_log 
> ORDER BY facility) AS x WHERE system_log.time=x.time
>
> I get an error: "Column name 'FACILITY' is in more than one table in 
> the FROM list."
>
> Why does it consider Facility to be a column in table X? Is this 
> correct behaviour, or is it a Derby bug?
>
This looks like a bug to me, but one with an easy workaround:

SELECT s.time,s.facility FROM system_log s,(SELECT time FROM system_log ORDER BY facility) AS x WHERE s.time=x.time;