none
Need a Temp table or Variable workaround RRS feed

  • Question

  • I need to create a DIRECT query that includes a temp table. The temp table is needed to filter out the hundreds of thousands of unneeded rows. Even with the filter the data returned is massive. I then use the XML PATH trick to combine one of the columns and match it to another column.

    I can run the query without the temp table and just use the XML PATH trick, but it takes upwards of 15 mins and is unacceptable. I need to simulate a temp table or DECLARE statement in the PowerBI query and it needs to stay as a DIRECT query. Below is what the query I am attempting to put into PowerBI looks like using a temp table:

    CREATE TABLE #t 
    (
    	name VARCHAR(20)
    	,name_desc VARCHAR(15)
    	,name_cd VARCHAR(20)
    	,sort_name_status_desc VARCHAR(20)
    )
    
    INSERT INTO #t
    SELECT name
    	,name_desc
    	,name_cd
    	,sort_name_status_desc
    FROM Rpt.NAME_STATUS
    WHERE name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    	AND sort_name_status_desc = 'ECHO'
    
    SELECT 
    (
        SELECT DISTINCT ' ' + name_cd + '(' + name_desc + ')'
        FROM #t c
    	WHERE c.[name] = b.[name]  
    		AND name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    		AND sort_name_status_desc = 'ECHO'
        FOR XML PATH('') 
    ) AS COMBINED_DATA
    	,b.name
    FROM #t b
    WHERE name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    	AND sort_name_status_desc = 'ECHO'
    DROP TABLE #t

    And this is using a variable version, just in case its relevant:

    DECLARE @t TABLE (name VARCHAR(20),name_desc VARCHAR(15),name_cd VARCHAR(20),sort_name_desc VARCHAR(20))
    
    INSERT INTO @t
    SELECT name
    	,name_desc
    	,name_cd
    	,sort_name_desc
    FROM Rpt.NAME_STATUS
    WHERE name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    	AND sort_name_desc = 'ECHO'
    
    SELECT 
    (
        SELECT DISTINCT ' ' + name_cd + '(' + name_desc + ')'
        FROM @t c
    	WHERE c.[name] = b.[name]  
    		AND name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    	AND sort_name_desc = 'ECHO'
        FOR XML PATH('') 
    ) AS COMBINED_DATA
    	,b.name
    FROM @t b
    WHERE name_desc  IN  ( 'ALPHA','BETA','CHARLIE','DELTA' ) 
    	AND sort_name_desc = 'ECHO'

    If anyone has any ideas or could point me in the right direction that would be great. Oh, and I cannot create tables on the DB, so unfortunately that is not an option.

    Wednesday, December 20, 2017 6:14 PM

Answers

All replies

  • Problem with SQL-statements is, that they might be executed multiple times. So the first alternative will also fail in import-mode. While the variable works in import, there seem to be limitations for direct query unfortunately. Would be interested as well to hear what they are and how to overcome them.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Thursday, December 21, 2017 6:09 AM
    Moderator
  • The current DirectQuery has a limitation that custom script needs to be able to participate in a sub-select. So I'm afraid this may not be possible for DQ.


    Peter Q. http://blogs.msdn.com/peter_qian

    Wednesday, January 10, 2018 7:34 PM