Eu criei o seguinte código T-SQL no SQL Server Management Studio. Neste, estou tentando obter detalhes de itens de cada banco de dados da estação de Gerenciamento de Clientes que está no servidor remoto 770Main. Todos os bancos de dados do cliente têm a mesma estrutura / design de banco de dados. Mas existem cerca de 60 clientes. Então, usando T-SQL eu coloquei o script inteiro em uma string de template, com espaços reservados {dbname} e execute @SQLSCRIPT dentro de um loop. Quando eu executo isso estou recebendo Aviso: valor nulo é eliminado por um agregado ou outra operação SET. Mas quando eu executo o script de template SQL (veja abaixo) separadamente para um cliente, ele funciona perfeitamente.
SELECT DISTINCT vend.contact
,C.customername
--,w.WarehouseNumber
,item.vendoritem
,item.description1
,item.description2
--,PW.VendorProductCode
--,pw.vendorcode
--,pw.vendorname
,item.issuecost
,bin.station
,ST.CRIBSTTYPE
,R.RBSTTYPE
--,PW.StatusCode
,Bin.packQty
,item.orderqty
,weeklysales.lastweeksales
,weeklysales.wtdsales
,st.cribmin
,st.cribmax
,R.MYMIN
,R.MYMAX
,acbin.binqoh [AutoCrib QOH]
--,sum(case when received=''0'' then coalesce(rls.[receive],0)*coalesce(rls.packqty,0) else 0 end) as ACtotalonorder
FROM [770main].{dbname}.dbo.bin Bin
LEFT OUTER JOIN [770main].{dbname}.dbo.item Item
ON bin.item = item.code
inner join [770main].{dbname}.dbo.release as rls on rls.itemid=item.code and item.vendor=rls.vendorid
LEFT OUTER JOIN [770main].{dbname}.dbo.company Company
ON bin.aspprocessed = company.aspprocessed
left join (select sum(onhand) binqoh,aspprocessed,item from [770main].{dbname}.dbo.bin where station not like ''ods%'' group by aspprocessed,item ) acbin
on acbin.item = Item.code and acbin.aspprocessed = Company.aspprocessed
LEFT OUTER JOIN [770main].{dbname}.dbo.vend Vend
ON item.vendor = vend.myno
LEFT OUTER JOIN customer C
ON C.customercode collate SQL_Latin1_General_CP1_CI_AS = vend.contact collate SQL_Latin1_General_CP1_CI_AS
--left JOIN (select distinct pwv.companyid,pwv.productcode,pwv.vendorcode, v.vendorname,pwv.warehousenumber, pwv.vendorproductcode, w.statuscode
--from productwarehousevendor pwv
--inner join Warehouse w on w.CompanyID = pwv.CompanyID and w.ProductCode = pwv.ProductCode and w.WarehouseNumber = pwv.WarehouseNumber
-- inner join vend v on pwv.companyid = v.companyid and pwv.vendorcode = v.vendornumber
--where pwv.CompanyID = ''002'' and pwv.PrimaryVendorFlag = ''Y'') pw
-- ON pw.ProductCode collate SQL_Latin1_General_CP1_CI_AS = item.vendoritem collate SQL_Latin1_General_CP1_CI_AS
left join
(select productcode
--, sellingwarehousenumber warehousecode
, ordh.companyid
, sum( CASE WHEN (invoicedate >= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate <= DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6))
THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
ELSE Null END) lastweeksales
,
sum( CASE WHEN (invoicedate >= dateadd(wk, datediff(wk, 0, getdate()), 0) and invoicedate <= getdate())
THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
ELSE Null END) wtdsales
from orderhistory ordh inner join orderhistoryline ordhl on
ordh.CompanyID = ordhl.companyid and
ordh.ordernumber = ordhl.ordernumber
where
(invoicedate >= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate <= getdate()) and
ShippedQuantity <> 0 and ordh.CompanyID = ''002''
group by productcode, ordh.companyid
) weeklysales on
weeklysales.productcode = item.vendoritem --and
--weeklysales.warehousecode = w.warehousenumber and
--weeklysales.companyid collate SQL_Latin1_General_CP1_CI_AS = pw.companyid collate SQL_Latin1_General_CP1_CI_AS
left join [770main].{dbname}.dbo.station s on bin.station = s.myno
FULL JOIN STNMM St ON Bin.Item=St.ITEM
FULL JOIN ROBOMM R ON Bin.Item=R.ITEM
WHERE
--pW.CompanyID = ''002'' AND
C.COMPANYID = ''002''
GROUP BY vend.contact
,C.customername
-- ,w.WarehouseNumber
,item.vendoritem
,bin.station
,ST.CRIBSTTYPE
,R.RBSTTYPE
--,pW.StatusCode
,Bin.packQty
,item.orderqty
,weeklysales.lastweeksales
,weeklysales.wtdsales
,st.cribmin
,st.cribmax
,R.MYMIN
,R.MYMAX
,item.description1
,item.description2
,acbin.binqoh
--,PW.VendorProductCode
-- ,pw.vendorcode
--,pw.vendorname
,item.issuecost
Por favor, ajude-me a corrigir este problema.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (object_id('TMPProductbyCustomer')) IS NOT NULL
BEGIN
DROP TABLE TMPProductbyCustomer
print('dropped')
END
DECLARE @sql_script VARCHAR(max)
DECLARE @template VARCHAR(max)
DECLARE @Dbname varchar(100)
DECLARE @table table(id int identity(1,1),dbname varchar(100))
insert into @table
SELECT 'Abipa'
union select 'ascoac'
union SELECT 'BourgaultE'
UNION SELECT 'Avcorp'
UNION SELECT 'Blackcat'
UNION SELECT 'Bohler'
UNION SELECT 'BourgaultE'
UNION SELECT 'CAPTIN'
UNION SELECT 'CascadeAC'
UNION SELECT 'Castool'
UNION SELECT 'CGL'
UNION SELECT 'Composites'
UNION SELECT 'CookeDennison'
UNION SELECT 'Delta'
UNION SELECT 'DFI'
UNION SELECT 'DKSpec'
UNION SELECT 'DSI'
UNION SELECT 'Dyna'
UNION SELECT 'Ellery'
UNION SELECT 'FBT'
UNION SELECT 'Foremost'
--UNION SELECT 'FredHall'
UNION SELECT 'GNCorp'
UNION SELECT 'Hawkeye'
--UNION SELECT 'Hewitt' doesn't exist
--UNION SELECT 'IMW' autocrib 53
UNION SELECT 'IndMach'
UNION SELECT 'JohnCrane'
UNION SELECT 'KDM'
UNION SELECT 'Kelowna'
UNION SELECT 'KFHamilton'
UNION SELECT 'MacKenzie'
UNION SELECT 'Massiv'
UNION SELECT 'Mearls'
UNION SELECT 'Miknar'
UNION SELECT 'ModernEng'
UNION SELECT 'MTU'
UNION SELECT 'MurrayLatta'
UNION SELECT 'Mytox'
UNION SELECT 'Nicholson'
UNION SELECT 'NorancoPK'
UNION SELECT 'NorancoVA'
UNION SELECT 'NorancoWB'
--UNION SELECT 'Norquest' FTP setup issue. Dave
UNION SELECT 'Northstar'
UNION SELECT 'NWT'
UNION SELECT 'Progressive'
UNION SELECT 'Pullmaster'
UNION SELECT 'Rose'
UNION SELECT 'RoseStores'
UNION SELECT 'RTI'
UNION SELECT 'Sabre2'
UNION SELECT 'SabreLloyd'
UNION SELECT 'Silverado'
UNION SELECT 'SnC_AC'
UNION SELECT 'StdMach'
UNION SELECT 'Straightline'
UNION SELECT 'Streamflo'
UNION SELECT 'TCS'
UNION SELECT 'Techtronics'
UNION SELECT 'Tsubaki'
--UNION SELECT 'Vector' ftp issues
--UNION SELECT 'Vector2'
UNION SELECT 'Venables'
UNION SELECT 'Veriform'
UNION SELECT 'Veritas'
UNION SELECT 'Weatherford'
UNION SELECT 'Wescam'
DECLARE @COUNTER INT = 1
DECLARE @END int = (select count(*) from @table)
CREATE table TMPProductbyCustomer (
CustomerID varchar(6)
,CompanyName varchar(100) null
,ItemVendorItem varchar(100)
,Decription1 varchar(35) null
,Description2 varchar (35) null
--,VendorProductCode varchar(35) null
--,VendorCode varchar(6)
--,VendorName varchar(50)
,IssueCost money
,Station varchar(4)
,CribStationType int null
,RoboStationType int null
--,StockingStatus varchar(1)
,PackQty int
,ORDERQTY int
,LastWeekSales money null
,WTDSales money null
,CRIBMIN int null
,CRIBMAX int null
,MYMIN int null
,MYMAX int null
,OnHand int
--,OnOrder int null
)
WHILE @COUNTER <= @END
BEGIN
set @DBNAME = (select dbname from @table where ID = @COUNTER )
set @template ='
WITH STNMM (item, station, cribsttype,name,packqty,onhand,burnqty,cribmin,cribmax)
AS
(
select
bin.item
,bin.station
,station.[type] AS CRIBSTTYPE
,station.name
,bin.packqty
,bin.onhand
,bin.burnqty
,bin.minimum as cribmin
,bin.maximum as cribmax
from [770main].{dbname}.dbo.bin as bin
left join [770main].{dbname}.dbo.station as station on bin.station=station.myno
where station.[type] <> 6
),
ROBOMM (item, station, RBSTTYPE,name,pkgqty,mymin,mymax)
AS
(
SELECT
MM.ITEM
,MM.STATION
,station.[type] as RBSTTYPE
,STATION.NAME
,MM.PKGQTY
,MM.MYMIN
,MM.MYMAX
FROM [770MAIN].{dbname}.DBO.ROBOMINMAX AS MM
LEFT JOIN [770MAIN].{dbname}.DBO.STATION AS STATION ON MM.STATION=STATION.MYNO
WHERE station.[type] = 6 )
INSERT INTO TMPProductbyCustomer SELECT DISTINCT vend.contact
,C.customername
--,w.WarehouseNumber
,item.vendoritem
,item.description1
,item.description2
--,PW.VendorProductCode
--,pw.vendorcode
--,pw.vendorname
,item.issuecost
,bin.station
,ST.CRIBSTTYPE
,R.RBSTTYPE
--,PW.StatusCode
,Bin.packQty
,item.orderqty
,weeklysales.lastweeksales
,weeklysales.wtdsales
,st.cribmin
,st.cribmax
,R.MYMIN
,R.MYMAX
,acbin.binqoh [AutoCrib QOH]
--,sum(case when received=''0'' then coalesce(rls.[receive],0)*coalesce(rls.packqty,0) else 0 end) as ACtotalonorder
FROM [770main].{dbname}.dbo.bin Bin
LEFT OUTER JOIN [770main].{dbname}.dbo.item Item
ON bin.item = item.code
inner join [770main].{dbname}.dbo.release as rls on rls.itemid=item.code and item.vendor=rls.vendorid
LEFT OUTER JOIN [770main].{dbname}.dbo.company Company
ON bin.aspprocessed = company.aspprocessed
left join (select sum(onhand) binqoh,aspprocessed,item from [770main].{dbname}.dbo.bin where station not like ''ods%'' group by aspprocessed,item ) acbin
on acbin.item = Item.code and acbin.aspprocessed = Company.aspprocessed
LEFT OUTER JOIN [770main].{dbname}.dbo.vend Vend
ON item.vendor = vend.myno
LEFT OUTER JOIN customer C
ON C.customercode collate SQL_Latin1_General_CP1_CI_AS = vend.contact collate SQL_Latin1_General_CP1_CI_AS
--left JOIN (select distinct pwv.companyid,pwv.productcode,pwv.vendorcode, v.vendorname,pwv.warehousenumber, pwv.vendorproductcode, w.statuscode
--from productwarehousevendor pwv
--inner join Warehouse w on w.CompanyID = pwv.CompanyID and w.ProductCode = pwv.ProductCode and w.WarehouseNumber = pwv.WarehouseNumber
-- inner join vend v on pwv.companyid = v.companyid and pwv.vendorcode = v.vendornumber
--where pwv.CompanyID = ''002'' and pwv.PrimaryVendorFlag = ''Y'') pw
-- ON pw.ProductCode collate SQL_Latin1_General_CP1_CI_AS = item.vendoritem collate SQL_Latin1_General_CP1_CI_AS
left join
(select productcode
--, sellingwarehousenumber warehousecode
, ordh.companyid
, sum( CASE WHEN (invoicedate >= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate <= DATEADD(wk, DATEDIFF(wk, 6, CURRENT_TIMESTAMP), 6))
THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
ELSE Null END) lastweeksales
,
sum( CASE WHEN (invoicedate >= dateadd(wk, datediff(wk, 0, getdate()), 0) and invoicedate <= getdate())
THEN (ordhl.NetPrice*ordhl.shippedquantity/ coalesce(ordhl.UnitOfMeasurePriceFactor,1) / coalesce(ordh.CurrencyRate,1))
ELSE Null END) wtdsales
from orderhistory ordh inner join orderhistoryline ordhl on
ordh.CompanyID = ordhl.companyid and
ordh.ordernumber = ordhl.ordernumber
where
(invoicedate >= dateadd(wk, datediff(wk, 0, getdate())-1, 0) and invoicedate <= getdate()) and
ShippedQuantity <> 0 and ordh.CompanyID = ''002''
group by productcode, ordh.companyid
) weeklysales on
weeklysales.productcode = item.vendoritem --and
--weeklysales.warehousecode = w.warehousenumber and
--weeklysales.companyid collate SQL_Latin1_General_CP1_CI_AS = pw.companyid collate SQL_Latin1_General_CP1_CI_AS
left join [770main].{dbname}.dbo.station s on bin.station = s.myno
FULL JOIN STNMM St ON Bin.Item=St.ITEM
FULL JOIN ROBOMM R ON Bin.Item=R.ITEM
WHERE
--pW.CompanyID = ''002'' AND
C.COMPANYID = ''002''
GROUP BY vend.contact
,C.customername
-- ,w.WarehouseNumber
,item.vendoritem
,bin.station
,ST.CRIBSTTYPE
,R.RBSTTYPE
--,pW.StatusCode
,Bin.packQty
,item.orderqty
,weeklysales.lastweeksales
,weeklysales.wtdsales
,st.cribmin
,st.cribmax
,R.MYMIN
,R.MYMAX
,item.description1
,item.description2
,acbin.binqoh
--,PW.VendorProductCode
-- ,pw.vendorcode
--,pw.vendorname
,item.issuecost
'
SET @SQL_SCRIPT = REPLACE(@template, '{dbname}', @DBNAME)
--set @SQL_SCRIPT = REPLACE(@SQL_SCRIPT, '{MFG}', @MFG)
EXECUTE (@sql_script)
set @COUNTER = @COUNTER +1
end
select * from TMPProductbyCustomer tmp
Tags sql sql-server