Re: Selecting multiple child tables throwing error
Posted by: Aaron J
Date: October 04, 2021 01:27PM

Please find details below.


Does the query elicit the same error when run directly in the mysql client program?
Yes.

is the server running on Windows without case sensitivity?
Yes


Query below.

SELECT
`Project4`.`OrderId`,
`Project4`.`CustomerId`

FROM (SELECT
`Project1`.`OrderId`,
`Project1`.`CustomerId`

CASE WHEN (`UnionAll1`.`ItemId` IS NOT NULL) THEN (1) ELSE (NULL) END AS `C86`
FROM (SELECT
`Extent1`.`OrderId`,
`Extent1`.`CustomerId`

FROM `ord.Orders` AS `Extent1`
WHERE `Extent1`.`OrderId` = 5) AS `Project1` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent4`.`UnitPriceId` IS NOT NULL) THEN (1) ELSE (NULL) END AS `C1`,
`Extent2`.`ItemId`,
`Extent2`.`ItemId` AS `ItemId1`

FROM `ord.Items` AS `Extent2` INNER JOIN `prd.EntityA` AS `Extent3` ON `Extent2`.`EntityAId` = `Extent3`.`EntityAId` LEFT OUTER JOIN `prd.ChildEndityA` AS `Extent4` ON `Extent4`.`EntityAId` = `Extent2`.`EntityAId`
WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`,
`Extent5`.`ItemId`,
`Extent5`.`ItemId` AS `ItemId1`

FROM `ord.Items` AS `Extent5` INNER JOIN `prd.EntityA` AS `Extent6` ON `Extent5`.`EntityAId` = `Extent6`.`EntityAId` INNER JOIN `prd.ChildEndityB` AS `Extent7` ON (`Extent5`.`EntityAId` = `Extent6`.`EntityAId`) AND (`Extent7`.`EntityAId` = `Extent6`.`EntityAId`)
WHERE 1 = 1)) AS `UnionAll1` ON `Extent1`.`OrderId` = `UnionAll1`.`OrderId`) AS `Project4` ORDER BY `Project4`.`OrderId` ASC


The problem with the query is in last line, `Extent1`.`OrderId` referring a invalid alias. `

If I change the alias manually to `Project1.OrderId`, query is working fine.


Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Re: Selecting multiple child tables throwing error
October 04, 2021 01:27PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.