VYTVÁ ENÍ VÝB ROVÝCH DOTAZ V PRODUKTECH YAMACO SOFTWARE
P ÍRU KA A NÁVODY PRO Ú ELY:
- VYTVÁ
ENÍ VÝB ROVÝCH SESTAV
© YAMACO SOFTWARE 2003-2004
1. ÚVODEM Standardní sou ástí všech produkt Yamaco Software jsou prost edky pro tvorbu sestav na zakázku. Jedná se o sestavy origináln navržené uživatelem a sloužící pro získání informací, které nelze v rámci dané evidence zjistit klasickými standardními sestavami. Pro vytvá ení p edloh sestav na zakázku se používá vestav ný grafický editor p edloh, jehož vlastnosti jsou popsány v dokumentu Za ínáme s p edlohami. Tento dokument se v nuje druhé rovin tvorby zakázkových sestav, a tou je vytvá ení výb rových dotaz . 2. CO JE VÝB ROVÝ DOTAZ Sestavy na zakázku lze na základ existující p edlohy sestav vytvá et dv ma zp soby – bu ze všech dat ( tento zp sob je používán v menší mí e) nebo s omezením jen na požadované informace – práv toto omezení se zadává formou výb rového dotazu a je v praxi velmi asté. Z technického hlediska je výb rový dotaz jistou strojovou interpretací požadovaného zadání pro výpo et – tato interpretace se provádí pomocí zápisu v jazyce SQL. Jedná se o univerzální a zavedený databázový prost edek, který se s v tšími i menšími obm nami používá v rámci všech známých databázových prost edí. Psaní SQL dotaz p ímo je pro b žného uživatele relativn složité, proto všechny produkty YAMACO Software disponují grafickými prost edky a pom ckami, sloužícími k vizuálnímu sestavení výb rové konstrukce. Jelikož se v portfoliu uživatel vyskytují i klienti, kte í znají pravidla pro používání SQL jazyka a umí psát vlastní výb rové dotazy, pracuje vytvá ení dotaz v našich produktech dvoucestn a umož uje i tento zp sob – tedy p ímé napsání pot ebné ásti dotazu, což zna n urychluje práci. Definici jazyka a popis všech SQL p íkaz lze nalézt v ad odborných titul . Podrobný popis jednotlivých p íkaz je k dispozici i v dokumentaci k databázovému serveru FireBird, ovšem samoz ejm v angli tin . Z hlediska formátu se každý SQL dotaz, použitý k výb ru n jakých informací v rámci projektu, skládá z jedné nebo více podmínek, p i emž každá podmínka obsahuje práv t i ásti: o pole tabulky, v n mž se bude vyhledávat o podmínku výb ru, která bude použita p i porovnání o vlastní hledaný výraz Tedy nap . v p ípad požadavku na zadání výb rové konstrukce, která by do sestavy zahrnula všechny p estupky, spáchané 1.7.2003, bude: o datum spáchání polem tabulky, v n mž se bude vyhledávat
o „rovná se“ podmínkou výb ru, která bude použita p i porovnání o datum 1.7.2003 vlastním hledaným výrazem Takovýchto konstrukcí m že výb rový dotaz obsahovat neomezené množství – p itom je jasné, že jednotlivé díl í konstrukce lze mezi sebou vyhodnocovat dv ma zp soby: o platí jedna a sou asn další podmínka o platí jedna nebo druhá podmínka Rozdíl ve zpracování je jasný – pokud nap . budeme hledat všechny p estupky, spáchané 3.6.2004 p estupcem Novákem, bude nutno zadat výb rový dotaz tak, aby platily ob podmínky sou asn ( najít všechny p estupky, kde je datum spáchání 3.6.2004 A SOU ASN kde p estupcem je Novák). Naproti tomu budeme-li hledat p estupky, které spáchal p estupce Dvo ák nebo p estupce Dolejš, výb rový dotaz musí být formulován tak, že platí jedna nebo druhá podmínka ( najít všechny p estupky, kde p estupcem je Dvo ák NEBO kde p estupcem je Dolejš) Zp sob vyhodnocení jednotlivých podmínek ve smyslu výše uvedeného odstavce lze zadat pomocí dvou anglických sloví ek, tzv. logických operátor . Mají-li platit podmínky sou asn , je nutno spojit je slovem AND ( a sou asn ), má-li platit jedna nebo druhá pak sloví kem OR ( nebo). Oba operátory lze zadat „graficky“, jak bude uvedeno dále, nebo p ímo napsat. Volitelnou ástí výb rového dotazu je specifikace polí, podle nichž bude výsledná sestava set íd na. I tato ást dotazu m že být bu „poskládána“ v dialogu tvorby dotazu, nebo zapsána p ímo do zn ní vlastního dotazu. P íklady budou uvedeny v dalším textu. Obecn platí, že sestavy lze set ídit podle libovolného po tu kritérií, která jsou v dané agend k dispozici. Lze ur it i sm r t íd ní, a to bu vzestupn nebo sestupn , vždy s respektováním eské normy t íd ní ( písmeno CH, diakritické znaky apod.). Výsledný dotaz m že, jak již bylo uvedeno, obsahovat neomezené množství díl ích výb rových konstrukcí a m že být prakticky neomezen dlouhý.
3. V EM SESTAVIT VÝB ROVÝ DOTAZ Pokud je daná agenda vybavena prost edky pro tvorbu sestav na zakázku, používá se jednotný p ístup – funkce Sestavy na zakázku-Vytvá ení výb rových dotaz , dostupná též v panelu tla ítek pod tímto symbolem: Po zvolení dané funkce se otev e následující dialog:
Tato ást dialogového okna obsahuje všechny prost edky, které pot ebujeme k sestavení díl ích podmínek i celého dotazu: o jména polí tabulky - seznam všech polí ( údaj ) dané evidence, která jsou pro vyhledávání k dispozici o podmínka výb ru – seznam matematických a jiných operátor , které mohou být použity p i porovnávání hledaného údaje o výb rové slovo – vlastní hledaný výraz Tyto t i ovládací prvky korespondují s výše uvedenými t emi ástmi každé podmínky a jsou pro každou díl í konstrukci povinné. Další ovládací prvky v dialogu jsou: o tla ítko P idat podmínku do dotazu – p idá jednotliv sestavenou díl í konstrukci do výsledného zn ní dotazu o další volby – umožní jednak uzav ít celou díl í konstrukci do závorek, jednak p idat p ed nebo za díl í konstrukci ( výraz) operátor AND nebo OR o kritérium t íd ní – umož uje na záv r tvorby výb rového dotazu p idat požadovaná t ídící kritéria a specifikovat sm r t íd ní o pole Dosud vložený dotaz – zde se zobrazuje zn ní vytvá eného dotazu a v p ípad pot eby lze dotaz upravovat p ímo v tomto poli o tla ítko Smazat dotaz – umož uje vymazat dosud vytvo ený dotaz a za ít sestavovat dotaz nový o tla ítko Zkontrolovat vložený dotaz – umož uje v kterémkoliv okamžiku zkontrolovat vytvá ený dotaz. Pozor: kontrola je syntaktická,
tzn. že odhalí správnost zápisu dotazu, nikoliv ovšem jeho logickou správnost – tu musí vždy posoudit uživatel. 4. VÝZNAM JEDNOTLIVÝCH PODMÍNEK VÝB RU Jednotlivé podmínky výb ru, zobrazené v poli Podmínka výb ru, lze rozd lit do t í skupin: o matematické – rovná se, v tší než, menší než, nerovná se, v tší nebo rovno, menší nebo rovno – tyto podmínky lze použít na porovnání ísel, text i položek typu Datum o textové – za ( za íná hledaným slovem), kon ( kon í hledaným slovem), obs ( obsahuje hledané slovo) – používají se pouze k hledání text o speciální – null ( položka nemá žádnou hodnotu), x null ( položka má n jakou hodnotu) – lze je použít pro všechny typy údaj 5. PRAKTICKÉ SESTAVENÍ DOTAZU Uve me si na malém p íkladu sestavení této výb rové konstrukce: do sestavy bychom pot ebovali všechny p estupce, kde p estupce je Josef a sou asn p estupek byl spáchán v obci Horní Dolní. Vše set ídíme podle data spáchání p estupku vzestupn . Nejprve si promyslíme zn ní dotazu ( tedy to, co od programu chceme): vybrat všechny p estupce, kde položka Jméno obsahuje text Josef a sou asn místo spáchání p estupku je Horní Dolní – vše set íd no podle údaje Datum spáchání p estupku vzestupn . Postup – jednotlivé kroky: 1. v seznamu polí klepneme na pole JMENO, ozna íme podmínku výb ru OBS a jako hledaný výraz vložíme text „josef“ ( není nutno rozlišovat velká a malá písmena) 2. klepneme na tla ítko P idat podmínku do dotazu 3. zatrhneme polí ko P ed výraz p idat operátor a ze seznamu vybereme AND 4. v seznamu polí klepneme na položku MISTO, ozna íme podmínku výb ru ROVNÁ SE a jako hledaný výraz vložíme text „horní dolní“ 5. klepneme na tla ítko P idat podmínku do dotazu 6. v dolním seznamu t ídících kritérií klepneme na položku D_SPACHANI a ponecháme ozna ený p epína VZESTUPN 7. klepneme na tla ítko P idat jako t ídící kritéria Celé zn ní dotazu je možné si prohlédnout v polí ku Dosud vložený dotaz. Zde je možno také provád t p ímé korekce dotazu ( dopl ování a úpravy) a též pomocí tla ítka Zkontrolovat vložený dotaz ov it správnost jeho zadání.
6. JAK VYPADÁ HOTOVÁ VÝB ROVÁ KONSTRUKCE Sestavený dotaz je zobrazen v poli Dosud vložený dotaz, a to p ímo tak, jak je p eveden do jazyka SQL. Výše uvedený p íklad bude vypadat v jazyce SQL takto: ( UPPER(JMENO) LIKE "%JOSEF%") AND ( UPPER(MISTO) ="HORNÍ DOLNÍ") ORDER BY D_SPACHANI ASC Je nutno si povšimnout zejména té skute nosti, že veškeré hledané texty v etn položek typu Datum jsou ohrani eny uvozovkami. Toto pravidlo je d ležité zejména v p ípad , že ru n upravujete vygenerovaný dotaz. Pokud je hledaným výrazem íslo, zadává se do dotazu vždy bez uvozovek, tedy nap . podmínka „ vybrat všechny dopravce, kte í mají po et vozidel v tší než 12“, by byl sestaven takto: VOZIDLA>12 7. OPAKOVANÉ POUŽITÍ VYTVO ENÉHO DOTAZU Unikátní vlastností všech produkt , využívajících sestavy na zakázku, je možnost uložení vytvo eného výb rového dotazu pro opakované použití. Ú el této vlastnosti je z ejmý – usnadnit práci p i vytvá ení sestav s asovou periodicitou, kdy se d íve vytvo ený dotaz použije zcela beze zm n nebo s dalšími úpravami. Vytvo ené dotazy lze ukládat ve druhé ásti dialogu výb rových dotaz :
Dialogové okno op t obsahuje všechny pot ebné prost edky:
o pole pro pojmenování dotazu – slouží k vhodnému ozna ení obsahu dotazu – nap . Lovecké lístky, vydané v prvním pololetí 2004 o tla ítko Uložit dotaz – slouží k uložení aktuáln vytvo eného a pojmenovaného dotazu pro pozd jší použití o m ížka pro na tení existujícího dotazu - zde se zobrazuje seznam uložených dotaz a v poli Zn ní dotazu i jejich obsah. Ozna ením lze vybrat dotaz k op tovnému na tení o tla ítko Na íst dotaz – slouží k na tení vybraného dotazu, poté se program p epne na první záložku Vytvo ení dotazu a pole Dosud vložený dotaz bude obsahovat požadovanou konstrukci Po et uložených dotaz op t není omezen, a protože se dotazy ukládají p ímo do databáze daného projektu, mohou s nimi pracovat všichni uživatelé, pokud mají k daným funkcím p ístup.
8. DALŠÍ POZNÁMKY KE TVORB VÝB ROVÝCH KONSTRUKCÍ Ru ní úpravy dotaz V ad p ípad se používá úprava zn ní výb rové konstrukce p ímo v poli Dosud vložený dotaz. Je d ležité si uv domit, že jazyk SQL nerozlišuje pro psaní jednotlivých p íkaz malá a velká písmena. Takže nap . „AND „ a „and“ má zcela totožný význam. Taktéž platí, že výsledná výb rová konstrukce m že být rozložena na libovolném po tu ádk a pro zp ehledn ní lze hojn využívat mezery. Uvozování skupin podmínek do závorek V n kterých p ípadech m že být dv nebo více podmínek pot eba vyhodnotit jako jednu podmínku. V takovém p ípad je nutno tuto skupinu uzav ít do separátních závorek pro zajišt ní správného vyhodnocení. M jme výše uvedený p íklad s tím rozdílem, že bychom cht li p estupky, spáchané nejen v Horní Dolní, ale ješt i v erné Lhot . Požadavek tedy bude takový, že: o první podmínkou je, že p estupce obsahuje „Josef“ o druhou ( složenou) podmínkou je to, že místo spáchání je Horní Dolní nebo erná Lhota Jak bude dotaz vypadat ? ( UPPER(JMENO) LIKE "%JOSEF%") AND (UPPER(MISTO) ="HORNÍ DOLNÍ")OR(UPPER(MISTO) =" ERNÁ LHOTA") ORDER BY D_SPACHANI ASC
Pokud si tento dotaz prohlédneme, zjistíme, že obsahuje vše, co pot ebujeme, ale p esto bude špatn vyhodnocen. Pro správnou funkci je nutno poslední dv
podmínky uzav ít do závorek tak, aby se vyhodnocovaly na stejné úrovni a byly chápány jako jedna podmínka: ( UPPER(JMENO) LIKE "%JOSEF%") AND ((UPPER(MISTO) ="HORNÍ DOLNÍ")OR(UPPER(MISTO) =" ERNÁ LHOTA")) ORDER BY D_SPACHANI ASC
Nyní je dotaz v po ádku i logicky a bude poskytovat požadované výsledky. Používání zástupných znak v hledaném textu V praxi nastávají i speciální požadavky, které se nedají zohlednit grafickými prost edky tvorby dotazu, ale dají se aplikovat p ímou editací dotazu. Jednou z takových možností je použití tzv. zástupných znak v dotazu. M jme požadavek ( zdánliv nesmyslný) na vytvo ení seznamu držitel loveckého lístku, v n mž chceme pouze ty držitele, kte í mají na t etí pozici údaje Jméno a p íjmení písmeno R. Tento požadavek lze zohlednit pouze p ímou úpravou SQL dotazu s použitím zástupných znak . Byli bychom schopni vytvo it dotaz „vybrat všechny držitele, jejichž jméno obsahuje R“: UPPER(JMENO) LIKE "%R%"
Ovšem toto ešení nám nedovoluje pracovat s dalším požadavkem, kterým je striktní pozice znaku R na t etím míst od za átku. Sta í jednoduchá úprava: UPPER(JMENO) LIKE "__R%"
a dotaz funguje. Úprava spo ívá v použití znaku podtržítko ( _) jako „žolíka“, tzn. že jej použijeme tolikrát, od které pozice v daném údaji chceme vyhledávat. Analogicky vyhledání všech držitel , majících ve jménu znak R na t etí a znak A na šesté pozici, by vypadalo takto: UPPER(JMENO) LIKE "__R__A%"
Samotným zástupným znakem je i uvedený znak procento (%), které se ovšem liší od podtržítka v tom, že nahrazuje libovolný po et znak na své pozici. To znamená ( nap . v posledn uvedené konstrukci), že hledáme jméno, které má t etí znak R a šestý A a kon í libovolným po tem libovolných znak . Použití operátoru NOT Tento operátor umož uje negovat n která klí ová slova. Nap . konstrukce UPPER(JMENO) LIKE "%PAVEL%"
vyhledá všechny p estupce, obsahující ve jménu text „Pavel“, kdežto pokud použijeme ješt operátor NOT:
UPPER(JMENO) NOT LIKE "%PAVEL%"
potom tato konstrukce vyhledá všechny p estupce krom t ch, jejichž jméno obsahuje „Pavel“. Vytvá ení tabulkových sestav dle skupin Tabulkové sestavy na zakázku jsou speciálním p ípadem uživatelských sestav a jsou velmi efektivní. Typickým p íkladem jejich použití je nap . sestava p estupk dle zp sobu ešení p estupku. Výsledkem takové sestavy je tabulka jednotlivých variant ešení s uvedením po tu odpovídajících p estupk . Krom toho, že pro vytvo ení takové sestavy musíme mít speciáln upravenou p edlohu, je nutno výb rovou podmínku pro takovou sestavu vždy doplnit kritériem t íd ní práv podle té položky, která bude použita jako skupinová, tzn. v tomto p ípad se jedná o položku Zp sob ešení. T íd né sestavy s tzv. falešnou podmínkou V ad p ípad požaduje praxe sestavu bez výb ru, tj. ze všech dat dané evidence, ale set íd nou podle požadované kritéria. V tomto p ípad je nutno provést malou lest a zadat tzv. falešnou podmínku. M jme požadavek na vytvo ení celkového p ehledu všech dopravc v taxislužb , set íd ný podle data vydání stanoviska. Víme, že tvorba dotaz je koncipována tak, že musíme zadat alespo jednu výb rovou konstrukci. Protože vybírat nechceme, zadáme takovou podmínku, která za každých okolností vrátí všechny záznamy tabulky. K tomu je vhodné použít vlastnosti povinných údaj záznamu. Pokud víme, že nap . údaj I O v taxislužb musí být vždy vypln n, zadáme podmínku „ vybrat všechny subjekty, kde I O je neprázdné“, a máme jistotu, že tato konstrukce vrátí vždy všechny subjekty z dané evidence: ICO IS NOT NULL ORDER BY D_VYDSTAN
Poslední revize: 9.6.2004
