dimanche 18 octobre 2015

How to construct common table with multiple nested queries

I have a number of views that I need to turn into common table because the where condition need to change dynamically. I can only get the common table to work with one sub query. Is it possible to use more then one as in the example below

WITH ctTableA AS (Select * FROM tableA WHERE fldAID=A)

WITH ctTableB AS (Select * FROM tableB WHERE fldAID=A)

SELECT * FROM ctTableA
INNER JOIN ctTableB ON ctTableA.fldX=ctTableB.fldX

NB:- I know that a simple inner join query of the 2 tables would work for this example but my real query is much more complex.

What I am specifically asking is; Can we have more than 1 with statement within a common table statement

Aucun commentaire:

Enregistrer un commentaire