Help Transforming TSQL to MSSQL

Question

Post reply

Help Transforming TSQL to MSSQL

jbeclapez

SSC-Addicted

Points: 405

December 11, 2020 at 11:40 am

#3819309

Hello,

I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can you please restate it correctly or put me on the right track?

It is the With statement that is killing me! I think it needs to be inserted in a query...

Thanks

WITH ret AS(
SELECT 
Rel.PrtID, 
Rel.ChdId, 
Rel.DTd,
Rel.DimId 
FROM Rel
WHERE ParentId = 12
AND Rel.DTd = 0
AND Rel.DimId = 0
UNION ALL
SELECT 
SecondRel.PrtID,
SecondRel.ChdId,
SecondRel.DTd,
SecondRel.DimId
FROM Rel SecondRel INNER JOIN 
ret r ON SecondRel.PrtID = r.ChdId
)

SELECT 
ParentId
,ChdId
, ret.DTd
, MB.MBId
,MB.Name
FROM ret

INNER JOIN dbo.MB MB
ON ret.ChdId = MB.MBId
AND ret.DTd = MB.DTd
AND ret.DimId = MB.DimId

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What do you mean by MSSQL? T-SQL is MSSQL, as far as I know.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbeclapez SSC-Addicted Points: 405 More actions · Answer 2

Hi Phil!

Thanks for your answer! I like the "as far as I know"... and I am sure you know a lot more than me 🙂

I think I am not on the right track then... I need rethink that.

Thanks Phil!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

What application or database are you trying to make this work in?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Are you terminating the preceding statement with a semicolon?

You can start statements that begin with "WITH" with a semicolon:

;WITH ret AS(

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Jonathan AC Roberts wrote:

Are you terminating the preceding statement with a semicolon?
You can start statements that begin with "WITH" with a semicolon:
;WITH ret AS(

Now, there's an interview question if I ever heard one. Name one place where you CANNOT prefix a WITH for a CTE with a semi-colon?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 6

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Are you terminating the preceding statement with a semicolon?
You can start statements that begin with "WITH" with a semicolon:
;WITH ret AS(
Now, there's an interview question if I ever heard one. Name one place where you CANNOT prefix a WITH for a CTE with a semi-colon?

At least within a function. I suspect there might be another place or two as well.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

jbeclapez wrote:

Hello,
I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can you please restate it correctly or put me on the right track? It is the With statement that is killing me! I think it needs to be inserted in a query...
Thanks
WITH ret AS(
SELECT 
Rel.PrtID, 
Rel.ChdId, 
Rel.DTd,
Rel.DimId 
FROM Rel
WHERE ParentId = 12
AND Rel.DTd = 0
AND Rel.DimId = 0
UNION ALL
SELECT 
SecondRel.PrtID,
SecondRel.ChdId,
SecondRel.DTd,
SecondRel.DimId
FROM Rel SecondRel INNER JOIN 
ret r ON SecondRel.PrtID = r.ChdId
)

SELECT 
ParentId
,ChdId
, ret.DTd
, MB.MBId
,MB.Name
FROM ret

INNER JOIN dbo.MB MB
ON ret.ChdId = MB.MBId
AND ret.DTd = MB.DTd
AND ret.DimId = MB.DimId
Please describe what you mean by "It is the With statement that is killing me!" What errors are you getting? Where are you actually trying to run this code? And what is the difference between ParentId and PrtID (which is what I think your problem may be)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbeclapez SSC-Addicted Points: 405 More actions · Answer 8

Hi Guys!

I start the WITH statement without the semi column ; then I will test also starting with the semi column.

I had to rewrite all of it and now it is working, but without the WITH. I need to investage further on that.

Thanks gys!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

ScottPletcher wrote:

Jeff Moden wrote:
Jonathan AC Roberts wrote:
Are you terminating the preceding statement with a semicolon?
You can start statements that begin with "WITH" with a semicolon:
;WITH ret AS(
Now, there's an interview question if I ever heard one. Name one place where you CANNOT prefix a WITH for a CTE with a semi-colon?
At least within a function. I suspect there might be another place or two as well.

That's the one I was thinking of (iTVFs that is... I'm pretty sure that you can use it in mTVFs but I don't use those and I've never tried in a Scalar UDF because I try to avoid doing anything that might require such a thing in those). As you say, there may be a couple of more places but haven't run into them, yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

jbeclapez wrote:

Hi Guys!
I start the WITH statement without the semi column ; then I will test also starting with the semi column. I had to rewrite all of it and now it is working, but without the WITH. I need to investage further on that.
Thanks gys!

You have not yet told us what you're writing the code in. Are you using SSMS to write code against tables in SQL Server or something else? And, very specifically, what is/are the precise error(s) that you're getting?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)