home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
What is available?ABIS Infor » 2017-09 » 2017-01 » 2016-11 » 2016-06 » 2016-04 » 2016-02 » 2015-03 » 2014-03 » 2013-04 » 2013-01 » 2012-10 » 2012-06 » 2012-04 » 2012-01 » 2011-11 » 2011-10 » 2011-08 » 2011-06 » Just another newsletter? » Gehinderd door teveel kennis ter zake » ISPF - I like Such a Powerful new Feature » Enter Oracle spreadsheets » XQuery vs. XSLT: battle or coexistence?Exploring Db2Presentations and papersTest your knowledge 

ABIS Infor - 2011-06

Enter Oracle spreadsheets

Kris Van Thillo (ABIS) - 31 mei 2011

Samenvatting

Nog steeds worden spreadsheets enorm veel gebruikt - hun flexibiliteit kent inderdaad nauwelijks grenzen. Eén van de belangrijkste uitdagingen eigen aan een spreadsheet blijft evenwel 'de data'. Die is veelal afkomstig van een derde bron, die dus in de spreadsheet moet worden gekopieerd. Als die derde bron wijzigt... juist! Ook de integratie van data en formules kan voor problemen zorgen. De oplossing: Oracle spreadsheets!

Het idee is even eenvoudig als spectaculair - want is het resultaat van een SQL select statement geen tabel, dus, een soort spreadsheet? We hebben dus enkel een SQL select instructie nodig die het 'standaard' select resultaat omvormt tot een spreadsheet resultaat, waarop we dan standaard spreadsheet berekeningen kunnen uitvoeren! En dat is nu juist de taak van de SQL select 'model' instructie.

De model-instructie

De 'model' instructie is niet éénvoudig; en vergt waarschijnlijk enig lees- en studiewerk. In hoofdlijnen - en sterk vereenvoudigd - komt het hierop neer.

Na het bepalen van de query resultset (voor select en order by instructies worden aangepakt) wordt de model instructie uitgevoerd. Deze instructie beslaat globaal 4 onderdelen (niet steeds allen verplicht):

Voorbeeld 1

Bekijk onderstaand voorbeeld (stel dat we beschikken over gegevens inkomen en cursustitel voor de jaren 2005 en 2006).

select * 
from modeltest
model 
partition by (ctitle)
dimension by (cyear) 
measures (ccount, cincome)
rules (cincome['2007']=cincome[2005]*1.25,
       ccount['2007']=sum(ccount)[ANY]
)
order by ctitle, cyear

Concreet:

In ons voorbeeld wordt door de model clause één extra rij aangemaakt: de rij met 'dimensie' 2007, en een cincome en ccount als bepaald door de 'rules'. Natuurlijk: een extra rij 'per sheet'!

Krachtig inderdaad, maar snel complex!

Voorbeeld 2

select * 
from modeltest
model 
dimension by (ctitle, cyear) 
measures (ccount, cincome, 0 as val1, 0 as val2, 0 as val3, 0 as val4, 0 as val5)
rules (
val1[ctitle, cyear]=sum(cincome)[ANY, ANY],
val2[ctitle, cyear]=sum(cincome)[cv(ctitle), ANY],
val3[ctitle, cyear]=cincome[cv(ctitle), cv(cyear)]/sum(cincome)[cv(ctitle), ANY] *100,       
val4[ctitle, cyear]=cincome[cv(ctitle), cv(cyear)]/sum(cincome)[ANY, ANY] *100,
val5[ctitle, 
cyear]=cincome[cv(ctitle),CV(cyear)]-cincome[cv(ctitle),CV(cyear)-1]/cincome[cv(ctitle),CV(cyear)]*100) 
order by ctitle, cyear

Eénvoudig zeker niet, krachtig in elk geval. Afhankelijk van de te realiseren doelstellingen zijn er mogelijk alternatieve SQL instructies denkbaar. Vaak zullen deze niet minder complex zijn, in elk geval unions bevatten, en daardoor SQL-technisch minder efficiënt.

Tot slot

Tot slot: voor sudoku fanaten die absoluut prijzen willen winnen. Google op volgende strings 'SQL', 'Model' en 'Sudoku'! Maar om echt zeker te zijn voeg je natuurlijk nog één string toe... 'Oracle'!