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 warunekDeleted = 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 kolumnieParent, dlatego tabela jest dołączona do siebie samej jakoDoPaP. - W warunku podaj GUID typu dokumentu.
fn_Get_Ic_ByIdwstawia ikonę statusu z tabeliIc.[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¶
- Rozszerzenia .NET: logika w C#.