Here is a real life query that fails because
the FROM of the inner select does not have parentheses.
The error message makes clear that only the first table in the failing
FROM clause is used (and the rest are dropped).
I am pretty sure that this query worked before 155 without the parentheses.
Adding the parentheses is no problem but just wanted to let you know about the
incompatibility.
Hope this helps.
SELECT
*,
DISPLAY_NAME AS TITLE,
TYPE || ': ' || DISPLAY_NAME AS FULLTITLE
FROM (
SELECT
PN.PNID,
PN.NAME,
PN.DISPLAY_NAME,
PN.PARENT_ID,
PN.CAPTION,
PN.DESCRIPTION,
PN.NOTES,
PN.IMAGE_ID,
PN.SERIES_ID,
PN.PATH,
PN.PUBLISH_ON_WEB,
PN.PRODUCTION_UNIT_ID,
PN.DEFAULT_RECORDING_TEMPLATE_PNID,
PN.DEFAULT_TRANSLATION_TEMPLATE_PNID,
SPN.DISPLAY_NAME AS SERIES_NAME,
SPPN.DISPLAY_NAME AS INHERITED_SERIES_NAME,
PG.DEFAULT_DURATION_5MIN AS DEFINED_DEFAULT_DURATION_5MIN,
PN.DEFAULT_DURATION_5MIN AS DEFAULT_DURATION_5MIN,
PPN.DEFAULT_DURATION_5MIN AS INHERITED_DEFAULT_DURATION_5MIN,
PG.IS_SERIES,
C.CATEGORY_ID,
C.NAME AS CATEGORY_NAME,
PC.NAME AS INHERITED_CATEGORY_NAME,
C.COLOR,
PC.COLOR INHERITED_COLOR,
CASE
WHEN CPG.CHILD_COUNT IS NULL
THEN 0
ELSE CPG.CHILD_COUNT
END AS CHILD_COUNT,
CASE
WHEN PG.IS_SERIES THEN 'SARJA'
ELSE 'KANSIO'
END AS TYPE
FROM
EXODUS_VIEW.PROGRAM_NODES PN
LEFT JOIN EXODUS_VIEW.PROGRAM_NODES SPN ON PN.SERIES_ID = SPN.PNID
LEFT JOIN EXODUS_VIEW.PROGRAM_NODES PPN ON PN.PARENT_ID = PPN.PNID
LEFT JOIN EXODUS_VIEW.PROGRAM_NODES SPPN ON PPN.SERIES_ID = SPPN.PNID
JOIN EXODUS.PROGRAM_GROUP PG ON PN.PNID = PG.PNID
LEFT JOIN EXODUS.CATEGORY C ON PN.CATEGORY_ID = C.CATEGORY_ID
LEFT JOIN EXODUS.CATEGORY PC ON PPN.CATEGORY_ID = PC.CATEGORY_ID
LEFT JOIN
(
SELECT PARENT_ID, COUNT(PNID) CHILD_COUNT
FROM EXODUS_VIEW.PROGRAM_NODES
GROUP BY PARENT_ID
) AS CPG
ON PG.PNID = CPG.PARENT_ID
);
Column "SPN.DISPLAY_NAME" not found; SQL statement:
CREATE FORCE VIEW PUBLIC._4478 AS
SELECT
PN.PNID,
PN.NAME,
PN.DISPLAY_NAME,
PN.PARENT_ID,
PN.CAPTION,
PN.DESCRIPTION,
PN.NOTES,
PN.IMAGE_ID,
PN.SERIES_ID,
PN.PATH,
PN.PUBLISH_ON_WEB,
PN.PRODUCTION_UNIT_ID,
PN.DEFAULT_RECORDING_TEMPLATE_PNID,
PN.DEFAULT_TRANSLATION_TEMPLATE_PNID,
SPN.DISPLAY_NAME AS SERIES_NAME,
SPPN.DISPLAY_NAME AS INHERITED_SERIES_NAME,
PG.DEFAULT_DURATION_5MIN AS DEFINED_DEFAULT_DURATION_5MIN,
PN.DEFAULT_DURATION_5MIN AS DEFAULT_DURATION_5MIN,
PPN.DEFAULT_DURATION_5MIN AS INHERITED_DEFAULT_DURATION_5MIN,
PG.IS_SERIES,
C.CATEGORY_ID,
C.NAME AS CATEGORY_NAME,
PC.NAME AS INHERITED_CATEGORY_NAME,
C.COLOR,
PC.COLOR AS INHERITED_COLOR,
CASEWHEN((CPG.CHILD_COUNT IS NULL), 0, CPG.CHILD_COUNT) AS CHILD_COUNT,
CASEWHEN(PG.IS_SERIES, 'SARJA', 'KANSIO') AS TYPE
FROM EXODUS_VIEW.PROGRAM_NODES PN [42122-155]
<http://localhost:8082/query.do?jsessionid=53d257f0d764319b73dc85d499097c27#>
42S22/42122
On 06/09/2011 08:17 PM, Thomas Mueller wrote:
Hi,
That's strange, it should work. Most of the test cases don't use
parentheses. Could you post a complete example? The one you gave
works:
drop all objects;
create table a(x int);
create table b(whatever int, foo int);
create table c(bar int);
SELECT *
FROM
A
LEFT JOIN B ON A.X = B.WHATEVER
JOIN C ON B.FOO = C.BAR
WHERE 1=0;
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
