loop in sql

Status
Niet open voor verdere reacties.
M

manueld

Gebruiker
Lid geworden
12 feb 2012
Berichten
216
MSSQL 2008

ik wil graag het volgende. ik heb een tabel met artikelnummers en aantallen. daarnaast heb ik een tabel met artikelnummers met bijbehorende serie nummers welke uniek zijn. ik wil bij elke record in de eerste tabel het aantal records die hetzelfde artikelnummer hebben opzoeken en aanpassen

stapsgewijs

ga naar record 1 in tabel 1

lees het artikelnummer en aantal.

zoek in tabel 2 de eerste (aantal) records die het artkikelnummer hebben.

update deze records(set Y in het veld)

ga terug naar tabel 1 markeer het aantal gevonden records in tabel 2 als waarde in tabel 1( als er genoeg artikelen op voorraad zijn is dit gelijk aan het gewenste aantal, anders lager)

herhaal tot alle records uit tabel 1 geweest zijn


tabel 1

[artikelnummer],[GewenstAantal],[GevondenAantal]


tabel 2

[Artikelnummer],[Serienummer(uniek)],[Y/N gemarkeerd]
 
Je moet met een "CURSOR" een heel eind komen als een algemene oplossing, maar ergens in mijn achterhoofd knaagt de gedachte dat het veel makkelijker moet kunnen. Of eigenlijk dat het design van je tabellen nogal schort eerlijk gezegd. Wat je hier doet zou eigenlijk in een aparte kruistabel moeten en niet via replicatie.
 
het design is echt wel goed maar dit is een extra funktie. de gebruiker krijgt in de mail een tabel met artikelen die besteld zijn. hij moet deze tabel kunnen kopieren en plakken in een sql tabel. vervolgens moet die tabel en aantallen gezocht worden in de voorraad tabel. aan het design is dan ook niets te veranderen. ik ben inmiddels een heel eind met de volgende code maar ga hier vandaag op verder puzzelen

problemen op dit moment nog zijn dat hij alleen het eerste part vind en hij markeerd de gevonden records nog niet.

req is tabel 1 en partno is tabel 2 rqty is gevraagde aantallen en fqty is de gevonden aantallen. PN is partnumber

Code: 
DECLARE @Counter INT
DECLARE @PN VARCHAR(50)
DECLARE @rQTY INT
DECLARE @Fqty INT
SET @Fqty = 1
SET @Counter = 0
WHILE @Counter <= (SELECT Count([partno]) FROM [LMD_Process_Control].[dbo].[req])
BEGIN
--SELECT top 1[partno],rqty FROM [LMD_Process_Control].[dbo].[req]
sET @PN = (SELECT top 1[partno] FROM [LMD_Process_Control].[dbo].[req]WHERE FQTY IS NULL)
SET @rQTY =(SELECT top 1 rqty FROM [LMD_Process_Control].[dbo].[req] WHERE FQTY IS NULL)

UPDATE [LMD_Process_Control].[dbo].[req]
   SET [FQTY] = @Fqty
 WHERE PARTNO = @PN

pRINT @RQTY
PRINT @PN

--PRINT @Counter
SET @Counter += 1
END
 
Zie hier Cursor, ik zou het niet zelf gaan bouwen, vooral ook omdat bovenstaande absoluut een keer de mist in gaat in het geval er een submit is terwijl je query loopt.:

https://msdn.microsoft.com/en-us/library/ms180169.aspx (zie voornamelijk onderaan de pagina)

Voor een report moet je echt niet gaan frutten in je core tabellen. In deze blijf ik erbij dat het design niet optimaal is. Dat je het misschien niet kan veranderen is iets heel anders, maar dat maakt het design niet goed.
 
mm daar kun je gelijk in hebben dat met een cursor het toch beter werkt. ik ga dat maar eens ombouwen. ik heb het op zich nu aardig werken op mijn manier
Code: 
DECLARE @Counter INT
DECLARE @PN VARCHAR(50)
DECLARE @RQTY INT
DECLARE @Fqty INT
DECLARE @REQID INT
SET @REQID = (SELECT TOP 1 [REQID]  FROM [LMD_Process_Control].[dbo].[req] WHERE FQTY IS NULL)
PRINT @REQID
SET @Fqty = 0
SET @Counter = 0

WHILE @Counter < (SELECT Count([partno]) FROM [LMD_Process_Control].[dbo].[req] WHERE REQID = @REQID)

BEGIN
PRINT N'COUNTER IS ' + CAST (@COUNTER AS VARCHAR)
--SELECT top 1[partno],rqty FROM [LMD_Process_Control].[dbo].[req]
sET @PN = (SELECT top 1[partno] FROM [LMD_Process_Control].[dbo].[req]WHERE FQTY IS NULL AND REQID = @REQID)
SET @rQTY =(SELECT top 1 rqty FROM [LMD_Process_Control].[dbo].[req] WHERE FQTY IS NULL AND REQID = @REQID)
PRINT @PN
pRINT N'Requested ' + cast (@rQTY as varchar)

set @Fqty =(SELECT TOP (@rQTY) Count([SN]) FROM [LMD_Process_Control].[dbo].[itemtest] where partnumber = @PN AND ITEMREQID IS NULL)
pRINT N'FOUND ' + cast (@FQTY as varchar)
UPDATE TOP (@rQTY) [LMD_Process_Control].[dbo].[itemtest]SET ItemReqId = @REQID where partnumber = @PN AND ITEMREQID IS NULL

UPDATE [LMD_Process_Control].[dbo].[req]
SET [FQTY] = @Fqty
WHERE PARTNO = @PN AND REQID = (@REQID);


--PRINT @Counter
SET @Counter += 1
END
 
inmiddels omgezet in cursor (zie code) heb nu een probleem. in de sql management studio loopt hij goed maar als passthrough vanuit access krijg ik foutmeldingen ook als stored procedure vanuit access aangestuurd krijg ik dezelfde error
ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]There are no rows in the current fetch buffer. (#16931) [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (#3621) [Microsoft][ODBC SQL Server Driver][SQL Server]1 (#0)
Klik om te vergroten...

Code: 
-- find matching parts


DECLARE @TEST INT
DECLARE @Counter INT
DECLARE @PN VARCHAR(50)
DECLARE @RQTY INT
DECLARE @Fqty INT
DECLARE @REQID INT
DECLARE @LOCATION VARCHAR(50)
DECLARE @WAREHOUSE VARCHAR(50)
SET @REQID = (SELECT TOP 1 [ReqID]  FROM [LMD_Process_Control].[dbo].pasteimput WHERE FQTY IS NULL)
PRINT @REQID
SET @Fqty = 0
SET @Counter = 0

DECLARE REQUEST_CURSOR CURSOR FOR 
SELECT  [partno]
      ,[Rqty]
      ,warehouse
      ,location

  FROM [LMD_Process_Control].[dbo].pasteimput
  WHERE REQID = (@REQID);

OPEN REQUEST_CURSOR
FETCH NEXT FROM REQUEST_CURSOR
INTO @PN,@RQTY,@WAREHOUSE,@LOCATION

WHILE @@FETCH_STATUS = 0
BEGIN

--SELECT top 1[partno],rqty FROM [LMD_Process_Control].[dbo].[req]
--sET @PN = (SELECT top 1[partno] FROM [LMD_Process_Control].[dbo].[req]WHERE FQTY IS NULL AND REQID = @REQID)
--SET @rQTY =(SELECT top 1 rqty FROM [LMD_Process_Control].[dbo].[req] WHERE FQTY IS NULL AND REQID = @REQID)
PRINT @PN
pRINT N'Requested ' + cast (@rQTY as varchar)



-- OPEN ITEM TABLE AS CURSOR

DECLARE ITEM_CURSOR CURSOR DYNAMIC FOR 
 SELECT  [SN],ItemKeyinDate,ItemReqId FROM [LMD_Process_Control].[dbo].[tblExItem] where partnumber = @PN AND ITEMREQID IS NULL AND ItemWarehouse = @WAREHOUSE AND ItemLocation = @LOCATION AND ItemBlocked = 0--ORDER BY ItemKeyinDate 
FOR UPDATE;
OPEN ITEM_CURSOR;
FETCH NEXT FROM ITEM_CURSOR;
WHILE @Counter < @RQTY
BEGIN 
UPDATE [LMD_Process_Control].[dbo].[tblExItem] 
SET ItemReqId = @REQID
WHERE CURRENT OF ITEM_CURSOR;
SET @COUNTER = @Counter+1
PRINT @cOUNTER
FETCH NEXT FROM ITEM_CURSOR;
END
CLOSE ITEM_CURSOR;
DEALLOCATE ITEM_CURSOR

UPDATE [LMD_Process_Control].[dbo].pasteimput
SET [FQTY] = (SELECT COUNT (SN) FROM [LMD_Process_Control].[dbo].[tblExItem] where partnumber = @PN AND ITEMREQID = @REQID)
WHERE PARTNO = @PN AND REQID = (@REQID);

FETCH NEXT FROM REQUEST_CURSOR
INTO @PN,@RQTY,@WAREHOUSE,@LOCATION

END 
CLOSE REQUEST_CURSOR;
DEALLOCATE REQUEST_CURSOR;
 
Access is niet volledig transact compatible, het is dus moeilijk om aan te geven of dit een probleem is met access of in de SQL zelf. Je kunt de gratis versie van SQL server eens proberen aan te zwengelen en kijken of dat dezelfde problemen geeft.
 
inmiddels de oplossing. het probleem is dat als je een fout in studio krijgt studio gewoon door gaat met de volgende opdracht. het bleek dat als hij niet kon vinden wat hij zocht hij een voor access fatale fout gaf terwijl studio de fout weergeeft en verder gaat met de volgende stap. de code moet dus perfect werken als je hem in access wilt gebruiken. ik heb er nu een if statment in gemaakt die eerst kijk hoeveel er te vinden zijn en dat aantal doorloopt hij ipv hoeveel hij wil vinden. nu geen fout in studio dus ook niet in access.
Code: 
-- find matching parts


DECLARE @TEST INT
DECLARE @Counter INT
DECLARE @PN VARCHAR(50)
DECLARE @RQTY INT
DECLARE @Fqty INT
DECLARE @REQID INT
DECLARE @LOCATION VARCHAR(50)
DECLARE @WAREHOUSE VARCHAR(50)
DECLARE @EQTY INT --FOUND IN TBLEXITEM FOR AMOUNT OF LOOP
SET @REQID = (SELECT TOP 1 [ReqID]  FROM [LMD_Process_Control].[dbo].pasteimput WHERE FQTY IS NULL)
PRINT @REQID
SET @Fqty = 0
SET @Counter = 0

DECLARE REQUEST_CURSOR CURSOR FOR 
SELECT  [partno]
      ,[Rqty]
      ,warehouse
      ,location

  FROM [LMD_Process_Control].[dbo].pasteimput
  WHERE REQID = (@REQID);

OPEN REQUEST_CURSOR
FETCH NEXT FROM REQUEST_CURSOR
INTO @PN,@RQTY,@WAREHOUSE,@LOCATION

WHILE @@FETCH_STATUS = 0
BEGIN

--SELECT top 1[partno],rqty FROM [LMD_Process_Control].[dbo].[req]
--sET @PN = (SELECT top 1[partno] FROM [LMD_Process_Control].[dbo].[req]WHERE FQTY IS NULL AND REQID = @REQID)
--SET @rQTY =(SELECT top 1 rqty FROM [LMD_Process_Control].[dbo].[req] WHERE FQTY IS NULL AND REQID = @REQID)
PRINT @PN
pRINT N'Requested ' + cast (@rQTY as varchar)

SET @EQTY = (SELECT  COUNT([SN]) FROM [LMD_Process_Control].[dbo].[tblExItem] where partnumber = @PN AND ITEMREQID IS NULL AND ItemWarehouse = @WAREHOUSE AND ItemLocation = @LOCATION AND ItemBlocked = 0 );
PRINT @EQTY

SET @EQTY =CAST(CASE  WHEN @EQTY > @RQTY   
                     THEN @RQTY
                  ELSE @EQTY END AS INT );


-- OPEN ITEM TABLE AS CURSOR
PRINT N'SELECT ITEMCURSOR'
DECLARE ITEM_CURSOR CURSOR DYNAMIC FOR 
 SELECT  [SN],ItemKeyinDate,ItemReqId FROM [LMD_Process_Control].[dbo].[tblExItem] where partnumber = @PN AND ITEMREQID IS NULL AND ItemWarehouse = @WAREHOUSE AND ItemLocation = @LOCATION AND ItemBlocked = 0 ORDER BY ItemKeyinDate 
FOR UPDATE;
OPEN ITEM_CURSOR;
FETCH NEXT FROM ITEM_CURSOR;
WHILE @Counter < @EQTY 
BEGIN 
PRINT N'UPDATE TBLEXITEM'
UPDATE [LMD_Process_Control].[dbo].[tblExItem] 
SET ItemReqId = @REQID
WHERE CURRENT OF ITEM_CURSOR;
SET @COUNTER = @Counter+1
PRINT @cOUNTER

FETCH NEXT FROM ITEM_CURSOR;
END
CLOSE ITEM_CURSOR;
DEALLOCATE ITEM_CURSOR
PRINT N'UPDATE PASTEIMPUT'
UPDATE [LMD_Process_Control].[dbo].pasteimput
SET [FQTY] = (SELECT COUNT (SN) FROM [LMD_Process_Control].[dbo].[tblExItem] where partnumber = @PN AND ITEMREQID = @REQID)
WHERE PARTNO = @PN AND REQID = (@REQID);
set @Counter = 0
FETCH NEXT FROM REQUEST_CURSOR
INTO @PN,@RQTY,@WAREHOUSE,@LOCATION

END 
CLOSE REQUEST_CURSOR;
DEALLOCATE REQUEST_CURSOR;
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan