CODEWITHSUNDEEP
sql-servert-sqlunion
Umut K
Umut K

Reputation: 1388

merging the results of a SQL query

i am trying to merge the results of this query all data in one list. 'Union' didnt help...

any ideas ?

DECLARE @cnt INT = 0
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @items AS VARCHAR(500)
DECLARE @stline AS VARCHAR(500)



while @cnt < 101

begin 

set @items = 'LG_0' + convert(nvarchar(3),@cnt) + '_ITEMS'
set @stline = 'LG_0' + convert(nvarchar(3),@cnt) + '_01_STLINE'

if (OBJECT_ID (@items) is not null and OBJECT_ID(@stline) is not null ) 

set @SQLQuery = 'SELECT
    ITM1.CODE, itm1.NAME, max (stl.date_) [latest date] , price as [latest price], '+ convert(nvarchar(3),@cnt) + ' as [proje kodu]

FROM ' + @items + ' ITM1 INNER JOIN ' + @stline +  ' STL 
    ON ITM1.LOGICALREF = STL.STOCKREF
WHERE PRICE > 0 
    AND TRCODE = 1 
    AND CANCELLED = 0 
    AND INVOICEREF > 0 


    group by code, name, stl.DATE_ , price

    '

    exec (@SQLQuery)



    set @cnt = @cnt +1 


end

the result is this :

Result

but i want them all union-ed... (sorry for the mostly code issue, i have to add here more typing)

Upvotes: 0

Views: 83

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

I don't know why UNION didn't work for you, but the following should work:

 DECLARE @cnt INT = 0
    DECLARE @SQLQuery AS NVARCHAR(MAX)  = ''
    DECLARE @items AS VARCHAR(500)
    DECLARE @stline AS VARCHAR(500)

    while @cnt < 101

    begin 

    set @items = 'LG_0' + convert(nvarchar(3),@cnt) + '_ITEMS'
    set @stline = 'LG_0' + convert(nvarchar(3),@cnt) + '_01_STLINE'

    if (OBJECT_ID (@items) is not null and OBJECT_ID(@stline) is not null ) 

    set @SQLQuery = @SQLQuery + 'SELECT
        ITM1.CODE, itm1.NAME, max (stl.date_) [latest date] , price as [latest price], '+ convert(nvarchar(3),@cnt) + ' as [proje kodu]

        FROM ' + @items + ' ITM1 INNER JOIN ' + @stline +  ' STL 
            ON ITM1.LOGICALREF = STL.STOCKREF
        WHERE PRICE > 0 
            AND TRCODE = 1 
            AND CANCELLED = 0 
            AND INVOICEREF > 0 
        GROUP BY code, name, stl.DATE_ , price

        UNION ALL
        '    
        set @cnt = @cnt +1 

    end 

    -- SUBSTRING to remove last UNION ALL
    SELECT @SQLQuery = SUBSTRING(@SQLQuery, 1, LEN(@SQLQuery) - 18) 

    -- For debugging purposes
    -- PRINT @SQLQuery
    exec (@SQLQuery)

I couldn't test it, but you can comment the exec, and uncomment the PRINT to see what query is being generated. It makes debugging dynamic SQL a whole lot easier.

The idea behind this code: first the UNION ALL query is being build, based on your conditions. Finally the last UNION ALL is removed, then the code can be executed.

Upvotes: 1

Umut K
Umut K

Reputation: 1388

DECLARE @cnt INT = 0
DECLARE @SQLQuery AS NVARCHAR(max)
DECLARE @items AS VARCHAR(500)
DECLARE @stline AS VARCHAR(500)

set @SQLQuery =''

while @cnt < 101

begin 

set @items = 'LG_0' + convert(nvarchar(3),@cnt) + '_ITEMS'
set @stline = 'LG_0' + convert(nvarchar(3),@cnt) + '_01_STLINE'

if (OBJECT_ID (@items) is not null and OBJECT_ID(@stline) is not null ) 

set @SQLQuery = @SQLQuery + 'SELECT
    ITM1.CODE, itm1.NAME, max (stl.date_) [latest date] , price as [latest price], '+ convert(nvarchar(3),@cnt) + ' as [proje kodu]

    FROM ' + @items + ' ITM1 INNER JOIN ' + @stline +  ' STL 
        ON ITM1.LOGICALREF = STL.STOCKREF
    WHERE PRICE > 0 
        AND TRCODE = 1 
        AND CANCELLED = 0 
        AND INVOICEREF > 0 
    GROUP BY code, name, stl.DATE_ , price

    UNION ALL

    '    
    set @cnt = @cnt +1 
end 

-- SUBSTRING to remove last UNION ALL
--PRINT len(@SQLQuery)

set @SQLQuery = left ( @SQLQuery , len(@SQLQuery)-18)


--PRINT len(@SQLQuery)

-- print @SQLQuery

-- For debugging purposes
-- PRINT @SQLQuery
exec (@SQLQuery)

Upvotes: 0

Related Questions