November 16, 2014 at 9:55 am
In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a
report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary
tables? I'm sorry if this is a naive question, but I don't know much about T-SQL.
Thanks,
- Tom
/*This T-SQL gets the services for the EPN download from WITS*/
-- Select services entered in the last 20 days along with the MPI number and program code.
SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id
-- Select group notes
SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,
dbo.delivered_service_detail.icd9_desc_primary, dbo.delivered_service_detail.service_location_type_code,
dbo.delivered_service_detail.service_location_type_desc, dbo.delivered_service_detail.duration, dbo.unit_program.program_no,
dbo.delivered_service_detail.updated_date, dbo.delivered_service_detail.rendering_staff_id,
dbo.delivered_service_detail.rendering_staff_full_name,
dbo.delivered_service_detail.group_session_client_id
INTO #temp_services
FROM dbo.delivered_service_detail, dbo.client_ssrs, dbo.unit_program
WHERE dbo.delivered_service_detail.client_id = dbo.client_ssrs.client_id AND dbo.delivered_service_detail.program_name = dbo.unit_program.program_name
AND
(dbo.delivered_service_detail.agency_id = 21) AND (DATEDIFF("day", dbo.delivered_service_detail.updated_date, GETDATE()) <= 20)
-- Form an outer join selecting all services with any group notes attached to them.
select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;
-- Drop temporary tables
DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;
November 16, 2014 at 12:07 pm
Are you making a stored procedure call here? I'd recommend it and then make the first line "set nocount off" and the last "set noucount on". This stops SQL from sending the messages on how many rows processed. Often this is enough to turn the listener off as it has data. So each step says "1 row processed" and that's returned and therefore the calling app stops waiting and processes the single row.
November 16, 2014 at 4:59 pm
I am not calling a stored procedure in my query. (I don't think I can call any stored procedures from the data base.) I just want to rewrite my T-SQL code so I am not creating temporary tables, just joining the results of the two select statements (select queries?) together. This way, I will be able to create an SSRS report from my T-SQL code.
Thanks for getting back to me.
- Tom
November 16, 2014 at 5:36 pm
thomaswellington (11/16/2014)
I am not calling a stored procedure in my query. (I don't think I can call any stored procedures from the data base.) I just want to rewrite my T-SQL code so I am not creating temporary tables, just joining the results of the two select statements (select queries?) together. This way, I will be able to create an SSRS report from my T-SQL code.Thanks for getting back to me.
- Tom
Put the two queries each in their own set of parenthesis, add an alias to each of them and use them in a FROM clause of an outer query as if the result sets were separate tables. These are known as "Derived Tables" or "Inline Views". A similar thing can be done using CTEs but the effects are no less frightening.
Here's what I mean by "frightening"...
Both of your queries have SELECT DISTINCT in them. That smacks of the queries producing duplicates due to accidental many-to-many joins, which are second cousins to outright CROSS-JOINs. Since each query will act as if it were a VIEW, it's likely to at least square the problem. Look at the execution plans... if these queries have arrows with more rows than the source tables, this could be a huge problem in the future. I recommend you revisit the queries and make sure there are no many-to-many joins before you even think of using these queries.
Oddly enough, you're trying to get rid of the one thing that might keep it all from getting worse, the Temp Tables. If it were me, I'd create a stored procedure so that I could take serious advantage of such "Divide'n'Conquer" performance tricks as the isolation of result sets in Temp Tables.
Also, SSRS is quite capable of using the results from Stored Procedures. It'll save you a lot of time later if you do.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2014 at 11:34 pm
Quick query without #temp tables which uses a CTE (Common Table Expression). Note that the joins have been changed from ANSI-89 and table aliases added.
😎
;WITH GROUP_SESSIONS AS
(
SELECT
GSC.note
,GSC.error_note
,GSC.group_session_id
,GSC.group_session_client_id
,dbo.group_session.signed_note
,GS.unsigned_note
FROM dbo.group_session_client GSC
INNER JOIN dbo.group_session GS
ON GSC.group_session_id = GS.group_session_id
)
,SERVICES_LIST AS
(
SELECT
CS.state_client_number
,DSD.[program_name]
,DSD.[start_date]
,DSD.start_time
,DSD.[service_name]
,DSD.cpt_code
,DSD.icd9_code_primary
,DSD.icd9_desc_primary
,DSD.service_location_type_code
,DSD.service_location_type_desc
,DSD.duration
,UP.program_no
,DSD.updated_date
,DSD.rendering_staff_id
,DSD.rendering_staff_full_name
,DSD.group_session_client_id
FROM dbo.delivered_service_detail DSD
INNER JOIN dbo.client_ssrs CS
ON DSD.client_id = CS.client_id
INNER JOIN dbo.unit_program UP
ON DSD.[program_name] = UP.[program_name]
WHERE DSD.agency_id = 21
AND 20 >= DATEDIFF("day", DSD.updated_date, GETDATE())
)
SELECT
SL.state_client_number
,SL.[program_name]
,SL.[start_date]
,SL.start_time
,SL.[service_name]
,SL.cpt_code
,SL.icd9_code_primary
,SL.icd9_desc_primary
,SL.service_location_type_code
,SL.service_location_type_desc
,SL.duration
,SL.program_no
,SL.updated_date
,SL.rendering_staff_id
,SL.rendering_staff_full_name
,SL.group_session_client_id
,GS.note
,GS.error_note
,GS.group_session_id
,GS.group_session_client_id
,GS.group_session.signed_note
,GS.unsigned_note
FROM SERVICES_LIST SL
LEFT OUTER JOIN GROUP_SESSIONS GS
ON SL.group_session_client_id = GS.group_session_client_id;
November 17, 2014 at 5:39 pm
dogramone (11/16/2014)
Are you making a stored procedure call here? I'd recommend it and then make the first line "set nocount off" and the last "set noucount on". This stops SQL from sending the messages on how many rows processed. Often this is enough to turn the listener off as it has data. So each step says "1 row processed" and that's returned and therefore the calling app stops waiting and processes the single row.
Set nocount on; is at the start.
Set nocount off; is at the end of your code.
As Jeff mentioned, Select DISTINCT is expensive.
----------------------------------------------------
November 17, 2014 at 7:25 pm
MMartin1 (11/17/2014)
dogramone (11/16/2014)
Are you making a stored procedure call here? I'd recommend it and then make the first line "set nocount off" and the last "set noucount on". This stops SQL from sending the messages on how many rows processed. Often this is enough to turn the listener off as it has data. So each step says "1 row processed" and that's returned and therefore the calling app stops waiting and processes the single row.Set nocount on; is at the start.
Set nocount off; is at the end of your code.
As Jeff mentioned, Select DISTINCT is expensive.
You don't actually need to SET NOCOUNT OFF at the end of your code because the command has a fairly limited scope. The SET NOCOUNT OFF will actually do nothing after the proc exits. Same mostly goes for "scripts" executed by some other process. SET NOCOUNT Is not a server wide statement.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2014 at 12:24 pm
Jeff Moden (11/17/2014)
MMartin1 (11/17/2014)
dogramone (11/16/2014)
Are you making a stored procedure call here? I'd recommend it and then make the first line "set nocount off" and the last "set noucount on". This stops SQL from sending the messages on how many rows processed. Often this is enough to turn the listener off as it has data. So each step says "1 row processed" and that's returned and therefore the calling app stops waiting and processes the single row.Set nocount on; is at the start.
Set nocount off; is at the end of your code.
As Jeff mentioned, Select DISTINCT is expensive.
You don't actually need to SET NOCOUNT OFF at the end of your code because the command has a fairly limited scope. The SET NOCOUNT OFF will actually do nothing after the proc exits. Same mostly goes for "scripts" executed by some other process. SET NOCOUNT Is not a server wide statement.
True, I use SET NOCOUNT OFF on and off 😀 I am not consistent with its use.
----------------------------------------------------
November 19, 2014 at 7:09 pm
Thank you for all the responses, everybody. I am going to try to use Microsoft Access to read the tables I need (I'll use an ODBC connection to the data base). I'll see if I can build my queries in Access. If that doesn't work, I'll try your suggestions.
Again, I appreciate everyone's advice.
Best
- Tom
November 19, 2014 at 7:38 pm
I dont think temporary tables have anything to do with the inability to return results to excel. I dont think you need to avoid them. The final select in the procedure is the results you get.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply