Rewrite using CTE

Question

Rewrite using CTE

rxm119528

SSCrazy

Points: 2960
More actions
November 11, 2015 at 11:55 am

#313005

Hi all,
Just want to know if the below query can be written using the CTE
[p]DECLARE @C INT = 0;
DECLARE @G INT = 0;
DECLARE @h INT = 0;
DECLARE @I INT = 0;
DECLARE @VARABLE VARCHAR(10) = 'AB.DEF'
DECLARE @Table TABLE (VARABLE1 VARCHAR(12) NOT NULL);
WHILE (@c < 10)
IF (@C = 0 OR @C = 1 OR @C = 2 OR @C = 3 OR @C = 8 OR @C = 9)
BEGIN
BEGIN
SET @G = 0
SET @h = 0
SET @I = 0
WHILE (@g < 2)
BEGIN
SET @h = 0
SET @I = 0
WHILE (@H < 2)
BEGIN
SET @I = 0
WHILE (@I < 2)
BEGIN
INSERT INTO @Table
SELECT SUBSTRING(@VARABLE, 1, 3) + cast(@c AS VARCHAR(1)) + SUBSTRING(@VARABLE, 5, 3) + cast(@G AS VARCHAR(1)) + cast(@H AS VARCHAR(1)) + cast(@I AS VARCHAR(1))
SET @I = @I + 1
END
SET @h = @h + 1
END
SET @g = @g + 1
END
SET @C = @C + 1
END
END
SELECT *
FROM @TABLE
[/p]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

rxm119528 (11/11/2015)
Hi all,
Just want to know if the below query can be written using the CTE
[p]DECLARE @C INT = 0;
DECLARE @G INT = 0;
DECLARE @h INT = 0;
DECLARE @I INT = 0;
DECLARE @VARABLE VARCHAR(10) = 'AB.DEF'
DECLARE @Table TABLE (VARABLE1 VARCHAR(12) NOT NULL);
WHILE (@c < 10)
IF (@C = 0 OR @C = 1 OR @C = 2 OR @C = 3 OR @C = 8 OR @C = 9)
BEGIN
BEGIN
SET @G = 0
SET @h = 0
SET @I = 0
WHILE (@g < 2)
BEGIN
SET @h = 0
SET @I = 0
WHILE (@H < 2)
BEGIN
SET @I = 0
WHILE (@I < 2)
BEGIN
INSERT INTO @Table
SELECT SUBSTRING(@VARABLE, 1, 3) + cast(@c AS VARCHAR(1)) + SUBSTRING(@VARABLE, 5, 3) + cast(@G AS VARCHAR(1)) + cast(@H AS VARCHAR(1)) + cast(@I AS VARCHAR(1))
SET @I = @I + 1
END
SET @h = @h + 1
END
SET @g = @g + 1
END
SET @C = @C + 1
END
END
SELECT *
FROM @TABLE
[/p]

As posted you have an endless loop. You would need an else for the first IF statement to increment @C so the loop will function. Once you do that you get 48 rows of output.

What I don't understand is what you are trying to accomplish here and why do you think a cte would help?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Hmmm...what exactly are you trying to do?

As written, the query will never terminate, because the command to increment @C is within the scope of the IF statement, so @C will stop incrementing after it hits 4, and the WHILE condition will always be met and evaluate the IF condition over and over again.

If that issue is fixed, then it seems you are just outputting all possible concatenations of 'AB.', a digit in the set {0,1,2,3,8,9}, 'EF', and a 3 digit binary sequence. In other words, AB.0EF000 through AB.9EF111.

That's easy enough to do without looping, but I'd like to know more about the goal of the exercise.

Cheers!

EDIT: I see Sean already posted the same point while I was typing mine 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

It is extremely unclear what you are trying to do but does something like get you pointed in the right direction? This is far simpler (and faster) than nested while loops 4 deep.

DECLARE @VARABLE VARCHAR(10) = 'AB.DEF';

select SUBSTRING(@VARABLE, 1, 3) + Prefix + SUBSTRING(@VARABLE, 5, 3) + Suffix

from (values('0'), ('1'), ('2'), ('3'), ('8'), ('9')) x(Prefix)

cross apply (values('000'), ('001'), ('010'), ('011'), ('100'), ('101'), ('111')) y(Suffix);

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I see that Sean got the simpler route by defining hard coded values.

This should allow the results to grow in an easier way if needed.

DECLARE @VARABLE VARCHAR(10) = 'AB.DEF';

WITH

E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

--E2(n) AS(

-- SELECT a.n FROM E a, E b

--),

--E4(n) AS(

-- SELECT a.n FROM E2 a, E2 b

--),

cteTally(n) AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n

FROM E

)

SELECT STUFF(@VARABLE, 4, 1, n) + x.b + y.b + z.b VARABLE1

FROM cteTally

CROSS JOIN (VALUES('0'),('1')) x(b)

CROSS JOIN (VALUES('0'),('1')) y(b)

CROSS JOIN (VALUES('0'),('1')) z(b)

WHERE n IN( 0, 1, 2, 3, 8, 9)

ORDER BY VARABLE1;

EDIT: It'll also avoid forgetting values when entering them manually as Sean did. Everybody make mistakes.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rxm119528 SSCrazy Points: 2960 More actions · Answer 5

Apologies for the missing loop. Thanks for the different approaches. I just wanted to learn the other ways we can append those sequence of numbers with out using the for loop.

Thanks again

rxm119528.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

rxm119528 (11/11/2015)
Apologies for the missing loop. Thanks for the different approaches. I just wanted to learn the other ways we can append those sequence of numbers with out using the for loop.
Thanks again
rxm119528.

Then more important than this exercise you should become intimately familiar with tally/number tables. They are much better than looping for this type of thing. Check out this great article from Jeff on this very topic. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Sean Lange (11/11/2015)
rxm119528 (11/11/2015)
Apologies for the missing loop. Thanks for the different approaches. I just wanted to learn the other ways we can append those sequence of numbers with out using the for loop.
Thanks again
rxm119528.
Then more important than this exercise you should become intimately familiar with tally/number tables. They are much better than looping for this type of thing. Check out this great article from Jeff on this very topic. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

This is also a great article from Dwain Camps: Tally Tables in T-SQL[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Luis Cazares (11/11/2015)
Sean Lange (11/11/2015)
rxm119528 (11/11/2015)
Apologies for the missing loop. Thanks for the different approaches. I just wanted to learn the other ways we can append those sequence of numbers with out using the for loop.
Thanks again
rxm119528.
Then more important than this exercise you should become intimately familiar with tally/number tables. They are much better than looping for this type of thing. Check out this great article from Jeff on this very topic. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
This is also a great article from Dwain Camps: Tally Tables in T-SQL[/url]

Oh....I hadn't seen that one. Thanks for sharing Luis. Dwain also writes some great articles on extremely relevant topics. I really need to get off my butt and write some articles myself. :blush:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/