Fonctions valides (fonctions de groupe)
Fonction(x) Retourne ? Exemple
AVG(x) la moyenne de X pour un groupe de ligne AVG(quantite)
COUNT(*) le nombre de lignes sélectionnées
COUNT(DISTINCT X) le nombre de valeurs différentes pour X COUNT(distinct nocli)
MAX(X) retourne la plus grande valeur sélectionnée MAX(DATLIV)
MIN(X) retourne la plus petite valeur sélectionnée MIN(prix)
SUM(x) retourne la somme de X SUM(qte * prix)
VAR(X) retourne la variance
STDDEV(X) retourne l'écart type
Fonctions valides (ligne à ligne)
Fonction(x) Retourne ? Exemple
MAX(X,Y) retourne la plus grande valeur de X ou de Y MAX(prixHA, pritarif) * qte
MIN(X,Y) retourne la plus petite valeur de X ou de Y MIN(datce, datliv)
ABSVAL(x) la valeur absolue de x ABSVAL(prix) * qte
CEIL(x) Retourne l'entier immédiatement supérieur à X CEIL(2,42) = 3 CEIL(2,56) = 3
RAND() Retourne un nombre aléatoire
ROUND(x , y) Retourne l'arrondi comptable à la précision y ROUND(2,42 , 1) = 2,40
ROUND(2,56 , 1) = 2,60
SIGN(x) Retourne -1 si x est négatif, 1 s'il est positif, 0 s'il est null Where SIGN(x) = -1
TRUNCATE(x , y) Retourne le chiffre immédiatement inférieur à X
(à la précision y) TRUNCATE(2,42 , 1) = 2,40 TRUNCATE(2,56 , 1) = 2,50
DEC(x , l, d) x au format numérique packé avec la lg et la précision demandée. DEC(zonebinaire)
DEC(avg(prix), 9, 2)
DIGITS(x) x en tant que chaîne de caractères DIGITS(datnum)
CHAR(x) x en tant que chaîne de car. (x étant une date) CHAR(current date)
FLOAT(x) x au format "virgule flottante" FLOAT(qte)
INT(x) x au format binaire INT(codart)
ZONED(x) x au format numérique étendu ZONED(prix)
CAST(x as typeSQL[lg]) x au format indiqué par typeSQL :
types valides
INT | INTEGER
SMALLINT
DEC(lg, nb-décimales)
NUMERIC(lg, nb-décimales)
FLOAT | REAL | DOUBLE
CHAR | VARCHAR
- --FOR BIT DATA-
-- -FOR SBCS ---
----FOR nø-ccsid *--
DATE
TIME
TIMESTAMP
* : un CSSID est un ‚quivalent code-page associ‚ … une donn‚e (france = 297)
CAST(qte AS CHAR(9))
Attention les zéros de gauche sont éliminés
CAST(prixchar as NUMERIC(7, 2))
cast('123456,89' as numeric(8, 2)) fonctionne
cast('123456,89' as numeric(7, 2))
donne une erreur
(trop peu d'entiers)
STRIP(x) ou
TRIM(x)
RTRIM(x) LTRIM(x)
supprime les blancs au deux extrémités de x.
• les blancs de droite
• les blancs de gauche TRIM(raisoc)
LENGTH(x) ou
OCTET_LENGTH(x)
la longueur de x LENGTH(nom)
LENGTH(TRIM(nom))
CONCAT(x , y) concatene X et Y (aussi x CONCAT y ou X !! Y) CONCAT(nom, prenom)
SUBSTR(x, d, l) extrait une partie de x depuis D sur L octets SUBSTR(nom, 1, 10)
SUBSTR(nom, length(nom), 1)
LEFT(x, l) extrait une partie de x depuis 1 sur L octets LEFT(nom, 10)
RIGHT(x, l) extrait les L derniers octets de x RIGHT(nom, 5)
SPACE(n) retourne n blancs nom concat space(5) concat prenom
REPEAT(x , n) retourne n fois x repeat('*', 15)
MOD(x, y) le reste de la division de x par y MOD(annee, 4)
RRN(fichier) N° de rang RRN(clientp1)
TRANSLATE(x)
UPPER(x)
UCASE(x)
X en majuscule WHERE
UCASE(RAISOC) LIKE 'VO%'
LOWER(x)
LCASE(x) x en minuscule WHERE
LCASE(ville) LIKE 'nan%'
TRANSLATE( x, remplace, origine) Remplace tous les caractères de X présent dans origine par le caractère de même position dans remplace. TRANSLATE(prixc, ' F', '0$')
remplace 0 par espace et $ par F
REPLACE( x, origine, remplacement) Remplace la chaîne de caractère origine par la chaîne remplacement dans x REPLACE(x, 'Francs' , 'Euros')
remplace Francs par Euros
SOUNDEX( x) Retourne le SOUNDEX (représentation phonétique) de X [basé sur un algorhytme anglo-saxon] Where SOUNDEX(prenom) = SOUNDEX('Henri')
DIFFERENCE( x) Retourne l'écart entre deux SOUNDEX
[0 = très différents, 4 = trés proches] Where DIFFRENCE(prenom, 'HENRI)> 2
VALUE(x, y)
IFNULL(x, y) retourne X s'il est non null, sinon Y IFNULL(DEPT, 0)
NULLIF(x, y) retourne NULL si X = Y NULLIF(Prix, 0)
LOCATE(x, y ,d) retourne la position à laquelle x est présent dans y ou 0
(la recherche commence en D, qui est facultatif) LOCATE(' ', raisoc)
POSITION(x IN y) idem LOCATE POSITION(' ' IN raisoc)
INSERT(x, d, nb, ch) insert ch dans x à la position d, en remplacant nb octets (0 admis)
DATABASE() retourne le nom de la base (enregistré par WRKRDBDIRE)
ENCRYPT_RC2(x, p, a) Encrypte x en utilisant p comme mot de passe
(a est l'astuce mémorisée pour se souvenir du mot de passe)
ENCRYPT_RC2(data, 'AS400', 'avant I5')
ENCRYPT_TDES(x, p, a) * Encrypte (algorithme TDES) x en utilisant p comme mot de passe
(a est l'astuce mémorisée pour se souvenir du mot de passe)
ENCRYPT_TDES(data, 'AS400', 'avant I5')
GET-HINT(x) retrouve l'astuce associée à x
GET-HINT(data) --> 'avant I5'
DECRYPT_BIT(x) retourne (en varchar for bit data) les données d'origine de x.
(on doit lancer avant SET ENCRYPTION PASSWORD = p)
DECRYPT_BINARY(x) retourne (en binary) les données d'origine de x.
(on doit lancer avant SET ENCRYPTION PASSWORD = p)
DECRYPT_CHAR(x) retourne (en VARCHAR) les données d'origine de x.
(on doit lancer avant SET ENCRYPTION PASSWORD = p)
CASE
when ... then ..
when ... then ..
[else]
END retourne la valeur du premier THEN ayant la clause WHEN de vérifiée. CASE dept
WHEN 44 then 'NANTES'
WHEN 49 then 'ANGERS'
ELSE 'Hors région'
END AS METROPOLE
ou bien
CASE
WHEN prix < 0 then 'négatif'
WHEN codart = 0 then 'inconnu'
ELSE 'positif ou nul'
END
Fonctions OLAP
ROW_NUMBER() numérote les lignes sur un critère de tri
select ROW_NUMBER() over (order by prix), codart, libart from articles [order by autre-chose]
RANK() attribue un rang (en gérant les ex-aequo, par exemple 1-1-3-4-4-4-7)
select RANK() over (order by prix), codart, libart
from articles
DENSE_RANK() attribue un rang consécutif (par exemple 1-1-2-3-3-3-4)
select DENSE_RANK() over (order by prix), codart, libart from articles
Cas particulier des dates
On ne peut utiliser l'arithmétique temporelle qu'avec des dates, des heures, des horodatages
les calculs peuvent se faire sous la forme
date + durée = date
date - durée = date
date - date = durée
heure + durée = heure
etc ..
les durées peuvent être exprimées de manière explicite avec
YEARS MONTHS DAYS
HOURS MINUTES SECONDS
les durées résultat (datcde - datliv) seront toujours exprimées sous la forme AAAAMMJJ, où :
AAAA represente le nombre d'années
MM le nombre de mois
JJ le nombre de jours
Ainsi, si SQL affiche 812, il faut comprendre 8 mois, 12 jours
40301 signifie 4 ans , 03 mois, 01 jour (attention SQL risque d'afficher 40.301)
Fonctions liées aux dates
Fonction(x) Retourne ? Exemple
DATE(x)
X doit être une chaîne au format SQL
(celui du JOB par défaut)
une date (sur laquelle les fonctions suivantes s'appliquent) DATE(
substr(digits(dat8), 7, 2)
concat '/' concat
substr(digits(dat8), 5, 2)
concat '/' concat
substr(digits(dat8), 3, 2) )
DAY(D)
DAYOFMONTH(D) retourne la partie jour de D
(doit être une date ou un écart AAAAMMJJ). DAY(DATCDE)
MONTH(D) retourne la partie mois de D (idem) MONTH(current date)
YEAR(D) Retourne la partie année de D (idem) YEAR(current date - DATCDE)
DAYOFYEAR(D) retourne le n° de jour dans l'année (julien) DAYOFYEAR(datdep)
DAYOFWEEK(D) retourne le N° de jour dans la semaine
(1 = Dimanche, 2=Lundi, ...) DAYOFWEEK(ENTRELE)
DAYOFWEEK_ISO(D) retourne le N° de jour dans la semaine
(1 = Lundi, ...) DAYOFWEEK_ISO(ENTRELE)
DAYNAME(d) retourne le nom du jour de d (Lundi, Mardi, ...) DAYNAME(datcde)
MONTHNAME(d) retourne le nom du mois de d (Janvier, Février, ...) MONTHNAME(datcde)
EXTRACT(day from d) Extrait la partie jour de D (aussi MONTH et YEAR) EXTRACT(MONTH from datcde)
DAYS(D) retourne le nbr de jours depuis 01/01/0001 DAYS(datcde)- DAYS(datliv)
QUARTER(D) retourne le n° du trimestre QUARTER(DATEFIN)
WEEK(D) retourne le n° de semaine
(Attention 01/01/xx donne toujours semaine 1) WHERE
WEEK(DATLIV)= WEEK(DATCDE)
WEEK_ISO(D) retourne le n° de semaine
(la semaine 1 est celle qui possède un JEUDI dans l'année.) WHERE
WEEK_ISO(DATLIV)= WEEK_ISO(DATCDE)
CURDATE() retourne la date en cours, comme CURRENT DATE
CURTIME() retourne l'heure en cours, comme CURRENT TIME
NOW() retourne le timestamp en cours
JULIAN_DAYS(d) retourne le nbr de jours qui sépare une date du 1er Janv. 4712 av JC. JULIAN_DAYS(datcde)
LAST_DAYS(d) * retourne la date correspondant au dernier jour du mois. LAST_DAYS('2006-04-21') = 2006-04-30
ADD_MONTHS(d, nbr ) * ajoute un nbr de mois à une date ,
si la date est au dernier jour du mois, la date calculée est aussi au dernier jour du mois ADD_MONTHS('2006-04-30' , 1) = 2006-05-31
NEXT_DAYS(d, 'day' ) * retourne le timestamp de la prochaine date ayant le jour demandé NEXT_DAYS('2006-12-31' , 'DIM') = ' 2007-01-07-00.00.00.000000'
Fonctions liées aux heures
Fonction(x) Retourne ? Exemple
TIME(T) une heure TIME(
substr(digits(h6), 1, 2)
concat ':' concat
substr(digits(h6), 3, 2)
concat ':' concat
substr(digits(h6), 5, 2) )
HOUR(T)
retourne la partie heure de T HOUR(Pointage)
MINUTE(D) retourne la partie minute de T
SECOND(T) Retourne la partie secondes de T
EXTRACT(hour from t) la partie heure de T (aussi MINUTE et SECOND) EXTRACT(SECOND from pointage)
Fonctions liées aux Timestamp
Fonction(x) Retourne ? Exemple
TIMESTAMP(T) un timestamp (date - heure - microsecondes) TIMESTAMP('
1999-10-06.15.45.00.000001 ')
TIMESTAMP
(D T)
un timestamp (microsecondes à 0) TIMESTAMP(datcde heure)
TIMESTAMP_ISO(x)
un timestamp à partier de x
Si x est une date, l'heure est à 00:00:00
Si x est une heure, la date est à aujourd'hui. TIMESTAMP_ISO(heure_pointage)
TIMESTAMPDIFF
(c 'DIFFERENCE')
C indique l'unité de mesure de l'écart que vous souhaitez obtenir
1 = fractions de s. 16 = jours
2 = secondes 32 = semaines
4 = minutes 64 = mois
8 = heures 128 = trimestres
256 = Année
'DIFFERENCE' est la représentation caractères [ CHAR(22) ] d'un écart entre deux timestamp.
TIMLESTAMPDIFF(32 ,
CAST(CURRENT_TIMESTAMP
- CAST(DATLIV AS TIMESTAMP)
AS CHAR(22)) ) indique l'écart en semaines entre DATLIV et aujourd'hui
MIDNIGHT_SECONDS retourne le nbr de secondes qui sépare un timestamp de minuit MIDNIGHT_SECONDS(pointage)
VARCHAR_FORMAT(d, 'YYYY-MM-DD HH24:MI:SS' ) * Transforme un timestamp en chaine (le format est imposé) VARCHAR_FORMAT( now() )
GENERATE_UNIQUE() * genère une valeur unique de type CHAR(13) basée sur le timestamp en cours. insert GENERATE_UNIQUE() ....
plus toutes les fonctions liées aux dates et aux heures
Sous sélections, Ordre SQL intégré dans la clause WHERE (ou dans la liste des colonnes) d'un ordre SQL :
SELECT * FROM tarif WHERE prix < (SELECT AVG(prix) from tarif)
donne la liste des articles ayant un prix inférieur à la moyenne
--------------------------------------------------------------------------------
SELECT * FROM tarif WHERE prix BETWEEN
(SELECT AVG(prix)*0,9 from tarif) AND (SELECT AVG(prix)*1,1 from tarif)
donne la liste des articles ayant un prix variant d'au maximum +/- 10 % par rapport à la moyenne
--------------------------------------------------------------------------------
SELECT * FROM tarif T WHERE prix <
(SELECT AVG(prix) from tarif Where famille = t.famille)
donne la liste des articles ayant un prix inférieur à la moyenne de leur famille
--------------------------------------------------------------------------------
Select codart , (qte * prix) as montant, (select sum(qte * prix) from commandes where
famcod = c1.famcod) as global_famille
from commandes c1
donne la liste des commandes (article, montant commandé), en rappelant sur chaque ligne le montant global commandé dans la famille.
--------------------------------------------------------------------------------
vous pouvez aussi utiliser la clause EXISTS dans un SELECT imbriqué.
• Elle indique VRAI si le select imbriqué retourne une ligne (ou plus)
• Elle indique FAUX si le select imbriqué ne retourne aucune ligne.
soit un fichier article ayant une colonne "unité_stockage" et un fichier stock,
il s'agit de supprimer les articles dans le fichiers stock si la zone "unité_stockage" est à blanc dans le fichier article.
DELETE from stock S where exists
(SELECT * from articles where codart = S.codart
and "unité_stockage" = ' ')
Accueil
