The query below works correctly when run against MS SQL, but fails when run against Denodo Virtual DataPort Administration Tool. The error that is returned in Denodo states “View ‘bom’ not found.” As you can see in the query, bom is defined as the result from the CTE, so I don’t understand why it is not being found. I assume that Denodo handles CTEs differently from MS SQL, and that is what’s causing the problem, but I don’t know how to re-write the query for Denodo VQL.

I have carefully checked capitalization and table and column names, so I don’t think I’ve made that kind of mistake – the tables in the SQL db and the Denodo view are the same, have the same columns, etc. So, I don’t think the structure of the table in Denodo is the problem. I think the problem is a difference between MS SQL and Denodo VQL. Can a VQL expert confirm or refute this diagnosis? How should I re-write the query for VQL?

WITH bom AS (
    SELECT PART_NO, COMPONENT_PART_NO, QTY_PER_ASSEMBLY, 0 AS bomlvl
    FROM bomTable
    WHERE bomTable.PART_NO = '123456'
    UNION ALL
    SELECT bomTable.PART_NO, bomTable.COMPONENT_PART_NO, bomTable.QTY_PER_ASSEMBLY, bom.bomlvl + 1
    FROM bomTable
    INNER JOIN bom ON bomTable.COMPONENT_PART_NO = bom.PART_NO
)
SELECT *
FROM bom a
WHERE NOT EXISTS (SELECT 1 FROM bom WHERE bom.PART_NO = a.COMPONENT_PART_NO);