How to deal with redundant sub/queries

[Ralf]

Hello All,

I have a couple of queries, which use all the same subquery. Well, what I
don't like about it is that all subqueries need to be edit as soon as I want
to do change them.
I would like to avoid this redundancy. Is there a way to refer to a subquery?

Ralf

 

[John W. Vinson]

I would like to avoid this redundancy. Is there a way to refer to a subquery?

Store it as a named query, and (rather than putting the entire SQL statement
in as a subquery) simply reference the stored query.

 

[Ralf]

Hi John,

I'm not quite sure what you mean. How shall I refer from one query to
another subquery?

Thank you,
Ralf

 

[John W. Vinson]

Hi John,

I'm not quite sure what you mean. How shall I refer from one query to
another subquery?

Perhaps you could post the SQL of one of your current queries using a
subquery. It's going to be easier to give a concrete example!

 

[Ralf]

Hi John,

An example would be:
SELECT *, (SELECT * FROM table_b) FROM table_a;
Now, image the encapsulated 2nd SELECT statement would be very complex and I
would use it in many other queries. What I would love to have is something
like this:
qryB: SELECT * FROM table_b
qryA: SELECT *, (qryB) FROM table_b;

Thank you,
Ralf

 

[John W. Vinson]

Hi John,

An example would be:
SELECT *, (SELECT * FROM table_b) FROM table_a;
Now, image the encapsulated 2nd SELECT statement would be very complex and I
would use it in many other queries. What I would love to have is something
like this:
qryB: SELECT * FROM table_b
qryA: SELECT *, (qryB) FROM table_b;

Thank you,
Ralf

Save your very complex encapsulated 2nd SELECT statement on its own as qryB.

SELECT qryA.*, (SELECT * FROM qryB) FROM table_b;

In essence you're just stashing all the complexity in a stored query and
referencing it, rather than reiterating all the SQL.