CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expression
Thiago Custodio
Thiago Custodio

Reputation: 18387

t-sql long query using CTE's and cross joins

I'm facing a very weird problem where I have a complex (long) query with multiple CTE's and in the end I select values from those multiple CTE's using cross joins. Most of the time it works fine, but for some cases it's returning 0 zeros where it should return data.

Running the cte's individually I can see the data I'm looking for. Then I've started to add one cte at a time in the final cross join. I've noticed that for only one is "crashing" the results even though it's not throwing any error.

My final test, I've moved the problematic one as the first cte in my final select statement and everything is working fine (in the following query, imagine cte_e becomes the first one in the final select).

the query looks like this:

;with cte_a as
(
     //select
),
cte_b as
(
     //select
),
cte_c as
(
     //select
),
cte_d as
(
     //select
)
,
cte_e as
(
     //select
)

select * from
cte_a,
cte_b,
cte_c,
cte_d,
cte_e

Questions:

  1. Is there any constrain related to type of the data when using cross joins?

  2. Is there any limit related to the number of CTEs in a query?

  3. How can I proper identify what is the issue with this CTE in particular?

Upvotes: 0

Views: 158

Answers (2)

Thiago Custodio
Thiago Custodio

Reputation: 18387

I've changed the way I'm returning the data, rather than cross join, one value per row and now it's working as expected. Thank you all for trying to help.

Upvotes: 0

SteveC
SteveC

Reputation: 6015

  1. No - there's no constraint on the column types which may be cross joined
  2. No (subject to all SQL Server maximums of course) - have a look here at all of the maxes. There's no mention of maximum number of common table expresssions.
  3. Join the particular CTE to the remaining CTE's pairwise. Somewhere there's a join condition between CTE's which returns zero rows.

Upvotes: 1

Related Questions