April 22, 2015 at 10:37 am
I am working on migrating DB from Oracle to MS SQL. With SSMA all the procedures and routines are not migrated. I have query written for oracle which has to be convered for SQL server. Can any one help me out with T-SQL script for following query?
select Distinct level T1.a1, T1.a2, T1.a3, T2.b1, T2.b2, T2.b3, T2.b4
from T1 TABLE1, T2 TABLE2
WHERE T1.a2=T2.a2
Start with T1.a2 IN (Select a2 from TABLE1 where a1 IS NULL)
CONNECT BY T1.a1 = PRIOR T1.a2 ORDER BY level
April 22, 2015 at 11:00 am
This can be done with a recursive CTE. You can find many examples on the internet.
It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
June 1, 2015 at 12:18 am
Great information you have done here. Thanks
July 10, 2015 at 8:51 am
Luis Cazares (4/22/2015)
This can be done with a recursive CTE. You can find many examples on the internet.It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
Don't forget that recursive CTEs have a 100 recursion limit unless you override it. To do so, specify this after your final query:
OPTION (MAXRECURSION n)
Substitute a positive value for n that is up to 32767. Here's the text from BOL:
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 8:59 am
sgmunson (7/10/2015)
Luis Cazares (4/22/2015)
This can be done with a recursive CTE. You can find many examples on the internet.It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
Don't forget that recursive CTEs have a 100 recursion limit unless you override it. To do so, specify this after your final query:
OPTION (MAXRECURSION n)
Substitute a positive value for n that is up to 32767. Here's the text from BOL:
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
I just want to note that if you hit the default limit, you might be doing something wrong. That level of recursion will be very painful for performance.
August 3, 2015 at 11:47 pm
I am in a little confusion about it. I got a good result. Please give me a detailed explanation.
August 5, 2015 at 8:04 pm
nugentgregg (8/3/2015)
I am in a little confusion about it. I got a good result. Please give me a detailed explanation.
The original Oracle query was recursive, and the CTE, or Common Table Expression, is the way that SQL Server handles recursion. In SQL Server, a recursive CTE has to have two elements. First, an "anchor" query, which is then added to via UNION or UNION ALL with a query that is self-referencing, by introducing a table reference to the CTE using it's alias as if it were a table name. I can't explain all the internals, but it makes for rather easy ways of dealing with hierarchical structures or even just a simple "tally" table (a table of just numbers you can use for generating things like a list of dates (calendar, anyone?). Does that help?
I'd also recommend reading some Itzhik Ben-Gan's books on T-SQL Querying and T-SQL Programming. He covers recursion and does a great job of explaining it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 18, 2015 at 7:17 am
Luis Cazares (4/22/2015)
This can be done with a recursive CTE. You can find many examples on the internet.It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
playing their respective pool members once eachplaying their respective pool members once eachplaying their respective pool members once eachplaying their respective pool members once each
September 18, 2015 at 7:18 am
Luis Cazares (7/10/2015)
sgmunson (7/10/2015)
Luis Cazares (4/22/2015)
This can be done with a recursive CTE. You can find many examples on the internet.It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
Don't forget that recursive CTEs have a 100 recursion limit unless you override it. To do so, specify this after your final query:
OPTION (MAXRECURSION n)
Substitute a positive value for n that is up to 32767. Here's the text from BOL:
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
I just want to note that if you hit the default limit, you might be doing something wrong. That level of recursion will be very painful for performance.
The winner and runner-up of each pool The winner and runner-up of each pool The winner and runner-up of each pool The winner and runner-up of each pool
July 22, 2020 at 4:10 am
This was removed by the editor as SPAM
September 1, 2020 at 5:30 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply