Przejdź do treści

Przykłady zapytań SQL

Zapytania SQL służą w Navigatorze m.in. do filtrów list, źródeł danych pól i warunków w procedurach obiegu. Z tego artykułu dowiesz się, jak zbudować kilka często potrzebnych zapytań i jakich tabel do tego użyć.

Zanim zaczniesz

  • Większość tabel ma kolumnę Deleted. Usunięte rekordy zostają w bazie, więc zawsze dodawaj warunek Deleted = 0.
  • ID tabel, np. w relacjach, sprawdzisz zapytaniem SELECT ID, Name FROM Tb.
  • Zapytania z tego artykułu sprawdzono na bazie Navigatora. Przed użyciem podmień GUID-y i ID na swoje.

Statusy typu dokumentu

Zapytanie zwraca statusy jednego typu dokumentu z ikonami, np. do filtra listy po statusie:

SELECT DoPa.Name ID, [dbo].fn_Get_Ic_ById(Ic.Icon, DoPa.Name) + ' ' + DoPa.Name Value
FROM DoPa
JOIN DoPa DoPaP ON DoPa.Parent = DoPaP.ID
LEFT JOIN Ic ON DoPa.IcID = Ic.ID
WHERE DoPaP.GUID = 'F5B91AB3-159B-4E33-AA91-273F396CE20B'
AND DoPa.Deleted = 0
ORDER BY DoPa.[Order]
  • Typy i statusy dokumentów są w tej samej tabeli DoPa. Status wskazuje swój typ w kolumnie Parent, dlatego tabela jest dołączona do siebie samej jako DoPaP.
  • W warunku podaj GUID typu dokumentu.
  • fn_Get_Ic_ById wstawia ikonę statusu z tabeli Ic.
  • [Order] to kolejność statusów z konfiguracji typu.

Stanowiska z użytkownikami

Zapytanie zwraca elementy struktury organizacyjnej z osobami na stanowiskach, np. „Księgowa (Anna Nowak)”, „Księgowa (nieobsadzone)” albo „Dział controllingu (wszyscy)”:

SELECT
    St.ID,
    CASE
        WHEN StPa.StLeID = 5 AND Us.ID > 0 THEN St.Name + ' (' + Us.Name + ' ' + Us.Surname + ')'
        WHEN StPa.StLeID = 5 AND Us.ID IS NULL THEN St.Name + ' (nieobsadzone)'
        ELSE St.Name + ' (wszyscy)'
    END Value
FROM St
JOIN StPa ON St.StPaID = StPa.ID
LEFT JOIN UsSt ON St.ID = UsSt.StID AND UsSt.Deleted = 0
LEFT JOIN Us ON UsSt.UsID = Us.ID AND UsSt.Deleted = 0
WHERE St.Deleted = 0
Tabela Zawartość
St Elementy struktury organizacyjnej.
StPa Wzorce struktury. StLeID to poziom wzorca, a 5 to stanowisko (lista poziomów: SELECT * FROM StLe).
UsSt Przypisania użytkowników do stanowisk. Nazwa łączy nazwy tabel Us i St, a kolumny UsID i StID wskazują ich rekordy.
Us Użytkownicy.

Złączenia LEFT JOIN zostawiają w wyniku także stanowiska bez użytkownika. Warunek UsSt.Deleted = 0 pomija osoby, które już nie pracują na stanowisku.

Niezakończone obiegi dokumentów powiązanych

Zapytanie sprawdza, czy dokumenty utworzone z pozycji danego dokumentu, np. zamówienia z pozycji zapotrzebowania, mają jeszcze niezakończone obiegi. Zwraca Tak, gdy wszystkie są zakończone, i Nie, gdy któryś trwa albo któraś pozycja nie ma jeszcze dokumentu:

SELECT CASE WHEN EXISTS (
    SELECT Do.ID AS DocID, Do.PersonalNumber, Do.Description
        , element1.AsID AS AsID, element1.AsName
        , rael.RelationTbID, rael.RelationTableID
        , parel.Deleted AS pareldel, parel.DoID AS pareldoid
        , pardoc.PersonalNumber, pardoc.Deleted AS DocDeleted
        , wkfl.Deleted AS WoDeleted, wkfl.WoSaID, WoSa.Name
    FROM Do
    LEFT JOIN DoEl element1 ON element1.DoID = Do.ID
    LEFT JOIN Ra rael ON element1.ID = rael.TableID
    LEFT JOIN DoEl parel ON parel.ID = rael.RelationTableID AND parel.Deleted = 0
    LEFT JOIN Do pardoc ON pardoc.ID = parel.DoID AND pardoc.Deleted = 0
    LEFT JOIN Wo wkfl ON wkfl.TableID = pardoc.ID
    LEFT JOIN WoSa ON WoSa.ID = wkfl.WoSaID
    WHERE Do.ID = @DocumentID
    AND (rael.RelationTbID IS NULL OR rael.RelationTbID = 50)
    AND (wkfl.[Table] IS NULL OR wkfl.[Table] = 'Do')
    AND (rael.RelationTableID IS NULL
        OR (pardoc.PersonalNumber IS NOT NULL AND wkfl.WoSaID <> 4 AND wkfl.Deleted = 0))
)
THEN 'Nie'
ELSE 'Tak'
END
Tabela Zawartość
Do Dokumenty.
DoEl Pozycje dokumentów. Dołączona dwa razy: pozycje dokumentu bazowego (element1) i pozycje z nimi powiązane (parel).
Ra Relacje między obiektami. RelationTbID = 50 to relacja z pozycją dokumentu.
Wo Obiegi dokumentów.
WoSa Statusy obiegów. 4 to Zakończony.

Warunki w WHERE pozostawiają tylko pozycje, które są wciąż w realizacji:

  • rael.RelationTbID IS NULL OR rael.RelationTbID = 50: pozycja bez relacji albo z relacją do innej pozycji,
  • rael.RelationTableID IS NULL: pozycja, z której nie powstał jeszcze żaden dokument,
  • pardoc.PersonalNumber IS NOT NULL: pomija relacje, które zostały po usuniętych pozycjach,
  • wkfl.WoSaID <> 4 AND wkfl.Deleted = 0: obieg dokumentu powiązanego nie jest zakończony.

Dokument powiązany bez obiegu traktuje się jak zakończony. Żeby zobaczyć listę pozycji zamiast odpowiedzi Tak albo Nie, uruchom samo zapytanie z wnętrza EXISTS.

Zapytanie możesz użyć np. w akcji Dokument: Zmień wartość pól, żeby wpisać wynik do pola dokumentu. Po wpisaniu zapytania kliknij Generuj parametry i przypisz @DocumentID do Id dokumentu.

Co dalej