SQL-Abfrage in VBA
05.02.2019 16:04:00
Matthias
Ich habe folgenden SQL-Code, welchen ich gerne in VBA einfügen würde. Leider bekomme ich es nicht zum laufen. Hat jemand eine Idee, woran es liegen könnte?
Der Code funktioniert einwandfrei in SQL.
Vielen Dank im vorraus!
DECLARE @year AS VARCHAR(4) SET @year = datepart(yyyy,getdate()) DECLARE @Firm AS VARCHAR(20) _
_
SET @Firm = '700052' SELECT p.U_ArtNum AS Artikelnummer, p.ItemName AS Artikelbezeichnung, _
[13] AS Januar, [14] AS Februar, [15] AS Maerz, [16] AS April, [17] AS Mai, [18] AS Juni, [19] AS Juli, [20] AS August, [21] AS September, [22] AS Oktober, [23] AS November, [24] AS Dezember FROM( SELECT U_ArtNum,ItemName,InvQty,month FROM ( SELECT T0.U_ArtNum, T1.ItemName, T0.U_Sales AS InvQty, 12+DATEPART(mm,T0.U_Period) as month, ROW_NUMBER() OVER(PARTITION BY U_ArtNum, U_Period ORDER BY U_timestamp DESC) AS rowNum FROM [PROD_BundF_GmbH].[dbo].[@BDGT] T0 LEFT JOIN [PROD_BundF_GmbH].[dbo].OITM T1 ON T1.ItemCode = T0.U_ArtNum WHERE T0.[U_Period] BETWEEN @year+'0101' AND @year+'1231' and T1.TreeType = 'N' AND T1.CardCode = @Firm ) SQ0 WHERE rowNum = 1 UNION ALL SELECT U_ArtNum,ItemName,InvQty,month FROM ( SELECT T5.Code AS U_ArtNum, T6.ItemName, T0.U_Sales/T4.Qauntity*T5.Quantity AS InvQty, 12+DATEPART(mm,T0.U_Period) as month, ROW_NUMBER() OVER(PARTITION BY U_ArtNum, U_Period, T5.Code ORDER BY U_timestamp DESC) AS rowNum FROM [PROD_BundF_GmbH].[dbo].[@BDGT] T0 LEFT JOIN [PROD_BundF_GmbH].[dbo].OITM T1 ON T1.ItemCode = T0.U_ArtNum INNER JOIN [PROD_BundF_GmbH].[dbo].UGP1 T3 ON T1.UgpEntry = T3.UgpEntry INNER JOIN [PROD_BundF_GmbH].[dbo].OITT T4 ON T0.U_ArtNum = T4.Code INNER JOIN [PROD_BundF_GmbH].[dbo].ITT1 T5 ON T4.Code = T5.Father INNER JOIN [PROD_BundF_GmbH].[dbo].OITM T6 ON T5.Code = T6.ItemCode WHERE T0.[U_Period] BETWEEN @year+'0101' AND @year+'1231' and T1.TreeType 'N' AND T1.CardCode = @Firm ) SQ0 WHERE rowNum = 1 ) s PIVOT(SUM(InvQty) FOR [month] IN ([13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24]) ) P