Subquery Factoring - WITH SELECT

SQL Navigator
1

I cannot get SubQuery Factoring to work in 6.1

Consider this simplest query in SQL Nav 5.5.4

*** SCRIPT START : Session:XX_CUSTOM@OCSNB(1) 2/17/2009 3:56:37 PM ***

WITH my_table AS (SELECT * FROM dual)
SELECT * FROM my_table

1 row(s) selected

DUMMY

X

1 row(s) retrieved

The above is the expected result. Now look at the same query in SQL Nav 6.1

**** SCRIPT STARTED 2/17/2009 4:02:54 PM ****
WITH my_table AS (SELECT * FROM dual)
SELECT * FROM my_table
==>
SQL statement doesn’t return rows
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
**** SCRIPT ENDED 2/17/2009 4:02:54 PM ****

2

That’s strange. It works fine for me Charles. Can anyone else reproduce it?

3

There is only one copy of SQL Nav 6.1 Trial in the building - so no.

Try this similar example - again it working in 5.5.4 but not 6.1 Oracle 10.2.0.4.0 database - same database in both instances of SQL Nav

**** SCRIPT STARTED 2/17/2009 4:36:56 PM ****

with my_table as (select * from user_tables) select * from my_table

==>

SQL statement doesn’t return rows

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

**** SCRIPT ENDED 2/17/2009 4:36:56 PM **** ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

4

Hi Charles,

Normally if nothing is returned, the message would be ‘0 row(s) fetched’. I’ve never seen this ‘SQL statement doesn’t return rows’ before. Which client are you using? could you please take a screenshot of the script page? Does it show the datagrid after the query was run?

Gwen

5

See attached screen shot file; but you were right to question the Oracle client.
Upon checking my PATH statement of the test machine where our trial SQL Nav 6.1 is located, I found the 8.172 client path listed first. When I removed this it allowed for the 9.2 client to be used and SQL Nav 6.1 executed the query as expected.
Thanks for your help!
sqlnavScreenShots1.doc (70 KB)

6

Glad I could help!

7

We have here a similar error. We use a oracle home with 10.2.0.1.0 client in the connection information.
Why path is related here?
In the path there is a oracle home wiht 10.1.0.5.0 client in front of some other.

8

Torsten,

I’d try to put the the 10.2.0.1.0 oracle home’s bin folder as the first entry in the path, and try again.

BTW: what is this “similar error”? It might point to an altogether different problem…

9

I tried following select in sql navigator 6.1.1.1105:

SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)

No data grid is shown after executing.
Spool output shows:

**** SCRIPT STARTED 9.03.2009 15:01:17 ****
SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)
==>
SQL statement executed
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
**** SCRIPT ENDED 9.03.2009 15:01:17 ****
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Opposite the original statement above works fine in sql navigator 6.1.1.1105.

10

I concur with tkleiber’s findings. I now have both V5.5.4 and V6.1 on my own desktop machine, they should both be looking at the same PATH.

In 5.5.4 I get this result of his query:
** SCRIPT START : Session:XX_CUSTOM@OCDEV(1) 3/9/09 9:21:08 ***
Processing …
SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)

1 row(s) selected

DUMMY

X

1 row(s) retrieved

**** SCRIPT STARTED 3/9/2009 9:20:02 AM ****

SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)

==>

SQL statement executed

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

**** SCRIPT ENDED 3/9/2009 9:20:02 AM ****

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No result grid appears.

FYI
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

*** SCRIPT END : Session:XX_CUSTOM@OCDEV(1) 3/9/09 9:21:08 ***
I believe this to be the expected result.

However in V 6.1, no errors but no results appear.

11

Torsten,

The statement “WITH a AS (SELECT * FROM DUAL) SELECT * FROM a” returns ‘X’ in the data grid as it should? It does in 6.2.0.1250, anyway.

The statement “SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)” also does not show a data grid and (about) the same spool output in 6.2.

I’d think you hit a bug - Gwen?

12

Is there a date for 6.2 production release, we find a lot of recognized bugs with the ‘WITH’ and ‘INTERSECT’ clauses?

13

Hi guys,

That one is already in our list and currently being fixed.
Torsten, could you give me an example of issue with INTERSECT clause?
We haven’t committed to a date yet. I will update as soon as we have one.
I will also have a chat with Bruce to see if we can do another beta release.

Cheers
Gwen

14

The INTERSECT problem seems to be fixed in 6.1.1.1105.
But as the WITH problems still persist, we wait urgent for 6.2, as we have to deploy the version to a lot of programmers.

15

The problem with
“SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)”
is furthermore reproducable in beta build 1317 .
This is an important problem on our side.

16

I checked the list and the CR is still open (i.e. has not been implemented yet).
But don’t worry, it will be on 6.2.

Gwen

17

Torsten,

I’ve just checked and
SELECT * FROM (WITH a AS (SELECT * FROM DUAL) SELECT * FROM a)
also works in our development build.

Roman

18

Beta 6.2.0.1381 solves this problem