NeoBase-opgørelser & replikat database > SQL-tips

Om SQL tips

se også Udveksling af SQL-udtryk mellem forskellige typer databaser samt Noter om AbsBD SQL

SQL, der i sin oprindelse er et forespørgselssprog overfor databaser, indeholder en begrænset række matematiske og statistiske funktioner.

Nogle af disse funktioner kan være lidt forskelligt implementeret i diverse databaser, hvorfor det kan være hensigtsmæssigt at anvende metoder, der forstås uanset valgte database-implementering.

Med dette udgangspunkt skal her gives en række tips, der fungerer for NeoBase 6 Reader samt for de mest almindelige SQL (server) databaser.

Talværdier vs. strengværdier

Når der for udtræk af data skal sættes betingelser eller grænser for udtrukne værdier, er det vigtigt at den konstant der refereres til er anført med den korrekte datatype, da man ellers ofte vil få fejlagtigt selekterede data.
Selvom data indeholder tal er det nemlig ikke sikkert, at data har datatype som tal, men kan have datatype som strengværdi (dvs. som tekst).
Selvom databasen måske accepterer den opstillede betingelse, er det således ikke sikkert man får de korrekte udtræk, hvis man angiver konstantens datatype forkert, eller man får en uhensigtsmæssig sorteringsorden af de udtrukne data.

Hvis data (trods indhold af tal-karakterer) er af datatypen strengværdi, skal den refererede konstant sættes i anførselstegn - f.eks.:
WHERE Forlosning = '3'

Hvis datatypen er numerisk, skal konstanten anføres uden anførselstegn, og med punktum som decimaltegn, hvor dette er relevant -f.eks.:
WHERE NavlesnorspH < 7.15

Hvis man sætter sin betingelse for et datofelt, vil man normalt anføre konstanten som en strengværdi formateret efter ISO-standarden, som f.eks.:
WHERE Fodselsdato BETWEEN '2004-03-15' AND '2005-03-14'

Tip vedr. datatyper

I datagitteret vil data af datatypen

•Strengværdi eller Datoværdi vises venstrestillet i den enkelte celle

•og Numeriske datatyper vil vises højrestillet i den enkelte celle

Division

Her skal man altid være opmærksom på, at det tal, der indgår som dividend, evt. kan have datatypen heltal hvorved en division vil fungere som en heltals-division - f.eks. 7 / 2 = 3.

Hvis man ønsker en ægte aritmetisk division, skal man altså sikre sig, at tallet trods værdi som heltal har datatype som flydende komma tal.
Dette kan gøres med:
CAST(<talværdi> AS FLOAT) / <divisor >
hvorved:
CAST(7 AS FLOAT) / 2 = 3.5

- se også Division by zero error

Afrunding

SQL-funktioner for afrunding kan alt efter den valgte database have varierende antal parametre, og vil hvis der afrundes til heltal ofte have datatypen heltal som resultant.

CEIL(<talværdi>)

afrunder til laveste heltal større end eller lig med den behandlede variabel (eller konstant) og afgiver resultanten som datatypen heltal.
For CEIL(2.5) returneres 3 og for CEIL(-2.5) returneres -2.
Funktionen benævnes i visse databaser CEILING.

FLOOR(<talværdi>)

afrunder til største heltal mindre end eller lig med den behandlede variabel (eller konstant) og afgiver resultanten som datatypen heltal.
For FLOOR(2.5) returneres 2 og for FLOOR(-2.5) returneres -3.

ROUND(<talværdi>)

der afgiver datatypen heltal, men kan også have forskellige andre implementeringer, og vil i nogle databaser kunne afrunde til decimaltal og f.eks. forlange:

ROUND(<talværdi>, <afrundingslængde>)

hvorimod f.eks. MS SQL Server forlanger:

ROUND(<talværdi>, <afrundingslængde> [, <afrundingstype>])

Et ganske væsentligt problem omkring funktionen ROUND i sin simpleste form er, at den opfattes (implementeres) forskelligt af forskellige leverandører, hvor nogle returnerer matematisk afrunding (til nærmeste hele tal) hvor andre returnerer den såkaldte Banker's Rounding*, hvor eksakt midtvejs-værdier (heltal + 0,5) afrunder til nærmeste lige heltal.

Af denne grund kan det aht. database-portabilitet være nødvendigt at anvende en mere primitiv metode vha. CAST(), hvor man ved afrunding til een decimal:

•ganger med 10

•adderer 0.5

•trunkerer til heltal vha. CAST(<talværdi> AS INTEGER)

•konverterer til flydende komma værdi med CAST(<talværdi> AS FLOAT)

•dividerer med 10

og hvor den resulterende formel ser således ud:

CAST(CAST(((<felt eller formel>) * 10 + 0.5) AS INTEGER) AS FLOAT) / 10 AS "Afrundet værdi"

Ved afrunding til 2 decimaler ganges og divideres der med 100 men uændret + 0.5 osv.

De + 0.5 sikrer at f.eks. 7.15 ... 7.24 efter *10 + 0.5 bliver til 72.0 ... 72.9, der ved heltals-trunkering bliver til 72 ... 72, der igen ved re-division bliver til 7.2 ... 7.2 - med andre ord at intervallet 7.15 ... 7.24 afrundes til 7.2.

Metoden fungerer dog kun korrekt for non-negative tal.
Metoden fungerer ikke med paradox tabeller, hvis der i den primært afledte værdi allerede er anvendt funktionen CAST, da paradox kun i ret begrænset omfang accepterer komplekse udtryk.

* Banker's Rounding:
Den aktuelle Absolute Database returnerer som default denne Banker's Rounding med

•ROUND(0.49) = 0,

•ROUND(0.5) = 0,

•ROUND(0.51) = 1,

•ROUND(1.49) = 1,

•ROUND(1.5) = 2,

•ROUND(2.49) = 2,

•ROUND(2.5) = 2,

•ROUND(2.51) = 3,

•ROUND(3.49) = 3,

•ROUND(3.5) = 4. etc.

Funktionen kan dog i lighed med MS SQL Server modtage supplerende parametre, hvor disse dog ikke er ISO-specificerede, samtidigt med at AbsDB og SQL Server anvender forskellige 3. parameter, hvorved funktionen bliver noget uforudsigelig ved portering til andre databaser.

MODULUS

Den i visse sammenhænge meget nyttige MODULUS funktion er ikke implementeret i ISO standarden og er ret forskelligt implementeret i de databaser, der medtager denne funktion.
For at opnå en universel ekvivalent for heltals-MODULUS kan anvendes følgende:

SELECT (X - (CAST(X AS INTEGER) / Y) * Y) AS "Afledt felt"

der i praksis ækvivalerer X MODULUS Y [≡ MODULUS(X;Y) ≡ X % Y ≡ diverse notationer ], og som forudsætter at dividenden X er eller kan konverteres til en heltals-datatype, hvorved divisionen kommer til at fungere som heltalsdivision med divisor Y, der kan udnyttes til opnåelse af den resulterende MODULUS-ekvivalent. En eller begge af variablerne X og Y vil kunne erstattes med feltreferencer eller konstanter.

Datoseparator og strengværdier til datoer

Datotolkningen af strengværdier volder ofte problemer pga. de forskellige datoformater i forskellige dele af verden samt fra PC til PC alt efter om brugeren bruger punktum eller bindestreg som datoseparator ('31.12.98' vs. '31-12-98') eller om databasen kun accepterer US format ('12/31/98').
Bedst er det at bruge ISO format (som '2016-06-31'), men hvor man kun har seks positioner til rådighed (f.eks. et text-datofelt [strDato] med f.eks. værdien '311216'), vil man for yderligere at kunne håndtere dette og specielt på datostrenge med kun to årstals-cifre som datoværdi være nødt til at konvertere dette f.eks. via følgende:
CAST(
(SUBSTRING(strDate FROM 1 FOR 2) + '-' + SUBSTRING(strDate FROM 3 FOR 2) + '-' + SUBSTRING(strDate FROM 5 FOR 2))
AS DATE)
AS "Dato"
hvor gyldigheden af datoseparatoren "-" er helt afhængig af den akuelle PC's opsætning for datoformat.
Har man en større samling SQL-udtryk med netop også datokonvertering, kan det ofte bedst betale sig blot at ændre PC'ens datoformat fra f.eks. '31-12-2016' til '31.12.2016', så det matcher formatet i de anvendte SQL-udtryk.

Se også den NeoBase specifikke mulighed for at anvende dynamisk datoseparator variabel: SQL Bruger-definerede udtryk.

NeoBase 6xe of NeoBase 6 Reader's forprogrammerede SQL-udtryk med datokonverteringer er alle parametriserede til dynamisk at tilpasse den aktuelle datoopsætning.

Datodifferencer og afledte beregninger med f.eks. AVG (gennemsnit)

Ved subtraktion af to datoer vil SQL i nogle databaser afgive en speciel dato-difference datatype, hvor det for at kunne foretage beregninger som f.eks. gennemsnitlige antal dage kan være nødvendigt at konvertere datodifferenceren til heltal som:

CAST((<dato_2> - <dato_1>) AS INTEGER) AS "Dato difference"

der sidenhen i en sekundær forespøgsel (på en sub-query eller temporær datatabel) kan refereres af andre (aggregations-) funktioner som SUM, MIN, MAX og AVG, f.eks. AVG("Dato difference").
Er de indgående felter ikke dato-felter men dato-tid felter, og ønsker man at relatere datodifference til nutid, kan formlen se sådan ud:

CAST((CAST(NOW AS DATE) - CAST(<dato-tid-felt> AS DATE)) AS INTEGER) AS "Alder af dato-tid-felt"

Hvis ikke datodifferencen CAST'es som heltalsværdi, vil differencer afgives som en dato i perspektiv af den retrograde proleptiske Gregorianske kalender (se nedenfor), der som "nulpunksdato" har laveste værdi 01.01.0001 (dvs. den teoretiske dato 1 januar år 1) med heltalsværdien 1, men uden at have et rigtigt nulpunkt, hvorfor der ikke findes nogen dato svarende heltalsværdien 0.
Datodifferencer på identiske datoer uden konvertering til heltal er derfor illegale og giver fejlmeddelelsen '0.0' is not a valid timestamp. med dialogen:
0.0NotValidTimeStamp

Ugedage (1 ... 7 / mandag ... søndag)

SQL indeholder ikke som standard en ugedagsfunktion, men da databaser såvel som regneark aht. beregning af datodifferencer altid for hver datoværdi har en underliggende heltalsværdi (seriel datoværdi), vil man kunne beregne ugedagen, hvis man kender de serielle datoværdiers nulpunktsdato*, som kan være forskellig fra program til program. For Excel repræsenterer værdien 0 for de tidlige versioner datoen 1. jan. 1904 og for de senere versioner værdien 1 datoen 1. jan. 1900.
For herværende samt en række andre databaser repræsenterer serielværdien 1 den teoretiske dato 1. januar år 1 i den proleptiske Gregorianske kalender (dvs. den Gregorianske kalender retrogradt udrullet fra nutid, uagtet tidligere tiders Julianske kalender, historisk irregulære skuddage, etc.).
Da denne reelle dag (men hypotetiske dato = dag 1) var en mandag, kan ugedage som mandag =1 ... søndag =7 beregnes som følger:
(CAST(dato AS INTEGER)-1 - ((CAST(dato AS INTEGER)-1) / 7) * 7) + 1 AS "Ugedag"
Denne formel må naturligvis tilpasses det givne databaseprograms anvendte nulpunktsdato, såvel som den stedlige konvention for hvilken ugedag, der regnes som 1. ugedag. Hvis man f.eks. i stedet har som standard, at søndag er den første dag i ugen (søndag =1 ... lørdag =7), skal udtrykket se således ud:
(CAST(dato AS INTEGER)-0 - ((CAST(dato AS INTEGER)-0) / 7) * 7) + 1 AS "Ugedag"

For herværende specifikke database er en enklere måde at få ugedagene på at få dem som tekst fremfor som ugedagsnumre ved simpelt at formatere datoværdier som:
CAST(TOSTRING(datofelt, 'ddd') AS CHAR(3)) AS "Ugedag"
hvor TOSTRING dog for andre databaser evt. må erstattes af anden funktion da TOSTRING ikke er en ISO SQL funktion. I den viste formatering vil ugedagene i det aktuelle sprog vises som "ma", "ti", "on", "to", "fr", "lø", "sø".

NULL-evaluering, filtrering på NULL-værdier

Helt stringent kan man ikke tale om "NULL-værdier", da NULL netop repræsenter manglende/ikke-eksisterende data og dermed heller ikke kan evalueres for andet end lige netop at være manglende.
For rent praktiske sprogbrug vil man dog alligevel ofte bruge denne "NULL-værdi" betegnelse for felter hvis "værdi" netop er NULL.

Da NULL netop ikke har nogen reel værdi, vil selektions-udtrykkene
WHERE [felt] <> [real værdi]
og
WHERE [felt] < [real værdi]
i databaser, der overholder ISO-standarden, udelukke poster, hvor [felt] = NULL, idet "værdien" NULL netop ikke har nogen størrelse og derfor heller ikke kan evalueres i forhold til en reel værdi.
Hvis NULL-værdier ønskes inkluderet foreslås f.eks. udtrykket:
WHERE [felt] < [real værdi] OR [felt] IS NULL

Duplikate værdier eller records

Hvis man vil undgå at få vist duplikate records, gøres dette nemmest ved et simpelt SELECT DISTINCT prædikat:

SELECT DISTINCT *
FROM TabelNavn;

Hvis man skal teste for duplikate værdier på eet eller flere datafelter kan dette gøres ved f.eks.:

SELECT PersonId, Kryptograferingsnr, COUNT(*) AS "Forekomster"
FROM NeoBase2
GROUP BY PersonId, Kryptograferingsnr
HAVING COUNT(*) > 1;

Hvis man vil have vist samtlige felter, hvor der er testet for duplikation på et antal udvalgte felt, kan dette gøres ved at mellemlagre dette udtræk i en IN MEMORY TABLE, der praktisk kunne kaldes "Temp", for derefter at lade udtrækket med det fulde antal felter relatere til det dette mellemlagrede sæt via et JOIN:

SELECT neo.[F.dato], neo.Løbenummer, neo.[Indlæg.dato], neo.Udskrivningsdato, COUNT(neo.*) AS "Forekomster"
INTO MEMORY Temp
FROM NEONAT94 neo
GROUP BY
neo.[F.dato], neo.Løbenummer, neo.[Indlæg.dato], neo.Udskrivningsdato
HAVING COUNT(*) > 1;
SELECT *
FROM NEONAT94 neo
JOIN MEMORY Temp mt
ON neo.[F.dato] = mt.[F.dato]
AND neo.Løbenummer = mt.Løbenummer
AND neo.[Indlæg.dato] = mt.[Indlæg.dato]
AND neo.Udskrivningsdato = mt.Udskrivningsdato;

Læg mærke til hvordan der holdes styr på hvordan enslydende feltnavne refererer specifikt til det hvert sit udtræk med tabel alias navnene "neo" og "mt".

Der findes desværre (iht. SQL ISO standard) ikke nogen nem måde at teste for duplikate records på uspecifikt antal felter (f.eks. alle felter) uden at anføre alle disse felter i testudtrækket.

Evaluering på tomme strengfelter

Felter med datatype af strengværdier vil, hvis feltet er tomt, iht. SQL-standarden ikke tolkes som havende "værdien" NULL men derimod en streng af længden = 0, dvs. tomt felt.
Ved søgning på tomme strengværdi-felter, skal man således generelt bruge kriteriet:
WHERE [strengfelt] = '' (to tætstillede anførselstegn uden mellemrum)
eller
WHERE LENGTH([strengfelt]) = 0
Hvis man vil inkludere poster, hvor det filtrerede felt er tomt samt alternativt evt. er "tomt" ved kun at indeholde mellemrumstegn, kan man sætte kriteriet:
WHERE (TRIM([strengfelt]) = '' (to tætstillede anførselstegn uden mellemrum)
eller
WHERE LENGTH(TRIM([strengfelt])) = 0
En række databaser, heriblandt den velkendte Oracle foruden den her anvendte Absolute Database samt Paradox og MS Access, accepterer dog at returnere tomme strengværdifelter med kriteriet
WHERE [strengfelt] IS NULL
men af hensyn til udtrykkets portabilitet bør man være tilbageholdende med at udnytte denne afvigelse fra ISO SQL-standarden.

Evaluering på NULL-værdier eller tomme datafelter på felter af ukendt datatype

Den ovennævnte skelnen mellem tomme streng-felter og tomme felter af anden datatype, er et forhold man skal være særlig opmærksom på, hvis man udfører SQL-udtræk med selektions-kriterier overfor felter af ukendt datatype - f.eks. overfor databaser, hvor talværdier eller datoer (uhensigtsmæssigt) er lagret som tekst fremfor som de mere relevante tal- eller dato-datatyper, eller hvor tilsyneladende talværdier reelt har tekstmæssig karakter og er lagret som sådan, f.eks. en betegnelse på et-eller-andet kategori '1', '2', '3', etc.

__________________________
*) Nulpunktsdato for databaser:
01.01. år 1 (seriel værdi = 1): ISO/ANSI SQL, IBM (DB2), Informix, Ingres, InterBase, MySQL, MS SQL-Server 2008 (DateTime2) og frem, Paradox, Absolute (herunder NeoBase 6).
01.01. år 1753 (seriel værdi = 1): Microsoft SQL-Server, Sybase.
01.01. år 4713 BC (seriel værdi = 1): Oracle.
01.01. år 1904 (seriel værdi = 0): Tidlige versioner af Microsoft Excel.
01.01. år 1900 (seriel værdi = 1): Microsoft Excel senere versioner.
01.01. år 100: Microsoft Access.

_____________________________