Help with looping

Question

Help with looping

vu_tran764

SSC Journeyman

Points: 78
More actions
October 23, 2014 at 5:13 pm

#291427

Hi, Can you please help me how to write a looping sql script to populate DocNum02 field just like how I wrote it out? There are four available fields(ID, AttachParentID,AttachLevel, Docnum) and DocNum02 is what the desired output. Please see the attachement for example.
Please let me know soon. Thank you you guys so much

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

There's no need to loop through the table. You should post sample data in a way that we can just execute it and start working on it. I did it for you as an example this time but I might not do it again.

CREATE TABLE SampleData(

ID int,

AttachParentID int,

AttachLevel int,

Docnum varchar(10)

)

INSERT INTO SampleData VALUES

(101,101,0,'ABC001'),

(102,101,1,NULL),

(103,103,0,'ABC002'),

(104,103,1,NULL),

(105,103,1,NULL),

(106,106,0,'ABC003'),

(107,106,1,NULL),

(108,106,1,NULL),

(109,106,1,NULL),

(110,106,1,NULL),

(112,112,0,'ABC004'),

(113,112,1,NULL)

SELECT c.ID,

c.AttachParentID,

c.AttachLevel,

c.Docnum,

x.Docnum + CASE WHEN AttachLevel = 1

THEN '.' + RIGHT( ROW_NUMBER() OVER( PARTITION BY AttachParentID ORDER BY ID) + 999, 3)

ELSE '' END Docnum2

FROM SampleData c

CROSS APPLY

(SELECT Docnum

FROM SampleData p

WHERE p.ID = c.AttachParentID) x

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

vu_tran764 SSC Journeyman Points: 78 More actions · Answer 2

Thank you Luis! You are awesome! I will create the sample data next time if I need to post. I can't believe loop is not needed for this but somehow you did the magic. 🙂 I am going to figure out how this works for the moment. If you could explain how you did this amazing job, please let me know. Much appreciated Thanks again Luis!!!

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

I have to admit that the CROSS APPLY was because I went lazy and didn't change it for an INNER JOIN. Here's an alternative with additional columns that show how the suffix is formed.

SELECT c.ID,

c.AttachParentID,

c.AttachLevel,

c.Docnum,

p.Docnum + CASE WHEN c.AttachLevel = 1

THEN '.' + RIGHT( ROW_NUMBER() OVER( PARTITION BY c.AttachParentID ORDER BY c.ID) + 999, 3)

ELSE '' END Docnum2,

ROW_NUMBER() OVER( PARTITION BY c.AttachParentID ORDER BY c.ID),

ROW_NUMBER() OVER( PARTITION BY c.AttachParentID ORDER BY c.ID) + 999,

'.' + RIGHT( ROW_NUMBER() OVER( PARTITION BY c.AttachParentID ORDER BY c.ID) + 999, 3)

FROM SampleData c

JOIN SampleData p ON p.ID = c.AttachParentID

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

vu_tran764 SSC Journeyman Points: 78 More actions · Answer 4

Thank you Luis! I think you used the row_number function for each row and partition by the attachpid so that each new attachpid restarts at 1 again, which is perfectly correct. Then, you add 999 so that it gets to four digits 0000. and lastly, you use right function to get the last three space from the right most then concatenate Docnum + period + and everything else I mentioned above. You're Genius man. Thank you.