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;