With Statement in TDA

Toad Data Point
1

Has anyone tried to do a with statement in TDA and have it execute successfully? If so can you post your format?

2

for Oracle

WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);

or

WITH
CUSTOMER_NAME AS
(SELECT ID AS CUST_NAME_ID,FIRSTNAME, LASTNAME
FROM CUSTOMER),
CUSTOMER_PHONE AS
(SELECT ID AS CUST_PHONE_ID, PHONE_HOME, PHONE_MOBILE
FROM CUSTOMER)

SELECT FIRSTNAME,LASTNAME, PHONE_HOME, PHONE_MOBILE
FROM CUSTOMER_NAME, CUSTOMER_PHONE
WHERE CUST_NAME_ID = CUST_PHONE_ID

3

reply.txt (64 Bytes)

4

still not working

5

,charges as

(select * from dual)

union all

select * from charges

;

I keep getting an invalid SQL error
this is what I am trying to run:

with members as (select * from dual)
select * from members

6

It executes fine when try it in the soon to be released TDA 3.0 beta.

7

I was able to successfully execute this statement in TDA 2.7 and TDA 3.0 using Oracle OCI cleint, Direct connect and ODBC. What type of connection are you using and what version of Oracle and client?

Debbie

8

TDA 2.7
Oracle Connection
ORACLE 8
Client 10g client

9

Support for the WITH clause was not added until Oracle 9i.

10

Thank you.