Union All mit Schleife

[ibuddy1]

Hallo,

ich habe hier eine Schleife. Diese gibt 30 einzelne Abfragen aus. Ich möchte aber die Werte in einer Tabelle ausgeben. Geht das mit UNION? Oder wie geht das? Ich bekomme mit UNION immer Fehlermeldung.

declare @tag INT = 0
declare @max INT = 30
WHILE(@tag <= @max)
BEGIN
SELECT au.Name, a.Bezeichnung, ad.SerialNo, ad.InventNo, ad.PrinterIP,
                             (SELECT        TOP (1) C1
                               FROM            dbo.ACCMIBCOUNTERVALUES
                               WHERE        (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))) AS Zähler0 ,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'black') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS schwarz0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'cyan') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS cyan0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'magenta') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS magenta0,
                             (SELECT        TOP (1) sValue
                               FROM            dbo.ACCSNMPHISTORY
                               WHERE        (Name LIKE '%Toner%' OR
                                                         Name LIKE '%Kassette%') AND (SnmpColorant = 'yellow') AND (DeviceId = ad.Id) AND (CONVERT(date, TimeUTC) = CONVERT(date, GETDATE() -@tag))
                               ORDER BY TimeUTC DESC) AS gelb0
						  

FROM            dbo.ACCDEVICES AS ad INNER JOIN
                         dbo.ACCUSERS AS au ON au.Id = ad.SubmitterId INNER JOIN
                         scOffice61.dbo.Maschinen AS ma ON ma.MaschinenNummer COLLATE Latin1_General_CI_AS = ad.InventNo INNER JOIN
                         scOffice61.dbo.Artikel AS a ON a.ArtikelNummer COLLATE Latin1_General_CI_AS = ma.ArtikelNummer COLLATE Latin1_General_CI_AS
WHERE        (ad.Obstacles = '0') AND PrinterIP = '10.1.50.102'


SET @tag = @tag +1

END

[Rolf b]

Hallo ibuddy1,

Thread-Spamming ist nicht so beliebt.

Rolf