Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31372

[SAP B1] create a VIEW with UNION and WHILE

Show all headers | View raw

Hello

I'm creating a VIEW (to be used as Excel pivot source) that collects data 
from five different tables. I coded it in MS SQL Server Management Studio 
and it works fine, but when I copy it to the VIEW I get the error (Unable 
to parse).

The SQL is like this:

SELECT *
INTO #tmp
FROM (
SELECT a,b,c
FROM tableONE
UNION ALL
SELECT a,b,c
FROM tableTWO
UNION ALL
SELECT a,b,c
FROM tableTHREE
UNION ALL
SELECT a,b,c
FROM tableFOUR
) tmp
DECLARE @cnt INT = 1
WHILE @cnt < 13
BEGIN
insert into #tmp
select a,b,@cnt as c
from tableFIVE
SET @cnt = @cnt + 1
END
select * from #tmp
DROP TABLE #tmp


As I wrote above, it WORKS, but not as a VIEW

The only "solution" I can imagine is to remove the #tmp table and make an 
UNION of all 16 (4+12) select statements


Any tip about this?


-- 
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar

Thread

[SAP B1] create a VIEW with UNION and WHILE Ammammata <ammammata@tiscalinet.it> - 2021-03-31 09:21 +0000
  Re: [SAP B1] create a VIEW with UNION and WHILE Erland Sommarskog <esquel@sommarskog.se> - 2021-03-31 22:32 +0200
    Re: [SAP B1] create a VIEW with UNION and WHILE Ammammata <ammammata@tiscalinet.it> - 2021-04-06 08:15 +0000
      Re: [SAP B1] create a VIEW with UNION and WHILE Ammammata <ammammata@tiscalinet.it> - 2021-04-06 15:19 +0000
        Re: [SAP B1] create a VIEW with UNION and WHILE Ammammata <ammammata@tiscalinet.it> - 2021-04-06 15:22 +0000
          Re: [SAP B1] create a VIEW with UNION and WHILE Ammammata <ammammata@tiscalinet.it> - 2021-04-06 15:45 +0000
            Re: [SAP B1] create a VIEW with UNION and WHILE Doc Tree <davidleerothshairpiece@gmail.com> - 2023-09-02 20:14 -0700

csiph-web