Every derived table must have its own alias

Communities ColdBox HMVC
1

I am stuck on this error: Every derived table must have its own alias I have been attempting to have a property in my entity that is called “InUse” which runs a formula (sql) to check 2 different tables for the same column and to see if it exists in either table. But for some reason every way I have written this query it throws an error that I cannot get past. Here is the example. Note: locationID is the primary key property of this entity.

`

SELECT itemLocation

FROM (

SELECT itemLocation

FROM store

WHERE itemLocation = locationID

UNION ALL

SELECT itemLocation

FROM warehouse

WHERE itemLocation = locationID

)

GROUP BY itemLocation

HAVING Count(*) = 1

`

and if I name the 2 table union outside of the parentheses AS mydata (which is my derivative table) it throws an error right there about syntax. Thoughts?

2

You need to give an alias to the tables. Otherwise, ORM won’t be able to differentiate column and property.

`
SELECT l.itemLocation
FROM (
SELECT s.itemLocation
FROM store AS s
WHERE s.itemLocation = locationID

UNION ALL

SELECT w.itemLocation
FROM warehouse AS w
WHERE w.itemLocation = locationID
) AS l
GROUP BY l.itemLocation
HAVING COUNT( * ) = 1;
`

3

Thanks, I have attempted this similar query but I am receiving an error on giving an alias to the union overall (the AS l part from your example).

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘.l GROUP BY l.itemLocation HAVING COUNT( * ) = 1) as formula1093_0_ from warehou’ at line 14

Thoughts? I am running Lucee 5. The query runs just fine on MySQL but not in the orm.

4

Yeah… I got the same result. It seemed to me Hibernate could not generate the right SQL statement.
Try a different approach. Create a VIEW that unions those two tables?