SQL "with clause" for reusing subqueries

[stevesavage]

I'm doing a rather complex query to generate a data dicitonary.
To simplify the query, I'd like to make use of Oracles WITH clause.  Unfortunately this doesnt seem to be supported in the SQL search feature (or the repository.SQLQuery) command.  

I don't know if it's something with my local setup, or somehting with EA.  Any ideas?  

Simple Example:
WITH q01 AS (Select Package_ID From t_package where ea_guid = '{A5B35797-AA94-4641-ACC7-AB1A2819DD66}')
SELECT Package_ID From q01

Info from oracle:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2129904

What I'm trying to do:

WITH q04 AS (Select q03.PName, q03.EName, q03.EID, q03.AName, q03.AType From
(Select q02.PName, q02.EName, q02.EID, t_attribute.Name AS AName, t_attribute.Type AS AType From t_attribute JOIN  /* q03: get the attrbutes for the XSD objects */
(Select q01.Name AS PName, t_object.Name AS EName, t_object.Note AS ENote, Object_ID AS EID from t_object JOIN /* q02: get the objects within the XSD packages */
(Select Package_ID, Name From t_package where Parent_ID IN /* q01: get the packages within the XSD Schema */
(Select Package_ID From t_package where ea_guid = '{A5B35797-AA94-4641-ACC7-AB1A2819DD66}') /* the package containing the XSD Schemas */
) q01 ON t_object.Package_ID = q01.Package_ID /* end: q01 */
) q02 ON t_attribute.Object_ID = q02.EID /* end: q02 */
) q03 /* end: q03 */
)

[Helmut Ortmann]

Hi,

I would first test the SQL with Oracle tools (sql developer).

If it works with Oracle Tools and not with EA I would create in Oracle a view with the with clause. Then it's a simple EA select on the query defined in Oracle.

Helmut

[stevesavage]

Hi Helmut, I tested it already with Query builder and it works fine

There was a typo in my last post though:
WITH q04 AS (Select q03.PName, q03.EName, q03.EID, q03.AName, q03.AType From
(Select q02.PName, q02.EName, q02.EID, t_attribute.Name AS AName, t_attribute.Type AS AType From t_attribute JOIN  /* q03: get the attrbutes for the XSD objects */
(Select q01.Name AS PName, t_object.Name AS EName, t_object.Note AS ENote, Object_ID AS EID from t_object JOIN /* q02: get the objects within the XSD packages */
(Select Package_ID, Name From t_package where Parent_ID IN /* q01: get the packages within the XSD Schema */
(Select Package_ID From t_package where ea_guid = '{A5B35797-AA94-4641-ACC7-AB1A2819DD66}') /* the package containing the XSD Schemas */
) q01 ON t_object.Package_ID = q01.Package_ID /* end: q01 */
) q02 ON t_attribute.Object_ID = q02.EID /* end: q02 */
) q03 /* end: q03 */
)
Select * From q04

For now I guess I'll just use SQL Developer to pull the data I need.

[KP]

EA's custom search will only execute select statements. I guess if EA sees a statement beginning 'WITH' it will assume it isn't a select. Worth a feature request?

[stevesavage]

Yes, I'll submit a feature request.
I thought the query would be a simple pass through, I guess EA does some type of syntax checking on the SQL?

[qwerty]

They need to parse it as some passages are being replaced (like e.g. <Serach Term>, #Branch#, etc.)

q.

[Geert Bellekens]

That is probably also the reason why recursive queries in MS SQL Server don't work. They also require a WITH clause.

Geert

[Helmut Ortmann]

Hi,

it's not a problem that EA doesn't allow a with clause.

Just create a view in the database with the with clause. Then make a select on the view.

With that you get the full power of your database in EA.

Helmut

[Geert Bellekens]

Helmut,

I do not fully agree with you.
I don't want to make changes to the db schema myself as it wouldn't be standard anymore and then I would have to start worrying about maintaining all those customizations again.

So it still is a problem for me and it would be a lot better if EA would just support the with clause.

Geert

[stevesavage]

They need to parse it as some passages are being replaced (like e.g. <Serach Term>, #Branch#, etc.)

q.

I thought something like that too, but I was surprised that using the API (Repository.SQLQuery) wasn't just a pass through.   If it was I could use a script and a template fragment to do some of what I want.

[qwerty]

I wasn't referring to SQLQuery but the SQL query builder (the window where you can type in SQL to create searches). I'd suspect that SQLQuery is more or less a pass-through.

q.