Joining the results of two queries without creating temporary tables.

  • 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;

  • 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.

  • 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

  • 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


    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)

  • 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;

  • 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.

    ----------------------------------------------------

  • 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


    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 (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.

    ----------------------------------------------------

  • 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

  • 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