Oracle CTE failing in one computer

Question

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.

I dont know the reason but the query in the WITH is selecting incorrectly. should be like SELECT SUBQUERY.COL FROM SUBQUERY. Else you should get ORA-00904 error. https://oracle-base.com/articles/misc/with-clause — sandy v, Jul 25 '19 at 16:46
@sandyv Yes that was a typo translating from real to simplified names, fixed. — user7393973, Jul 26 '19 at 10:55
It works on one client computer and not another? Just a guess, but I'd say that Microsoft Query is connecting to Oracle via ODBC and that the client machine that does not work has an out-dated version of the Oracle Client installed. — Matthew McPeak, Jul 26 '19 at 15:06
@MatthewMcPeak I just checked and the driver's version is 8.01.07.00 (Oracle ODBC Driver from 11 May 2000) while mine is 11.02.00.01 (Oracle in OraClient11g_home1 from 2 April 2010). I'm going to ask for his to be updated. If you want to write an answer I'll accept it after checking if it worked. Thank you. — user7393973, Jul 26 '19 at 15:33

score 2 · Accepted Answer · answered Jul 26 '19 at 16:59

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.

score 0 · Answer 2 · answered Jul 26 '19 at 17:04

0

I think you can use...

SELECT
  SUBQUERY.COL
FROM
  (
    SELECT
      TBL.COL AS COL --or (TBL.COL COL) or ( COL ) #if not duplicate with any
    FROM
      DB.TBL TBL
  ) SUBQUERY;

answered Jul 26 '19 at 17:04

I'm Limit

889
5
18

Oracle CTE failing in one computer

2 Answers2