OPENQUERY

Question

OPENQUERY

Charmer

SSChampion

Points: 14903
More actions
January 17, 2016 at 11:41 pm

#324174

Hi Friends,
I am trying to use while loop in an OPEN QUERY but it is not working. Does OPENQUERY accepts while loop? or
Is there any alternative?
Provide your suggestions please. Below code will show the scenario.
DECLARE @ReportingGroupChanges TABLE
(
AccountId nvarchar(50) NOT NULL
,AccountNumber nvarchar(40) NOT NULL
,ReportingGroup nvarchar (205) NOT NULL
,CreatedOn datetime NOT NULL
)
INSERT INTO @ReportingGroupChanges
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138','AA00000140','191701 (EID)','2016-01-13 11:13:22.000'
UNION
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138','AA00000140','180407 (EID)','2016-01-13 11:12:32.000'
UNION
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138','AA00000140','170445 (EID)','2016-01-12 10:00:32.000'
select * from @ReportingGroupChanges
------ Expecting Result
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138' as AccountID,'AA00000140' AS AccountNumber,'170445 (EID)' As ReportingGroup, NULL as BeforeReportingGroup,'2016-01-12 10:00:32.000' as CreatedOn
UNION
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138' as AccountID,'AA00000140' AS AccountNumber,'170445 (EID),180407 (EID)' As ReportingGroup, '170445 (EID)' as BeforeReportingGroup,'2016-01-13 11:12:32.000' as CreatedOn
UNION
SELECT 'A74F836D-D3FA-E311-8F78-005056B10138' as AccountID,'AA00000140' AS AccountNumber,'170445 (EID),180407 (EID),191701 (EID)' As ReportingGroup, '170445 (EID),180407 (EID)' as BeforeReportingGroup,'2016-01-13 11:13:22.000' as CreatedOn
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 23 total)

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

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 1

I don't see either a WHILE loop or OPENQUERY in the code.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Charmer SSChampion Points: 14903 More actions · Answer 2

No Grant, I have not posted the complete part. I am trying to achieve this through while loop which is okay. I am able to get the result. But unfortunately I have to use this script in OPENQUERY eventually. So I thought it would be better to ask what exactly I am trying to achieve.

Thanks,
Charmer

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

I'm confused then.

As written, you can run the code you're showing, anywhere at all. It's basically hard coding values into a table variable. What does that have to do with a WHILE loop or OPENQUERY? You're going to load the table variable multiple times through a WHILE loop? That doesn't make sense, but you could do it. You're going to JOIN the table variable to an OPENQUERY command within a WHILE loop? I guess the question there is more focused on the need of a WHILE loop with OPENQUERY, but the table variable can be used within a WHILE loop.

I'm sorry. I'm just confused by what you're asking and what you're attempting to do.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Charmer SSChampion Points: 14903 More actions · Answer 4

Grant, Just forget about the open query..I gave you sample data and the expected outcome. I am trying to write a script without using while loop.

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

OK, so you want to concatenate all the preceding ReportingGroups into one column. I've got to ask - how many rows do you or will you have? If you only have three, it's not a problem, but if you have a hundred then you won't be able to display it properly and performance won't be good. What about a million rows - could that ever happen?

John

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 6

So I assume it's keyed off the accountid, but what determines the order? if it's just ordinal, we might have a problem because you can't guarantee order without an ORDER BY statement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Charmer SSChampion Points: 14903 More actions · Answer 7

Hi John,

I will have rows in thousands like 10k...I have to sort based on createdon desc

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 8

ORDER BY CreatedOn column...in descending..

Thanks,
Charmer

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 9

Is it always three values or is it any number of values?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Charmer SSChampion Points: 14903 More actions · Answer 10

I know John, Performance will be affected. But I have no other option.

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 11

Grant, It could be any number. I am expecting in thousands..not more than that...And I am going to use CreatedOn column in WHERE clause.

Thanks,
Charmer

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Charmer (1/18/2016)
I know John, Performance will be affected. But I have no other option.

Never mind performance - how are you going to display a minimum of 12,000 characters in your ReportingGroup column, and why would you even want to? Please will you explain what the exact requirement is and how it arose - maybe we can help you find a better way?

John

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 13

Yikes.

OK, well, the way to solve this would be using a recursive CTE and the OVER...ORDER BY clause... but thousands? No. It basically won't work. I'm think 3-5 or even 8-10 combined columns for a limited set of rows. But you're describing what would be effectively an endless loop. It won't work.

I think John's right. Re-evaluating the exact business requirement we're trying to meet by doing this seems like a good idea.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Charmer SSChampion Points: 14903 More actions · Answer 14

Hi John,

the scenario is that I want to track the changes over a period of time. showing like before and after the modification. Modification can be either update or delete.

I have attached the sample screenshot. I am sorry that I was not clear of what I had explained yesterday. The column ReportingGroup has to be concatenated but it would not be more than 10 rows per AccountID. We need to concatenate RerpotingGroup column based on ChangeDateTime in descending order per AccountID.

Are you clear , John? If not please let me know.

Thanks,
Charmer