Posted By: Jovo () on 'CZdatabases' Title: Jovo 4 Date: Thu Nov 9 19:35:33 2000 Z Jovova zapisniku - 4:"Pouziti isnull jako IF" =============================================== Mame nasledujici situaci: Vyrabime a prodavame. Nektere vyrobky vyrabime ve vlastni dilne, nektere nakoupime jinde a prodavame. TABLE vyrobky TABLE ext_ceny id nazev cena id cena ================================= ===================== (serial) (char10) (decimal(11,2)) (int) (decimal(11,2)) --------------------------------- --------------------- 1 LSD 15 3 3,14 2 MDMA 30,12 4 10 3 trava 7 4 Extaze 5 Rezna 1,5 6 cigarety 7 pernik Takze LSD, Reznou a MDMA vyrabime sami, travu a Extazi "importujeme" :-) Tise opomeneme fakt, ze v ext_ceny budeme mit i uvedeny ceny "vyrobku" u "konkurence". (X1) Chceme napriklad udelat takovy select, ktery vybere vsechny nami prodavane vyrobky i s cenami. Chceme dostat : id nazev cena externi --------------------------------------------------------- 1 LSD 15 <-vlastni 2 MDMA 30,12 3 trava 3,14 "A" <-cizi 4 Extaze 10 "A" 5 Rezna 1,5 6 cigarety ? <-vyrabime ale nevime za kolik 7 pernik ? "A" <-konkurence nedodala cenu 1] Udelame nekolik SELECTu (Omlouvam se puristum. Ano, jde to prepsat tak do dvou selectu, ale toto je "skolni" priklad :-) a] SELECTy pod sebou SELECT id,nazev,cena,"" FROM vyrobky WHERE cena IS NOT NULL; -> vybere zaznamy c. 1,2,5 SELECT vyrobky.id,vyrobky.nazev,ext_ceny.cena,"A" FROM vyrobky,ext_ceny WHERE vyrobky.id = ext_ceny.id AND vyrobky.cena IS NULL #toto je kdyby nebyla splnena (X1) AND ext_ceny.cena IS NOT NULL -> vybere zaznamy c. 3,4 SELECT id,nazev,"?","" FROM vyrobky WHERE NOT EXISTS ( SELECT * FROM ext_ceny WHERE ext_ceny.id = vyrobky.id ) AND vyrobky.cena IS NULL; -> vybere zaznam c. 6 SELECT vyrobky.id,vyrobky.nazev,"?","A" FROM vyrobky,ext_ceny WHERE vyrobky.id = ext_ceny.id AND ext_ceny.cena IS NULL; -> vybere zaznam c. 7 b] jen jeden SELECT, ale slozeny UNIONem z predchozich ctyr SELECT id,nazev,cena,"" FROM vyrobky WHERE cena IS NOT NULL UNION SELECT vyrobky.id,vyrobky.nazev,ext_ceny.cena,"A" FROM vyrobky,ext_ceny WHERE vyrobky.id = ext_ceny.id AND vyrobky.cena IS NULL #toto je kdyby nebyla splnena (X1) AND ext_ceny.cena IS NOT NULL UNION SELECT id,nazev,"?","" FROM vyrobky WHERE NOT EXISTS ( SELECT * FROM ext_ceny WHERE ext_ceny.id = vyrobky.id ) AND vyrobky.cena IS NULL UNION SELECT vyrobky.id,vyrobky.nazev,"?","A" FROM vyrobky,ext_ceny WHERE vyrobky.id = ext_ceny.id AND ext_ceny.cena IS NULL - pozn: pokud vam bude SQL rvat, ze "Corresponding column types must be compatible for each UNION statement" - to je zpusobeno tim, ze prvni a druhy SELECT vybiraji cenu (decimal) a treti a ctvrty vybiraji retezec ("?"). Reseni zavisi na implementaci SQL. Muzete v prvnim dotazu zkusit nahradit v SELECT id,nazev,cena -> SELECT id,nazev,isnull(cena,"Z") a v druhem SELECT vyrobky.id,vyrobky.nazev,ext_ceny.cena -> SELECT vyrobky.id,vyrobky.nazev,isnull(ext_ceny.cena,"Z") Tim "Z"kem se cena nikdy nenahradi, ale isnull "pretypuje" typ decimal (cena) na typ retezec ("Z"). Pokud by ani to nefungovalo, nahradte "?" nejakym cislem, nejlepe -1. - pozn: UNION vyhazuje duplicitni zaznamy ! Zde to nevadi - vyrobky.id u nas bude vzdy unikatni, ale kdyby vyrobky.id bylo typu INT a meli bychom tam dva stejne radky (treba 2x 1,LSD,15), tak by nam to vypsalo jen jednou. Na druhou stranu kdy by tam bylo LSD dvakrat s ruznymi cenami, tak bychom dostali vypis dvou radku. Takze : "vada" UNIONu se da nekdy i vyuzit k dobrym vecem, ale pri pouzivani si davejte pozor - nekdy se nestacite divit ;-) 2] pouzijeme isnull. Nekdy se stava, ze bychom chteli neco jako SELECT .... IF podm THEN SELECTuj neco ELSE SELECTuj neco uplne jineho (X2) Pokud muzu rict, nejlepsi je to rozpadnout na vice SELECTu, jinak se vyrobi takova motanina ruznych sub-SELECtu, EXISTu, ze kdyz k tomu prijdete za tyden, myslite si, ze tuhle hruzu vyplodil nejaky genius (nebo silenec, podle toho, jestli to funguje, nebo ne) a ne vy :-) Nekdy (ale jen nekdy) lze napsat elegantni reseni pomoci storovanych procedur. Procedura isnull je v nasem pripade "zadratovana" rovnou do DB stroje a dela ve zkratce toto : isnull(in_par1,in_par2) { //pokud bude in_par1 NULL vrat in_par2 //jinak vrat in_par1 (ktery neni NULL) IF in_par1 IS NULL THEN RETURN in_par2 ELSE RETURN in_par1 END IF }; Napriklad SELECT id,nazev,cena FROM vyrobky 1 LSD 15 2 MDMA 30,12 3 trava 4 Extaze 5 Rezna 1,5 6 cigarety 7 pernik SELECT id,nazev,isnull(cena,0) FROM vyrobky 1 LSD 15 2 MDMA 30,12 3 trava 0 <- pokud nema cenu 4 Extaze 0 <- vrat 0 5 Rezna 1,5 6 cigarety 0 7 pernik 0 Procedura isnull nam muze nekdy nahradit (X2). Kdyz vyrobime dotaz takto: SELECT vyrobky.id,vyrobky.nazev, isnull(vyrobky.cena,ext_ceny.cena), isnull(vyrobky.cena,"A") FROM vyrobky, ext_ceny WHERE vyrobky.id = ext_ceny.id; #(X3) zda se, ze je vse v poradku, dokud se nam nevypise jen id nazev cena externi --------------------------------------------------------- 3 trava 3,14 "A" 4 Extaze 10 "A" 7 pernik "A" Sakra ! Jak to ? Odpoved je jednoducha :-) Podivejte se na radek oznaceny jako (X3) ... vyber vse z "vyrobky" a "ext_ceny", kde zaznam z vyrobku ma odpovidajici zaznam v externich cenach ... zaznamy 1,2,5,6 ho nemaji. A jsme doma ! Musime pouzit OUTER. SELECT vyrobky.id,vyrobky.nazev, isnull(vyrobky.cena,ext_ceny.cena), isnull(vyrobky.cena,"A") FROM vyrobky, OUTER(ext_ceny) #(X4) WHERE vyrobky.id = ext_ceny.id; id nazev cena externi --------------------------------------------------------- 1 LSD 15 2 MDMA 30,12 3 trava 3,14 "A" 4 Extaze 10 "A" 5 Rezna 1,5 6 cigarety 7 pernik "A" OUTER zabezpeci, ze pokud vazba "vyrobky"<->"ext_ceny" nebude existovat, radek se vybere. Proste : jako by tam bylo navazani na radek, ktery ma vsechny sloupce NULL. Takze finalni reseni (po kosmetickych upravach) je: SELECT vyrobky.id,vyrobky.nazev, isnull(vyrobky.cena,isnull(ext_ceny.cena,"?"), #(X5) isnull(vyrobky.cena,"A") FROM vyrobky, OUTER(ext_ceny) WHERE vyrobky.id = ext_ceny.id; id nazev cena externi --------------------------------------------------------- 1 LSD 15 2 MDMA 30,12 3 trava 3,14 "A" 4 Extaze 10 "A" 5 Rezna 1,5 6 cigarety ? 7 pernik ? "A" Procedura isnull se muze zanorovat - viz. (X5). Uff :-) Vas Jovo. ----------------------------------------------------------------------------- PS: - Samozrejme, kdyz pouzivate agregacni funkce (SUM,MAX,...), tak otaznik musite nahradit, nejlepe nulou. - isnull lze pouzit i v dotazech typu SELECT vrchni WHERE ... (SELECT spodni WHERE neco ze spodniho = neco z vrchniho) - vsimete si, jak jsem se vyhnul, aby ve sloupci bylo jen "A" a nikoli "A" kdyz je externi a "N" kdyz neni ;-) Kdyby tak melo byt, musely by se pouzit jiny mechanismus nez isnull (dva dotazy, temp tabulka,...) - pokud nekomu vadi cestina bez "hacku a carek", zaslu mu text i s diakritikou - Pro ty, kteri/ktere si priklady chteji overit : create temp table vyrobky ( id serial, nazev char(10), cena decimal(11,2) ); create temp table ext_ceny ( id int, cena decimal(11,2) ); insert into vyrobky values(1,"LSD",15); insert into vyrobky values(2,"MDMA",30.12); insert into vyrobky values(3,"trava",NULL); insert into vyrobky values(4,"Extaze",NULL); insert into vyrobky values(5,"Rezna",1.5); insert into vyrobky values(6,"cigarety",NULL); insert into vyrobky values(7,"pernik",NULL); insert into ext_ceny values(3,3.14); insert into ext_ceny values(4,10); insert into ext_ceny values(7,NULL); select * from vyrobky; select * from ext_ceny; drop table vyrobky; drop table ext_ceny;