Data utworzenia: 22.05.2018 Numer referencyjny: SXJ045 Publiczny

Najczęściej używane funkcje SQL w XL-u | Narzędzie ERP XL

Wprowadzenie

Tworząc różnego rodzaju funkcjonalności  w Comarch ERP XL odnoszące się do bazy danych zachodzi często potrzeba skorzystania z funkcji zwracającej pewną wartość.

Funkcje SQL pełnią rolę "podprogramów" wyliczających pewne dane na podstawie zadanych kryteriów (zwanych parametrami lub argumentami funkcji) i zwracających przeliczony wynik. Używa się ich z kilku powodów:

  • Ułatwiają i przyśpieszają kodowanie - zamiast za każdym razem tworzyć rozbudowany kod to można skorzystać z gotowej funkcji. Przykładem takiej funkcji jest CDN.NumerDokumentu zwracającej numer dokumentu handlowego.
  • Poprawiają i upraszczają czytelność kodu - zamiast tworzyć zapytanie odwołujące się do wielu tabel można stworzyć zapytanie odwołujące się do jednej tabeli, a dane z tabel "nieobecnych" zostaną pzreliczone przez funkcję). Przykładem takiej funkcji jest CDN.AtrPobierzWartosc zwracającej wartość wskazanego atrybutu, którzy przechowywany jest w zewnętrznej tabeli.
  • Unifikują i ujednolicają kod - jeśli pewnego rodzaju przeliczenia (zapytania SQL) mają być wykorzystywane w wielu różnych miejscach to miejsca te powinny odnosić się do funkcji, którą w razie potrzeby łatwo jest zmodyfikować oddziaływując automatycznie na wszystkie te miejsca. Przykładem może być funkcja CDN.Slownie zwracająca liczbę w postaci słownej.
  • Zapobiegają błędom powtórzeń rekordów w wynikach - funkcje zawsze zwracają pojedynczą wartość skalarną (poza wyjątkiem funkcji zwaracających recordset), w przypadku łączeń tabel istnieje ryzyko powielenia rekordów. Przykładem znów może być funckja CDN.AtrPobierzWartosc.
  • Umożliwiają sięgnięcie do pewnych danych w kontekście, w którym może nie być to możliwe lub trudne. Takie funkcje zwykle tworzy się samodzielnie, na przykład w konieczności sięgnięcia do pewnych danych na elemencie schematu księgowania.

W programie funkcje wykorzystuje się najczęściej w następujących miejscach:

  • Wydruki w Crystal Reports
  • Raporty SQL i Raporty typu wykres
  • Filtry
  • Schematy księgowań
  • doraźne polecenia SQL zmierzające odczytu pewnych danych z bazy lub do ingerencji w bazę danych

W tym artykule przedstawiam zastosowanie i składnię najczęściej używanych przeze mnie funkcji SQL do pracy na bazie danych ERP XL. Opis dostępnych parametrów funkcji ograniczam do minimum w odniesieniu do zastosowania w ERP XL. 

Abyś odniósł z niego jakąś korzyść powinieneś posiadać podstawową wiedzę o SQL. 

Jeśli poszukujesz informacji na temat znaczenia dat w programie to przeczytaj artykuł Miejsce i znaczenie dat dokumentów w bazie danych.

DATEADD

Opis
Funkcja zamienia postać liczbową daty na postać czytelną. Daty w bazie danych ERP XL przechowywane są w trzech różnych postaciach, z czego dwie z nich są w postaci liczbowej: data w formacie Clarion i data w formacie TimeStamp.

Format Clarion określa liczbę dni jaka upłynęła od dnia 1800/12/28. Przykładowo liczba 79403 oznacza datę 22.05.2018.

Format TimeStamp określa liczbę sekund jaka upłynęła od dnia 1990/01/01. Przykładowo liczba 895845120 oznacza datę 22.05.2018 godzina 13:52.

Składnia
DATEADD (param1, param2, param3), gdzie parametry przyjmują następującą wartość:

  • param1 - w zależności od formatu daty - Clarion: day, TimeStamp: s
  • param2 - liczba określająca datę
  • param3 - w zależności od formatu daty - Clarion: '18001228', TimeStamp: '19900101'

Przykładowe zastosowanie
Zapytanie zwraca datę wystawienia dokumentu handlowego:

select dateadd(day, trn_data2, '18001228') from cdn.tranag

Zapytanie zwraca datę i godzinę zalogowania się do programu:

select dateadd(s, ses_start, '19900101'),SES_OpeIdent from cdn.sesje

DATEDIFF

Opis
Funkcja zamienia postać czytelną daty na postać liczbową, czyli jest przeciwieństwem funkcji DATEADD. Wykorzystywana najczęściej do konwersji daty na format Clarion, rzadziej na TimeStamp.

Składnia
DATEDIFF (param1, param2, param3), gdzie parametry przyjmują następującą wartość:

  • param1 - w zależności od formatu daty - Clarion: day, TimeStamp: s
  • param2 - w zależności od formatu daty - Clarion: '18001228', TimeStamp: '19900101'
  • param3 - data w postaci czytelnej, w formacie  'YYYYMMDD'

Przykładowe zastosowanie
Zapytanie zwraca listę dokumentów handlowych wystawionych w okresie pomiędzy 1 stycznia 2018, a 15 stycznia 2018:

select * from cdn.tranag 
where trn_data2 between datediff(day, '18001228', '20180101') and datediff(day, '18001228', '20180115')

Zapytanie zwraca listę sesji, które rozpoczęły się w dniu 22 maja 2018 w godzinach pomiędzy 7, a 9 rano:

select * from cdn.sesje 
where ses_start between datediff(s, '19900101', '20180522 07:00') and datediff(s, '19900101', '20180522 09:00')

CDN.YMD

Opis
Funkcja dostarczona przez Comarch zamienia datę zapisaną w trzech oddzielnych polach (Rok, Miesiąc, Dzień) na datę w postaci skalarnej, czytelnej. Jest to trzeci spośród dwóch stosowanych sposobów zapisu daty w bazie ERP XL. Wykorzystywany głównie na rejestrze VAT i na dziennikach księgowych.

Składnia
CDN.YMD (Rok, Miesiac, Dzien)

Przykładowe zastosowanie
Zapytanie wyświetla datę dekretu księgowego:

select CDN.YMD(dzk_rok,dzk_miesiac,dzk_dzien) from cdn.dziennik

FORMAT

Opis
Przydatna funkcja formatująca datę na pożądany format. Dla standardowego ustawienia bazy MS SQL funkcja DATEADD zwraca datę w formacie [Rok-Miesiąc-Dzień Godzina], gdzie w przypadku odwołania do daty w formacie Clarion godzina jest zawsze puta. Funkcja FORMAT pozwala na zmianę sposobu wyświetlania tej daty, w szczególności na usunięcie niepożądanej daty

Składnia
FORMAT(data, format), gdzie parametry przyjmują nastepujące wartości:

  • data - bazowa wartość daty
  • format - sposób formatowania daty, na przykład: 'dd.MM.yyyy'

Przykładowe zastosowanie
Zapytanie zwraca datę wystawienia dokumentu handlowego w polskim formacie:

select format(dateadd(day, trn_data2, '18001228'), 'dd.MM.yyyy')
from cdn.tranag

CDN.NumerDokumentu

Opis
Funkcja zwraca wewnętrzny numer dokumentu, przy czym obsługuje różne typy dokumentów w XL-u. Są to m.in: dokumenty handlowe, magazynowe, importowe, zamówienia i oferty, bilans otwarcia, noty memoriałowe, wyciągi bankowe i inne.

Składnia
CDN.NumerDokumentu(GIDTyp, SpiTyp, TrNTyp, TrNNumer, TrNRok, TrNSeria, TrNMiesiac), gdzie parametry są zależne od obiektu, do kttórego się odnoszą.

Przykładowe zastosowanie
Zapytanie zwraca numer dokumentu handlowego:

select CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac) 
from CDN.TraNag

Zapytanie zwraca numer dokumentu magazynowego:

select CDN.NumerDokumentu(Man_GIDTyp, 0, MaN_TrNTyp, MaN_TrNNumer, MaN_TrNRok, MaN_TrNSeria, MaN_TrNMiesiac) 
from CDN.MagNag

Zapytanie zwraca numer Zamówienia lub Oferty (bierze w nim również udział funkcja CDN.DokMapTypDokumentu generująca identyfikator obiektu dla tabeli CDN.ZamNag):

select CDN.NumerDokumentu(CDN.DokMapTypDokumentu(ZaN_GIDTyp,ZaN_ZamTyp,ZaN_Rodzaj),0,0,ZaN_ZamNumer,ZaN_ZamRok,ZaN_ZamSeria,ZaN_ZamMiesiac) 
from cdn.ZamNag

Zapytanie zwraca numer zapisu w rejestrze bankowym:

select CDN.NumerDokumentu(KAZ_GIDTyp, 0, 0, KRP_Numer, KAZ_Rok, KAZ_Seria, KAZ_KRPLp) 
from CDN.Zapisy
join CDN.Raporty ON KAZ_KrpNumer = KRP_GIDNumer

CDN.NumerDekretu

Opis
Funkcja zwraca numer zapisu dekretu cząstkowego lub numer w dzienniku.

Składnia od wersji 2022.1
cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, DZK_Prosty, DZK_OkrSymbol), gdzie za parametry podstawiane są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety.
 

W wersji 2022.1 funkcja została rozszerzona o pole [DZK_OkrSymbol], co stanowi symbol okresu obrachunkowego, który jest yjmowany w numerze dekretu.

Składnia wcześniejsza do wersji 2022.0
cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, DZK_Prosty), gdzie za parametry podstawiane są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety.
 

Przykładowe zastosowanie
Zapytanie zwraca  numer księgowego dekretu cząstkowego (wraz z numerem pozycji dekretu przekazywanego w polu dt_gidlp):

select cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, dt_gidlp, DZK_Prosty, DZK_OkrSymbol)
from cdn.dziennik join cdn.dekrety on dt_gidnumer=dzk_gidnumer

Zapytanie zwraca numer w dzienniku  księgowym (z pominięciem numeru cząstkowego):

select cdn.numerDekretu(dzk_bufor, dzk_dziennik, dzk_rok, dzk_miesiac, dzk_rmNumer, 0, 1,DZK_OkrSymbol)
from cdn.dziennik

CDN.AtrPobierzWartosc

Opis
Bardzo przydatna funkcja zwracająca wartość wskazanego rodzaju atrybutu na wskazanym obiekcie. Jest niezastąpiona w sytuacji wykorzystania atrybutów na wydruku Crystal Reports i znacząco upraszająca tworzenie zapytań SQL.

Składnia
CDN.AtrPobierzWartosc(GIDTyp, GIDNumer, GIDLp, SubGIDLp, AtkID, TimeStamp), gdzie za parametry mają następujące znaczenie:

  • GIDTyp - GIDTyp obiektu, na którym znajduje się atrybut
  • GIDNumer - GIDNumer obiektu, na którym znajduje się atrybut
  • GIDLp - GIDLp obiektu, na którym znajduje się atrybut. Wykorzystywany głównie w sytuacji umieszczenia atrybutu na pozycji dokumentu. Jesli obiekt nie posiada pozycji to należy wpisać 0
  • SubGIDLp - subpozycja obiektu, na którym znajduje się atrybut. Jeśli Wykorzystywany głównie na dostawcach pozycji dokumentu (tabela CDN.TraSElem). Jeśli obiekt nie posiada subpozycji to należy wpisać 0
  • AtkID - identyfikator klasy atrybuty. Informacja o identyfikatorze atrybutu przechowywana jest w tabeli klas atrybutów (CDN.AtrybutyKlasy) w polu Atr_AtkID
  • TimeStamp - data w formacie TimeStamp, jeśłi atrybut przechowuje wartość historyczną. Wskazanie daty zwraca wartośc atrybutu na wskazany dzień. Jeśli atrybut nie przechowuje wartości hisorycznych to należy wpisać 0

    Przykładowe zastosowanie
    Zapytanie zwraca  wartośc atrybutu określonego identyfikatorem 4  (i nie będącego atrybutem przechowującym wartość historyczną) na liście dokumentów handlowych:

    select cdn.AtrPobierzWartosc(trn_gidtyp, trn_gidnumer, 0, 0, 4, 0)
    from cdn.traNag
    

    OBJECT_NAME

    Opis
    Wbudowana w MS SQL funkcja sięgająca do tzw. metadanych. Zwraca nazwę obiektu bazy danych o wskazanym identyfikatorze. Może to być nazwa tabeli, funkcji, procedury, itd. Jest zwłaszcza pomocna przy identyfikowaniu nazwy tabeli dla wskazanej kolumny.

    Składnia
    object_name(ID), gdzie  ID określa identyfikator obiektu w bazie danych.

    Przykładowe zastosowanie
    Zapytanie zwraca  nazwę tabeli dla kolumny 'Knt_GIDNumer':

    select object_name(ID) from syscolumns where name='Knt_GIDNumer'

    CDN.NazwaObiektu

    Opis
    Funkcja zwraca wewnętrzny numer dokumentu (podobnie jak CDN.NumerDokumentu) przy czym ze względu na uproszczone parametry eliminuje konieczość odnoszenia się w zapytaniu do tabeli zawierającej porządane informacje. Warto korzystać z niej w sytuacji, gdy w zapytaniu chcemy odwołać się do pewnej tabeli bez łączenia z tą tabelą, na przykład do numeru dokumentu pierwotnego na korekcie, numeru spinacza na WZ, numeru zamówienia na fakturze, nazwy kontrahenta, itp.
    Dodatkowo obsługuje  dane słownikowe, w tym: kartoteki towarów, kontrahentów, środków trwałych i pracowników. 

    Składnia
    CDN.NazwaObiektu(GIDTyp,GIDNumer,GIDLp,Format,) gdzie parametry są zależne od obiektu, do którego się odnoszą, a format jest typem bitowym przyjmującym wartość od 0 do 7, gdzie poszczególne bity mają następujące znaczenie:

    • 1 - rodzaj obiektu, np. "Przyjęcie zewnętrzne"
    • 2 - numer obiektu lub akronim w przypadku obiektów słownikowych, np. "PZ-267/08"
    • 4- nazwa obiektu w przypadku obiektów słownikowych, np. nazwisko pracownika

    Przykładowe zastosowanie
    Zapytanie do jednej tabeli CDN.TraNag zwraca informacje przechowywane w innych tabelach, w tym ZamNag, OpeKarty, KntKarty i skorelowanej tabeli (na potrzeby korekty) TraNag:

    select
    [Nr dokumentu]=CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac),
    [Nr korekty]=CDN.NazwaObiektu(TrN_ZwrTyp,TrN_ZwrNumer,0,2),
    [Nr Zamówienia]=CDN.NazwaObiektu(TrN_ZanTyp,TrN_ZanNumer,0,2),
    [Kontrahent]=CDN.NazwaObiektu(TrN_KntTyp,TrN_KntNumer,0,4),
    [Operator wystawiający]=CDN.NazwaObiektu(TrN_OpeTypW,TrN_OpeNumerW,0,4)
    FROM CDN.TraNag WHERE TrN_GIDTyp=2033 and TrN_ZwrTyp=2041

    Artykuł uzupełniony o tę funkcję dzięki sugestii pana Michała z CDN Partner.

    CDN.DateToClarion

    Opis
    W wersji 2020.2 ERP XL pojawiła się nowa przydatna funkcja pozwalająca na łatwą konwersję daty sformatowanej do postaci Clarion. Jest ona uproszczonym wariantem funcji DATEDIFF.
    Funkcja konwertuje datę kalendarzową do postaci daty Clarion wyrażonej liczbą dni, jaka upłynęła od dnia '1800-12-28'.

    Składnia
    cdn.DateToClarion('<data kalendarzowa>'), gdzie data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.

    Przykładowe zastosowanie
    Zapytanie do tabeli TraNag zawęża listę dokumentów do określonego zakresu dat:

    select [Nr dokumentu]=CDN.NumerDokumentu(TrN_GIDTyp, TrN_SpiTyp, TrN_TrNTyp, TrN_TrNNumer, TrN_TrNRok, TrN_TrNSeria, TrN_TrNMiesiac)
    FROM CDN.TraNag 
    WHERE TrN_GIDTyp=2033 and TrN_Data2 between cdn.DateToClarion('2020-02-01') AND cdn.DateToClarion('2020-03-15')

     

    CDN.DateToTS

    Opis
    To druga z nowych funkcji dodanych w wersji 2020.2 , która umożliwa na łatwą konwersję daty sformatowanej do postaci TimeStamp. Jest ona uproszczonym wariantem funcji DATEDIFF.
    Funkcja konwertuje datę kalendarzową do postaci daty TimeStamo wyrażonej liczbą sekund, jaka upłynęła od dnia '1990-01-01'.

    Składnia
    cdn.DateToTS('<data kalendarzowa i opcjonalnie godzina>'), gdzie data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.

    Przykładowe zastosowanie
    Zapytanie do tabeli Sesji pokazuje listę operatorów, którzy w dniu 1 lipca 2020 uruchomili moduł programu po godzinie 9:00.

    select Operator=ses_opeIdent, Start=dateadd(s, ses_start, '19900101') from cdn.sesje
    where ses_start>cdn.DateToTS('2020-07-01 09:00:00')

    Komentarze

    Obrazek użytkownika MIchał Sekuła
    MIchał Sekuła 29.05.2018 - 12:05
    Moim zdaniem zamiast CDN.NumerDokumentu lepiej jest używać CDN.NazwaObiektu. Przykładowe wywołanie dla dokumentów handlowych : select cdn.NazwaObiektu(trn_gidtyp,trn_gidnumer,0,1) from cdn.TraNag dla ZS: select cdn.NazwaObiektu(zan_gidtyp, zan_gidnumer , 0,2) from cdn.zamnag. Jak widać składnia jest dużo prostsza
    Obrazek użytkownika redaktor
    redaktor 30.05.2018 - 12:05
    Bardzo dobry pomysł. Nie znałem tej funkcji ale chętnie zacznę używać. Dodam ją również do artykułu. pozdrawiam, Piotr Jesionek
    Obrazek użytkownika redaktor
    redaktor 30.12.2019 - 16:12
    Statystyki pokazują, że ten artykuł cieszy się dużą popularnością na stronie. Umieszczam zatem komentarz aby było łatwo do niego dotrzeć z pierwszej strony serwisu.
    Obrazek użytkownika admin
    admin 04.09.2020 - 11:09
    Informujemy że od wersji 2020.2 pojawiły się dwie nowe funkcje SQL: CDN.DateToClarion i CDN.DateToTS.
    Obrazek użytkownika andrzejp
    andrzejp 30.04.2021 - 9:04
    W przypadku funkcji CDN.DateToTS, to w poprzednich wersjach systemu istniała procedura wbudowana CDN.DateToTS. Comarch zastosował funkcje w celu użycia jej w zapytaniach.

    Sample2ść h3 tag

    Sample pararaph