[SOLVED] Oracle CTE failing in one computer

Oracle CTE failing in one computer

I have queries created in Microsoft Query to run in Excel with VBA.

They work in different computers but there's one computer where it doesn't work.

In that computer the queries still work except the ones that use CTEs.

A normal query like the following works:

SELECT
  TBL.COL
FROM
  DB.TBL TBL;

But when it has a subquery (CTE) like the following:

WITH
  SUBQUERY AS (
    SELECT
      TBL.COL
    FROM
      DB.TBL TBL
  )
SELECT
  SUBQUERY.COL
FROM
  SUBQUERY;

It runs but doesn't retrieve any data.

It doesn't even show the column name like it would if it worked but had 0 records returned.

The query shows the warning message:

SQL Query can't be represented graphically. Continue anyway?

Which is normal and shows in any computer, but it also shows another warning message after:

SQL statement executed successfully.

Which only appears in that computer when it doesn't work.

I need to be able to use them for the queries that I have made.

Using temporary tables would maybe work but I don't have the permissions required to try.

I tried using inline views but they duplicate the data.

Solution

I have queries created in Microsoft Query to run in Excel with VBA. ... but there's one computer where it doesn't work.

Common table expressions (i.e., the WITH clause) were not introduced until release 9 of the database. Since ODBC is involved (Microsoft Query), the most likely reason for your situation is that the computer that does not work has an out-dated (pre-release 9) version of the Oracle Client installed.

Compare the Oracle Client installations between a client computer that works and one that does not, to find whether this is the case. If it is, upgrade the Oracle Client on the problematic machine.