TOP 1 bez ORDER BY. Skąd stan ujemny w bazie WMS

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Opisujemy wdrożenie dedykowane u operatora logistycznego z południa Polski, zbudowane na platformie Studio WMS.net. To przypadek jednego magazynu, a nie opis standardu produktu. Przebieg zdarzeń od strony procesu magazynowego znajdziesz w artykule o stanie ujemnym po powtórzonym numerze palety. Tutaj zostajemy przy zapytaniach.

Minus przy poprawnych ilościach

Na lokalizacji źródłowej pojawił się stan ujemny, choć każdy dokument miał właściwą ilość. Paleta z ilością 9,9 została przyjęta i wydana, a po kilkudziesięciu dniach wróciła do magazynu pod tym samym numerem, z inną datą produkcji. Potem dokument ZL przesunął ją na inną lokalizację.

Stan w dpmag liczymy osobno dla każdej daty produkcji. Ruch zapisany ze starą datą obniżył grupę pierwszego przyjęcia do -9,9, a grupa z nową datą została na +9,9. Na lokalizacji docelowej paleta dostała złą datę.

Przykład ilustracyjny. Daty oznaczono literami, a ilości i numery nie pochodzą z magazynu.

Sekwencję da się odtworzyć co do sekundy. ZL założony na ekranie zapisuje jeszcze datę poprawną, wziętą ze stanu. Po około 47 sekundach wózek rozpoczyna zadanie, a po kolejnych 4 sekundach zapis realizacji nadpisuje datę i partię. Dowodem są czasy zapisane w dokumentach i w zadaniach wózka (tabela dptrm), między innymi w kolumnach TR_KIEDY_R i TR_KIEDY_D, oraz wpisy w historii operacji.

Zapytanie, które bierze pierwszy wiersz

Zapytania wózka czytały partię i datę produkcji prostym wzorcem: pierwszy wiersz z danym numerem palety. W T-SQL wygląda to tak.

SELECT TOP 1 seria, data_prod
FROM dpmag
WHERE paleta = @paleta;

Przykład ilustracyjny, nazwy obiektów uproszczone.

Przy jednym przyjęciu pod danym numerem wynik jest jednoznaczny. Przy dwóch zależy od tego, który wiersz silnik poda jako pierwszy, a kolejności bez ORDER BY nikt nie gwarantuje. W praktyce wynikiem był najstarszy zapis. Dokumentacja klauzuli TOP zaleca, by w instrukcji SELECT zawsze łączyć ją z ORDER BY, a opis klauzuli ORDER BY zawiera przykład zapytania bez sortowania, które zwraca wiersze w kolejności nieokreślonej.

Bez ORDER BY klauzula TOP zwraca dowolne wiersze spełniające warunek. Wynik, który zwykle bywa najstarszy, jest przypadkiem planu wykonania, a nie regułą zapytania.

Wzorzec miało sześć zapytań wózka, między innymi te od zapisu ZL i zamiany palet. Inne obsługują paletę innego indeksu albo dodają pozycję ZL do strefy pośredniej.

Zapytania, które wyłapują problem wcześniej

Stan ujemny wykrywa zapytanie grupujące pozycje dpmag po kolumnach, które identyfikują towar w danym miejscu, oraz po dacie produkcji. Zostają grupy z sumą poniżej zera.

SELECT indeks, lokalizacja, magazyn, paleta, seria, data_prod,
       SUM(ilosc) AS suma
FROM dpmag
GROUP BY indeks, lokalizacja, magazyn, paleta, seria, data_prod
HAVING SUM(ilosc) < 0;

Przykład ilustracyjny, nazwy obiektów uproszczone.

W opisywanym wdrożeniu takie zapytanie działa cyklicznie w zadaniu agenta SQL. W edycji Express agenta nie ma, więc harmonogram trzeba tam zbudować inaczej. Nie należy go mylić z tabelą _jobs, która opisuje zadania automatyczne samej platformy.

Drugie zapytanie szuka przyczyny, a nie skutku. Wskazuje numery palet z więcej niż jedną datą produkcji.

SELECT paleta, COUNT(DISTINCT data_prod) AS liczba_dat
FROM dpmag
GROUP BY paleta
HAVING COUNT(DISTINCT data_prod) > 1;

Przykład ilustracyjny, nazwy obiektów uproszczone.

Zapytanie przegląda całą historię pozycji, więc wynik czytamy razem z bieżącym stanem palety. Można go też wystawić w raporcie, a o raportowaniu z bazy SQL Server piszemy osobno.

Funkcja tabelaryczna zamiast TOP 1

Poprawka polega na odczycie ze stanu palety, a nie z pierwszego zapisu o jej numerze. Funkcja tabelaryczna zwraca trzy dane: partię, datę produkcji i termin. Wybiera grupę z dodatnią ilością i porządkuje kandydatów według trzech kryteriów. Pierwsze to lokalizacja źródłowa, drugie partia, trzecie najnowszy ruch. Z wyboru wyłączamy własną parę ZL i własny dokument. Gdy palety nie ma na stanie, funkcja sięga po ostatnie przyjęcie.

CREATE FUNCTION dbo.f_stan_palety
    (@paleta varchar(18), @lokalizacja varchar(20), @seria varchar(20))
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (1) g.seria, g.data_prod, g.termin
    FROM (
        SELECT seria, data_prod, termin, lokalizacja,
               SUM(ilosc) AS suma, MAX(kiedy) AS ostatni_ruch
        FROM dpmag
        WHERE paleta = @paleta
        GROUP BY seria, data_prod, termin, lokalizacja
        HAVING SUM(ilosc) > 0
    ) AS g
    ORDER BY CASE WHEN g.lokalizacja = @lokalizacja THEN 0 ELSE 1 END,
             CASE WHEN g.seria = @seria THEN 0 ELSE 1 END,
             g.ostatni_ruch DESC
);

Szkic funkcji bez wyłączenia własnej pary ZL i bez ścieżki dla palety spoza stanu. Przykład ilustracyjny, nazwy obiektów uproszczone.

W zapytaniach typu UPDATE funkcję wywołuje OUTER APPLY. Gdy nic nie zwróci, w ZL zostaje wartość już zapisana. Kod JS ekranu zostaje bez zmian. To samo dotyczy parametrów i reszty logiki.

UPDATE z
SET z.seria     = ISNULL(s.seria, z.seria),
    z.data_prod = ISNULL(s.data_prod, z.data_prod)
FROM zl_pozycje AS z
OUTER APPLY dbo.f_stan_palety(z.paleta, z.lokalizacja_zr, z.seria) AS s;

Przykład ilustracyjny, nazwy obiektów uproszczone.

Wdrożenie zmiany w repozytorium zapytań

Zapytania wózka nie są zapisane w kodzie aplikacji. Ich treść leży w repozytorium zapytań w bazie, więc poprawka jest zmianą zapisanych treści, a nie nową wersją programu. W tym przypadku skrypt wdrożeniowy podmienił sześć zapytań. Składa się z czterech elementów kontrolnych.

1. Zgodność z kopią. Treść w bazie musi zgadzać się z kopią w pliku.
2. Liczba wystąpień. Zmieniany fragment ma wystąpić tyle razy, ile zakładaliśmy.
3. Kompilacja. Nowa treść jest kompilowana przed zapisem.
4. Tryb próbny. Skrypt przechodzi całą ścieżkę bez zapisu zmian.

Do wdrożenia dołączony jest osobny skrypt wycofania z oryginalnymi treściami zapytań. Po zapisie trzeba zresetować pamięć podręczną repozytorium zapytań, żeby aplikacja czytała nową treść.