koenslootmans
Starting Member
1 Post |
Posted - 2013-04-05 : 08:39:36
|
Hello,I have an openquery which is being passed variables in a procedureIt compiles fine but When i run it i get the following error:Msg 245, Level 16, State 1, Procedure GET_SLOWMOVERSINFO, Line 24Conversion failed when converting the nvarchar value 'select count(1) as nrofreferencehere is the procedure...Create PROCEDURE [dbo].[GET_SLOWMOVERSINFO] @PickLocation nvarchar(30), @StorageLocationWarehouse nvarchar(30), @StorageLocationAisle nvarchar(30), @PickBinType intASBEGIN SET NOCOUNT ON; -- Insert statements for procedure here insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','started',' '); delete from dbo.KPI_Inventory_Health where datediff(DAY,dateofloading, SYSDATETIME()) = 0 and PickBinType = @PickBinType and PickLocation = @PickLocation and @StorageLocationAisle = StorageLocationAisle and @StorageLocationWarehouse = StorageLocationWarehouse; Declare @TSQL nvarchar(4000); set @TSQL = 'select count(1) as nrofreferences, sqdept, Product_Type, Product_Status, sum(qtystocked) as qtystocked, sum(boxesstored) as boxesstocked, sum(valuestocked) as salesvaluestocked from (select sqdept||''-''||sqstyl||''-''||sqcolr||''-''||sqsize as a_reference, sqdept, rmdesc, MAS.RMFRCT, sum(bstk.sqqtys) as qtystocked, sum(bstk.sqqtys*rmsspr) as valuestocked, sum(bstk.sqqtys)/rmpunu as boxesstored, qty_out, max_box_out, avg_box_out, boxes_out, nof_weeks_picked as nbr_times_picked, rmpunu, rmsspr, last_act_yy, last_act_ww, lastactivedate, case when (max_box_out <= 2 and max_box_out >1 and sysdate - rmfrct >28 ) then ''Slow mover'' when (max_box_out <= 1 and sysdate - rmfrct >28) then ''Very slow mover'' else ''Normal'' end as Product_type, case when (sysdate - rmfrct >28 and sysdate - lastactivedate > 28 ) then ''Sleeping'' when (sysdate - rmfrct >140 and sysdate - lastactivedate > 140 ) then ''Death'' When (sysdate - rmfrct <= 28) then ''NEW'' else ''Normal'' end as Product_status, sqdept as dept from stkbstk bstk, stkmas mas, pick_aisle_loc ais, (select qrrdept,qrrstyl, qrrsize,qrrcolr, max(lastactivedate) as lastactivedate from (select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick group by qrdept, qrstyl,qrsize,qrcolr union select qrdept as qrrdept, qrstyl as qrrstyl, qrsize as qrrsize, qrcolr as qrrcolr, max(qrcreated_dt) as lastactivedate from stkpick_hist group by qrdept, qrstyl,qrsize,qrcolr ) group by qrrdept,qrrstyl,qrrsize,qrrcolr ) , ( select max(crea_yy) as last_act_yy, max(crea_ww) as last_act_ww, mas.rmdesc as description, qrdept, qrstyl, qrcolr, qrsize, sum(quantityout) as qty_out, sum(quantityout/rmpunu) as boxes_out , max(quantityout/rmpunu) as max_box_out, avg(quantityout/rmpunu) as avg_box_out, count(1) as nof_weeks_picked from ( select crea_yy, crea_ww, qrdept, qrstyl, qrcolr, qrsize, sum(quantityOut) as quantityOUT from ( select to_char(qrcreated_dt,''yy'') as crea_yy, to_char(qrcreated_dt,''ww'') as crea_ww, qrdept, qrstyl, qrcolr, qrsize, sum(qrrqty) as quantityOUT from Disport.stkpick pik, disport.pick_aisle_loc ais where pik.qraisl = ais.AISLE and aisle_com '''+ @PickLocation +''' and qrcreated_dt > sysdate - 140 group by to_char(qrcreated_dt,''yy''), to_char(qrcreated_dt,''ww''), qrdept, qrstyl, qrcolr, qrsize union select to_char(qrcreated_dt,''yy'') as crea_yy, to_char(qrcreated_dt,''ww'') as crea_ww, qrdept, qrstyl, qrcolr, qrsize, sum(qrrqty) as quantityOUT from Disport.stkpick_hist hist, Disport.pick_aisle_loc ais where qraisl = ais.AISLE and aisle_com '''+@PickLocation+''' and qrcreated_dt > sysdate - 140 group by to_char(qrcreated_dt,''yy''), to_char(qrcreated_dt,''ww''), qrdept, qrstyl, qrcolr, qrsize ) group by crea_yy, crea_ww, qrdept, qrstyl, qrcolr, qrsize ) p1, stkmas mas where qrdept = rmdept and qrstyl = rmstyl and qrcolr = rmcolr and rmcomp = 1 and qrsize = rmsize group by qrdept, qrstyl, qrcolr, qrsize, rmpunu, rmdesc ) p2 where qrdept(+) = sqdept and qrstyl(+) = sqstyl and qrcolr(+) = sqcolr and qrsize(+) = sqsize and qrrstyl = qrstyl and qrrsize = qrsize and qrrcolr = qrcolr and qrrdept = qrdept and sqdept = rmdept and sqstyl = rmstyl and sqcolr = rmcolr and sqsize = rmsize and rmcomp = 1 and sqaisl = ais.AISLE and aisle_com '''+@StorageLocationWarehouse+''' and Sqaisle '''+@StorageLocationAisle+''' and SQBTYP = '''+@PickBinType+''' group by sqdept, sqstyl, sqcolr, sqsize, qty_out, boxes_out, last_act_yy, last_act_ww, rmpunu, rmfrct, rmsspr,lastactivedate, rmdesc, avg_box_out, max_box_out, nof_weeks_picked) group by sqdept, product_type,product_status' set @TSQL = N'insert into dbo.KPI_Inventory_Health select SYSDATETIME() as dateofloading, '''+@PickBinType+''' as PickBinType, '''+@PickLocation+''' as PickLocation, '''+@StorageLocationWarehouse+''' as StorageLocationWarehouse, '''+@StorageLocationAisle+''' as StorageLocationAisle, NROFREFERENCES,SQDEPT, PRODUCT_TYPE , PRODUCT_STATUS, qtystocked,boxesstocked,salesvaluestocked from openquery(Disport,''' + REPLACE(@TSQL, '''', '''''') + ''')' print @TSQL; exec (@TSQL); insert into dbo.ProcedureLog values(SYSDATETIME(),'GET_SLOWMOVERSINFO','completed','ALL'); ENDcommit |
|