Troubleshooting
Problem
A SQL statement executed by Denodo returns no rows when the SQL statement includes a correlated subquery.
The following SQL statement uses a subquery to select rows in the tsuppliers table based on value of the rows in the tsupply table.
SELECT
"sno" AS "SNO",
"pno" AS "PNO",
"qty" AS "QTY"
FROM
"tsupply" "sp1"
WHERE
"qty" > (
SELECT
AVG(CAST('double', "qty"))
FROM
"tsupply" "sp2"
WHERE
"sp2"."jno" = "sp1"."jno" AND
"sp2"."sno" IN (
SELECT
"sno"
FROM
"tsuppliers" "s"
WHERE
"s"."sno" = "sp1"."sno" )
)
ORDER BY
"SNO" ASC,
"PNO" ASC
Symptom
Denodo server version 7 and 8 incorrectly returns a result set with no rows.
Cause
Denodo defect #83598.
Resolving The Problem
Modify the query to avoid using correlated subqueries when possible.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"a8m0z0000001jkbAAA","label":"Reporting-\u003EPerformance"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
14 February 2024
UID
ibm17086155