
Najczęściej używane funkcje SQL w Comarch ERP XL
Tworząc dodatkowe funkcjonalności do Comarch ERP XL odnoszące się do bazy danych często potrzeba skorzystać z funkcji zwracającej pewną wartość. Funkcje SQL w Comarch ERP XL można nazwać ‘podprogramami’ które wyliczają pewne dane na podstawie podanych kryteriów i zwracają przeliczone wyniki.
Funkcje SQL w Comarch ERP XL – Dlaczego je używamy?
Podstawą działania systemu Comarch ERP XL są Funkcje SQL. Używa się ich z kilku powodów:
- Ułatwienie i przyśpieszenie procesu kodowania – zamiast ciągle tworzyć rozbudowany kod można skorzystać z przygotowanej wcześniej funkcji. Przykładem jest NumerDokumentu zwracający numer dokumentu handlowego.
- Poprawienie i uproszczenie czytelności kodu – zamiast pisać zapytanie odwołujące się do wielu tabel można stworzyć jedno zapytanie. Zapytanie to będzie odwoływać się do jednej tabeli, a dane z tabel ‘nieobecnych’ zostaną przeliczone przez funkcję. Przykładem takowej funkcji jest AtrPobierzWartosc zwracającej wartości wskazanego atrybutu, który przechowywany jest w zewnętrznej tabeli.
- Unifikacja i ujednolicenie kodu – jeżeli pewnego rodzaju zapytania mają być wykorzystywane w wielu miejscach to miejsca te powinny odnosić się do funkcji, którą w razie potrzeby łatwo można zmodyfikować. Przykładem takiej funkcji będzie Slownie zwracać będzie liczbę w postaci słownej.
- Zapobieganie błędom powtórzeń rekordów w wynikach – funkcje zawsze zwracają pojedynczą wartość skalarną (wyjątkiem są funkcje zwracające recordset). W przypadku łączenia tabel istnieje ryzyko powielenia rekordów. Przykładem funkcji może być CDNAtrPobierzWartosc.
- Umożliwiają sięgnięcie do pewnych danych w kontekście, w którym może nie być to możliwe lub trudne. Tego typu funkcje zwykle tworzy się samodzielnie, na przykład w konieczności sięgnięcia do pewnych danych na elemencie schematu księgowania.
Funkcje SQL w Comarch ERP XL wykorzystuje się najczęściej w:
- Wydrukach,
- Filtrach,
- Raportach SQL oraz typu wykres,
- Schematach księgowań.
Najczęściej używane funkcje SQL w Comarch ERP XL
DATEADD
Opis
Funkcja zamienia postać liczbową daty na postać czytelną. Daty w bazie danych Comarch ERP XL przechowywane są w trzech różnych postaciach. Dwie spośród trzech postaci to postacie liczbowe: format Clarion oraz format TimeStamp.
Format Clarion określa liczbę dni jaka upłynęła od dnia 28.12.1800. Natomiast format TimeStamp określa liczbę sekund jaka upłynęła od dnia 01.01.1990. Poprzez format TimeStamp możemy wykazać dokładną godzinę utworzenia dokumentu.
Składnia
DATEAD (parametr1, parametr2, parametr3)
- parametr1 – w zależności od formatu daty – Clarion: day, TimeStamp: s,
- parametr2 – liczba określająca datę,
- paramet3 – 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ę oraz godzinę zalogowania do programu:
select dateadd(s, ses_start, '19900101'),SES_OpeIdent from cdn.sesje
DATEDIFF
Opis
Funkcja zmienia postać czytelną daty w postać liczbową. Funkcja DATEDIFF jest przeciwieństwem wyżej opisanej funkcji DATEADD. Wykorzystuje się ją najczęściej do konwersji daty na format Clarion, rzadziej natomiast na format TimeStamp.
Składnia
DATEDIFF (parametr1, parametr2, parametr3)
- parametr1 – w zależności od formatu daty – Clarion: day, TimeStamp: s,
- parametr2 – w zależności od formatu daty – Clarion: ‘18001228’, TimeStamp: ‘19900101’,
- parametr3 – data w postaci czytelnej, w formacie ‘YYYYMMDD’.
Przykładowe zastosowanie
Zapytanie zwraca listę dokumentów handlowych wystawionych w okresie pomiędzy 1 stycznia 2023 a 30 stycznia 2023:
select * from cdn.tranag
where trn_data2 between datediff(day, '18001228', '20230101') and datediff(day, '18001228', '20230130')
Zapytanie zwraca listę sesji, które rozpoczęły się w dniu 20 lutego 2023 w godzinach pomiędzy 10 a 12:
select * from cdn.sesje
where ses_start between datediff(s, '19900101', '20230220 10:00') and datediff(s, '19900101', '20230220 12:00')
CDN.YMD
Opis
Funkcja dostarczona przez Comarch zmienia 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 Comarch ERP XL. Format daty jest wykorzystywany głównie na rejestrach VAT oraz na dziennikach księgowych.
Składnia
CDN.YMD (Rok, Miesiąc, Dzień)
Przykładowe zastosowanie
Zapytanie wyświetla datę dekretu księgowego:
select CDN.YMD(dzk_rok, dzk_miesiac, dzk_dzien) from cdn.dziennik
FORMAT
Opis
Funkcja formatująca datę na pożądany format. Dla standardowego ustawienie bazy MS SQL funkcja DATEADD zwróci datę w formacie [Rok-Miesiąc-Dzień Godzina]. W przypadku odwołania do daty w formacie Clarion godzina zawsze jest pusta. Funkcja pozwala na zamianę sposobu wyświetlania daty, w szczególności na usunięcia niepożądanej daty.
Składnia
FORMAT(data, format)
- data – bazowa wartość daty,
- format – sposób formatowania daty (np. ‘DD.MM.YYYY’, ‘YYYY.MM.DD’).
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.NumerDokumnetu(GIDTyp, SpiTyp, TrNTyp, TrNNumer, TrNRok, TrNSeria, TrNMiesiac)
Parametry są zależne od obiektu, do któ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 dokumentowego 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 (zawarta jest w nim również 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)
Za parametry podstawione są odpowiednie pola z tabeli CDN.Dziennik lub CDN.Dekrety.
W wersji 2022.1 funkcja została rozszerzona o pole [DZK_OkrSymbol], cl stanowi symbol okresu obrachunkowego, który jest ujmowany 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)
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
Funkcja zwraca wartość wskazanego rodzaju atrybutu na wskazanym obiekcie. Funkcja jest niezastąpiona w pracy z Crystal Reports znacząco upraszczając tworzenie zapytań SQL.
Składnia
CDN.AtrPobierzWartosc( GIDTyp, GIDNumer, GIDLp, SubGIDLp, AtkID, TimeStamp)
Parametry prezentują się następująco:
- 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. Parametr wykorzystywany głównie w sytuacji umieszczenia atrybutu na pozycji dokumentu. Jeśli obiekt nie posiada pozycji to należy wpisać 0,
- SubGIDLp – subpozycja obiektu, na którym znajduje się atrybut. Jest wykorzystywany głównie na dostawcach pozycji dokumentu (tabela CDN.TraSEIem). Jeśli obiekt nie posiada pozycji to należy wpisać 0,
- AtkID – identyfikator klasy atrybutu. Informacja o identyfikatorze atrybutu przechowywana jest w tabeli klas atrybutów (CDN.AtrybutyKlasy) w polu Atr_AtkID,
- TimeStamp – data w formacie TimeStamp, jeśli atrybut przechowuje wartość historyczną. Wskazanie daty zwraca wartość atrybutu na wskazany dzień. Jeśli obiekt nie posiada pozycji to należy wpisać 0.
Przykładowe zastosowanie
Zapytanie zwraca wartość 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 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 konieczność odnoszenia się w zapytaniu do tabeli zawierającej pożądane informacje. Warto skorzystać z niej w sytuacji, gdy chcemy odwołać się do pewnej tabeli bez łączenia z tą tabelą. 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)
Parametry są zależne od obiektu, do którego się odnoszą, a format jest typem bitowym przyjmującym wartość od 0 do 7. Poszczególne bity maja 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-232/03”,
- 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
CDN.DateToClarion
Opis
W Comarch ERP XL 2020.2 pojawiła się nowa przydatna funkcje SQL pozwalająca na łatwą konwersję daty sformatowanej do postaci Clarion. Jest ona uproszczonym wariantem funkcji DATEDIFF. Funkcja konwertuje datę kalendarzową do postaci daty Clarion wyrażonej liczby dni, jaka upłynęła od dnia ‘1800-12-28’.
Składnia
Cdn.DateToClarion(‘<data kalendarzowa>’)
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 kolejna z nowych funkcje SQL w Comarch ERP XL 2020.2, która umożliwia na łatwą konwersję daty sformatowanej do postaci TimeStamp. Jest ona uproszczonym wariantem funkcji DATEDIFF. Funkcja konwertuje datę kalendarzową do postaci daty TimeStamp wyrażonej liczbą sekund, jaka upłynęła od dnia ‘1990-01-01’.
Składnia
Cdn.DateToTS(‘<data kalendarzowa i opcjonalnie godzina>’)
Data kalendarzowa jest parametrem tekstowym zawartym w apostrofach.
Przykładowe zastosowanie
Zapytanie do tabeli Sesji pokazuje listę operatorów, którzy w dniu 10 sierpnia 2023 uruchomili moduł programu o 12:00.
select Operator=ses_opeIdent, Start=dateadd(s, ses_start, '19900101')
from cdn.sesje
where ses_start>cdn.DateToTS('2023-08-10 12:00:00')
Zapraszamy na darmową prezentację Comarch ERP XL. Skontaktuj się z nami!
Telefonicznie 797 604 592 lub mailowo.
Jeżeli są Państwo zainteresowani innymi ułatwieniami w prowadzeniu firmy.
Zapraszamy do naszego sklepu!