November 25, 2015 at 1:31 pm
for example :
with cte1(id) as
{
select query ..............
union all
select query.............
join some query ................
join cte1 on some query
}
how to replicate this logic into normal sql query
I would really appreciate if anyone can crack this logic
Thank You
November 25, 2015 at 1:55 pm
Declare @Table table
Populate it using "query"
Add rows to @Table using
select query.............
join some query ................
join @Table on some query
Repeat.
_____________
Code for TallyGenerator
November 25, 2015 at 4:36 pm
siddharthak024 (11/25/2015)
for example :with cte1(id) as
{
select query ..............
union all
select query.............
join some query ................
join cte1 on some query
}
how to replicate this logic into normal sql query
I would really appreciate if anyone can crack this logic
Thank You
You haven't provided enough information to answer your question. Recursive CTEs are simply a way to accomplish a given task, just like any other "method". The problem that it's being used to solve will determine what (if any) alternatives are available.
If you care to provide a specific query & sample test data, odds are someone here has an alternative.
November 29, 2015 at 11:08 am
Thank You guys for your reply,
This is the detailed query
;with cte1(id) AS
{
select MyId from table1 T1
inner join table2 T2
on T2.MyId=T1.MyId
Union ALL
select MyId from table1 T1
Join Hierarchy_Table HT
on T1.MyId = HT.MyId
Join cte1 c1
on HT.ParentMyId = c1.MyId
where HT.ParentMyId <> T1.MyId
}
select id from cte1
Let me know if this is sufficient
We have to rewrite this query into normal SQL queries without the CTE's.
November 29, 2015 at 12:29 pm
siddharthak024 (11/29/2015)
We have to rewrite this query into normal SQL queries without the CTE's.
Gosh... WHY? What's wrong with using a recursive CTE for what it was best designed for?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2015 at 8:44 pm
My question is how to solve it, Please don't divert the issue. I have to use the non cte code in different places.
November 30, 2015 at 12:09 am
siddharthak024 (11/29/2015)
My question is how to solve it, Please don't divert the issue. I have to use the non cte code in different places.
As Jeff alluded, this is one of those instances where the recursive CTE is the best option. The other, less attractive, options are to use either a cursor or while loop.
Considering that you don't consider CTEs to be "normal"... I don't imagine that you'd consider loops & cursors to be normal either... In which case, you're SOL...
If you have the option to add columns to your table, you could also consider the use of "nested sets". This would, of course, alleviate for loops or recursion when querying the data, but you'd still need them to populated the left & right bowers.
November 30, 2015 at 12:33 am
Jason A. Long (11/30/2015)
The other, less attractive, options are to use either a cursor or while loop.
"Less attractive" is a questionable judgement.
There were several tests here which proved that in terms of performance WHILE loop is more attractive than recursive CTE. It's not much, but faster.
So, "the beauty is in the eyes of beholder" - if you have an antipathy to writing loops you may implement them in form of recursive CTE's.
Some prefer everything to be "in a single query".
But it's only an outlook.
_____________
Code for TallyGenerator
November 30, 2015 at 1:12 am
Sergiy (11/30/2015)
Jason A. Long (11/30/2015)
The other, less attractive, options are to use either a cursor or while loop."Less attractive" is a questionable judgement. There were several tests here which proved that in terms of performance WHILE loop is more attractive than recursive CTE. It's not much, but faster. So, "the beauty is in the eyes of beholder" - if you have an antipathy to writing loops you may implement them in form of recursive CTE's.Some prefer everything to be "in a single query".But it's only an outlook.
It's been my experience that recursive CTEs out perform both while loop and cursors. That said, there are always exceptions and the best way to know for sure in any given circumstance, is to write it both ways and see which version runs faster.
November 30, 2015 at 4:12 am
It depends on what is considered 'normal'. For example
with t1 as(
-- select some initial data
), t2 as (
select --what you need
from t1
join HierarchyTable on -- ...
), t3 as (
select --what you need
from t2
join HierarchyTable on -- ...
)
-- and so on up to max level expected
select * from t1
union all
select * from t2
union all
select * from t3
-- an so on
I hardly can consider this code normal but your mileage may vary.
November 30, 2015 at 4:33 am
depending on what you want to achieve then you may want to add a hierarchyid column to your table and then use the .GetDescendant / .GetAncestor functions rather than using a CTE.
although these are table changes. if that isn't possible then stick with the CTE. unless you are looking to port this code to MYSQL or some other format ??? is this why you are asking the question ?
MVDBA
November 30, 2015 at 6:41 am
It depends on what the recursive CTE is doing. Here's an example, but you need to test and be sure it's a better option. It's a while loop, but it's not RBAR, it's actually set-based programming.
http://www.sqlservercentral.com/articles/set-based+loop/127670/
November 30, 2015 at 9:15 am
@MYDBA
Yes I want to port this code to other platform.
November 30, 2015 at 9:24 am
siddharthak024 (11/30/2015)
@MYDBAYes I want to port this code to other platform.
Then it might depend on the platform. Oracle has something called START WITH...CONNECT BY which could help you.
If you give incomplete information, you won't get the best answers. You should also ask in a forum of the destination platform.
December 2, 2015 at 9:02 am
I'm not sure if this is the answer the OP is looking for, but a derived table may be what is being referred to as "Normal SQL"
In that case,
with cte1(id) as
{
select query ..............
union all
select query.............
join some query ................
join cte1 on some query
}
Would become
select some stuff
from some table
join (select more stuff from another table) x on some conditions
I think the problem we're all having is that the original is so badly represented that what we would assume to be the actual first query is not a safe assumption.
How do I join some query to cte1 on some query?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply