SQL 89

SELECT colonne1 [as entete1],
       colonne2 [as entete2]
( select imbriqué retournant une valeur) as entete3
FROM fichier1 [f1], fichier2 [f2]
WHERE [critères de jointure et sélection]
GROUP BY colonne
HAVING [sélection]

ORDER BY colonne [ASC|DESC]
ou N°-de-colonne

FETCH FIRST n ROWS ONLY

... where (cepage1, cepage2) = ('Syrah' , 'Grenache')

nom LIKE 'DU%'   (commence par)
nom LIKE '%PON%' (contient)
nom LIKE '%RAND' (se termine par)

aussi : nom LIKE '%'concat ville concat '%'

test la valeur nulle (pratique avec les jointures externes)

Where REGEXP_LIKE(pr_nom , 'ch[aâä]teau')

Where JDATA IS JSON

Where JSON_EXISTS(JDATA   'strict $.TEL'   FALSE ON ERROR)

Where EXISTS(select * FROM ...) -- voir la fin de ce mémo

CEIL(2,42) = 3 
CEIL(2,56) = 3 

FLOOR(2,42) = 2 
FLOOR(2,56) = 2 

ROUND(2,42 , 1) = 2,40 
ROUND(2,56 , 1) = 2,60 

TRUNCATE(2,42 , 1) = 2,40 
TRUNCATE(2,56 , 1) = 2,50 

retourne en VARCHAR une chaîne CHAR

Select VARCHAR_FORMAT(PR_NOM) from producteurs

retourne en chaine une information numérique
avec dans le masque :

• 0 un chiffre
• 9 un chiffre à blanc si non significatif
• . le point comme marque décimale
• , la virgule comme séparateur de milliers
• S le signe (+ ou -)
• $ le symbole monétaire $
• L le symbole monétaire en cours
• D la marque décimale en cours
• G le séparateur de groupe en cours
  

VALUES VARCHAR_FORMAT(1,5 - 1,4, '0D0') -> '0,1'

x au format indiqué par typeSQL :

types valides :
INT | INTEGER
SMALLINT
DEC(lg, dec)
NUMERIC(lg, dec)
FLOAT | REAL | DOUBLE
CHAR | VARCHAR
- --FOR BIT DATA-
-- -FOR SBCS ---
----FOR n°-ccsid *--
DATE
TIME
TIMESTAMP

* (France = 297, US = 37)

LENGTH(x) ou
OCTET_LENGTH(x)

RPAD(chaine , n , 'c')

LPAD(chaine , n , 'c')

TRANSLATE(x)
UPPER(x)
UCASE(x)

Encrypte x en utilisant p comme mot de passe
(a est l'astuce mémorisée pour se souvenir du mot de passe)

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)

Encrypte (algorithme AES) x en utilisant p comme mot de passe
(a est l'astuce mémorisée pour se souvenir du mot de passe)

retrouve l'astuce associée à x

retourne (en varchar for bit data) les données d'origine de x.
(sans pwd, on doit lancer avant SET ENCRYPTION PASSWORD = p)

retourne (en binary) les données d'origine de x.
(sans pwd, on doit lancer avant SET ENCRYPTION PASSWORD = p)

retourne (en VARCHAR) les données d'origine de x.
(sans pwd, on doit lancer avant SET ENCRYPTION PASSWORD = p)

retourne 1 si l'utilisateur appartient à l'un des groupes indiqués, 0 sinon.

                       +--SESSION_USER--+
VERIFY_GROUP_FOR_USER(-+------USER------+--, G1 [, Gn] --)
                       +-CURRENT_USER---+            

retourne la dernière valeur assigné à une zone avec l'attribut AS IDENTITY
(voir plutôt la clause FINAL TABLE, maintenant)

retourne la valeur hexa d'un chaine

HEX('bonjour') -> 8296959196A499
X'8296959196A499' -> Bonjour

Retourne la suite binaire formattée

varbinary_format('A7B37D20-915D-45C4-8D0B-B5AA0F864FCA',
'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') --adresse mac
-> BX'A7B37D20915D45C48D0BB5AA0F864FCA 
        

retourne la valeur hexa, formatée, d'une chaîne.

VALUES VARCHAR_FORMAT_BINARY('123456789abcdef0',    
'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')             

-> F1F2F3F4-F5F6-F7F8-F981-8283848586F0
        

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

numérote les lignes affichées

select ROW_NUMBER() over (), codart, libart from articles
[order by un tri]

numérote les lignes sur un critère de tri (ici le prix)

select ROW_NUMBER() over (order by prix), codart, libart from articles [order by autre-tri]

numérote les lignes sur un tri (le prix) à l'intérieur d'une même famille

select ROW_NUMBER() over (Partition by FAM Order by prix), codart, libart from articles [order by autre-tri]

attribue un rang unique à chaque ligne, en gérant les ex-aequo
(par exemple 1-1-3-4-4-4-7)

attribue un rang unique et consécutif (sans trou) à chaque ligne
(par exemple 1-1-2-3-3-3-4)

Retourne le % du rang (entre 0 et 1)

Retourne la valeur de X de la ligne du dessus

Retourne la valeur de X de la ligne du dessous

Retourne la quantile (NTILE(10) = le décille)

Retourne la distribution cumulée (le dernier vaut 1)

Retourne la première valeur de X suivant le tri

Retourne la dernière valeur de X suivant le tri

Retourne la Nième valeur de X suivant le tri

Retourne le % de la somme cumulée

soit le SELECT basique suivant -->

affiche les totaux pour 2 GROUPES consécutifs

affiche 1 total par groupe puis des ruptures de niveau supérieur

affiche les totaux pour tous les groupes possibles

indique si cette ligne est le résultat de ROLLUP (rupture)

les fonctions d'agrégation peuvent être utilisées comme des fonctions OLAP (avec OVER) et sans GROUP BY

production d'un flux XML à partir d'une chaîne de caractère.
Cette action est implicite lors des ordres INSERT et UPDATE, dans une colonne de type XML.

production après vérification, d'un flux XML, avec choix de conservation des espaces ou non

validation d'un flux XML à l'aide d'un schéma XSD enregistré dans XSROBJECTS

transforme un flux XML à l'aide de XSLT

retourne un texte compatible XML

select  XMLTEXT('100 est > à 99 & à 98') FROM SYSIBM.SYSDUMMY1  ;

  ==> 100 est > à 99 & à 98

production d'un élément XML

select XMLELEMENT(name "numero" , nocli) from clients

      <numero>1</numero>
      <numero>2</numero>

select xmlelement(name "client:nom", XMLNAMESPACES('http://www.volubis.fr/clients/1.0' AS "client") ,
raisoc) from clients;

  <client:nom xmlns:client="http://www.volubis.fr/clients/1.0">IBM</client:nom>
  <client:nom xmlns:client="http://www.volubis.fr/clients/1.0">Rational</client:nom>

SELECT XMLPI(NAME "Instruction", 'APPUYEZ SUR ENTREE')  
    FROM SYSIBM.SYSDUMMY1

<?Instruction APPUYEZ SUR ENTREE?>

select  XMLCOMMENT('A consommer avec modération') 
   FROM SYSIBM.SYSDUMMY1  ;

<!--A consommer avec modération-->

select XMLCONCAT( XMLELEMENT(name "numero", nocli) ,        XMLELEMENT(name "nom", raisoc) ) from clients

     <numero>1</numero><nom>IBM</nom>
     <numero>2</numero><nom>Rational</nom>

select XMLFOREST(nocli , raisoc) from clients

     <NOCLI>1</NOCLI><RAISOC>IBM</RAISOC>
     <NOCLI>2</NOCLI><RAISOC>Rational</RAISOC>

select XMLROW(nocli , raisoc) from clients

     <row> <NOCLI>1</NOCLI><RAISOC>IBM</RAISOC> </row>
     <row> <NOCLI>2</NOCLI><RAISOC>Rational</RAISOC> </row>

select xmlagg(XMLELEMENT(name "nom" , raisoc)) from clients

<nom>IBM</nom><nom>Rational</nom>     ....

select xmlgroup(XMLELEMENT(name "nom" , raisoc)) from clients

<rowset>
<row><NOCLI>1</NOCLI><RAISOC>IBM</RAISOC><VILLE>New York </VILLE></row>
<row><NOCLI>2</NOCLI><RAISOC>Rational</RAISOC><VILLE>Toronto </VILLE></row>
</rowset>

Fonction Table qui traite sous forme colonnée (relationnelle), les éléments d'un flux XML. La source peut être :

• une colonne de type XML
• le résultat de GET_XML_FILE
• le résultat de HTTPGETBLOB|CLOB

SELECT X.NOM, X.RUE, X.TEL FROM
 XMLTABLE ('$c/customerinfo' passing <source> as "c"
   COLUMNS
      NOM CHAR(30)    PATH 'name',
      RUE VARCHAR(25) PATH 'addr/street',
      TEL VARCHAR(20) PATH '@tel'
 ) AS X

Fonction Table qui traite sous forme colonnée (relationnelle), les éléments d'un flux JSON. La source peut être :

• une colonne de type VARCHAR
• le résultat de GET_CLOB_FROM_FILE
• le résultat de HTTPGETCLOB

SELECT X.NOM, X.RUE, X.TEL FROM
 JSON_TABLE (<source> , $.client[*]
   COLUMNS(
      NOM CHAR(30)    PATH 'lax $.name',
      RUE VARCHAR(25) PATH 'lax $.addr.street',
      TEL VARCHAR(20) PATH 'strict $.tel')
 ) AS X

retourne une valeur scalaire (unitaire)

retourne une chaîne au format JSON

Produit un tableau de valeur (rappel entre [ et ] )

produit un objet JSON,
   chaque élément a un nom et une valeur

produit un tableau de valeurs par groupe (GROUP BY)

produit une série d'objets JSON (clé/valeur) par groupe (GROUP BY)

EXEC SQL     values cast(GET_CLOB_FROM_FILE('/monfichier.txt') as varchar(20000) )            into :variable;

passe une requête HTTP retourne l'entête HTTP (header) retournée par le serveur

passe une requête HTTP avec la méthode indiquée et retourne la réponse sous forme de BLOB

passe une requête HTTP avec la méthode indiquée et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode GET et retourne la réponse sous forme de BLOB

passe une requête HTTP avec la méthode GET et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode POST et retourne la réponse sous forme de BLOB

passe une requête HTTP avec la méthode POST et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode PUTet retourne la réponse sous forme de BLOB

passe une requête HTTP avec la méthode PUTet retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode DELETE et retourne la réponse sous forme de BLOB

passe une requête HTTP avec la méthode DELETE et retourne la réponse sous forme de CLOB

retourne la chaîne au format compatible avec une URL
     systools.urlencode('info@volubis.fr', '') -> info%40volubis.fr

retourne la chaîne en clair

retourne la chaîne au format base64 (utilisé dans les entêtes HTTP pour l'authentification)

retourne la chaîne en clair

-- exemple, retourne la liste des taux de change avec l'EURO depuis la banque centrale Européenne :
SELECT * FROM
 XMLTABLE('$result/*:Envelope/*:Cube/*:Cube/*:Cube' 
    PASSING XMLPARSE( DOCUMENT 
             SYSTOOLS.HTTPGETCLOB('https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml','')
    ) as "result"
 COLUMNS 
  monnaie CHAR(3) PATH '@currency',
  taux DEC(11, 4) PATH '@rate'
 ) AS LESTAUX;

--Toutes ces fonctions sont aussi livrées en mode "verbeux" 
  (fonctions TABLE retournant le contenu et l'entête HTTP)

select * from TABLE (
 systools.httpgetclobVERBOSE('http://www.volubis.fr' , '')
) as T  
+-------------------------+----------------------------------+
+    RESPONSEMSG          +     RESPONSEHTTPHEADER           +
+ <! DOCTYPE html PUBLIC..+ <httpheader responseCode="200"   +
+-------------------------+----------------------------------+
        

passe une requête HTTP avec la méthode GET et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode POST et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode PUT et retourne la réponse sous forme de CLOB

passe une requête HTTP avec la méthode DELETE et retourne la réponse sous forme de CLOB

retourne la chaîne au format compatible avec une URL (encodage UTF-8)
     qsys2.url_encode('info@volubis.fr') -> info%40volubis.fr

retourne la chaîne en clair (encodage UTF-8)

retourne la chaîne au format base64 (utilisé dans les entêtes HTTP pour l'authentification)

retourne la chaîne ou valeur binaire décodée

--Toutes ces fonctions sont aussi livrées en mode "verbeux" 
  (fonctions TABLE retournant le contenu et l'entête HTTP)

select * from TABLE (
 qsys2.http_get_VERBOSE('http://www.volubis.fr' , '')
) as T  
+-------------------------+----------------------------------+
+    RESPONSEMSG          +     RESPONSEHTTPHEADER           +
+ <! DOCTYPE html PUBLIC..+ {"HTTP_STATUS_CODE":200,"Date":"Fri, 15 Oct ...   +
+-------------------------+----------------------------------+
        

Compte le nombre de fois ou une expression régulière est vraie

Where REGEXP_COUNT(pr_nom , 'ch[aâ]teau') > 1 // présent au moins 2 fois

retourne la position de la chaîne où l'expression régulière est vraie

Where REGEXP_INSTR(pr_nom , 'ch[aâ]teau') > 5 // Chateau après position 5

retourne la chaîne qui fait que l'expression régulière est vraie

VALUES REGEXP_SUBSTR(msg , '(\w+\.)+((org)|(com)|(gouv)|(fr))') 
  into :machaine // une URL valide

remplace la chaîne qui fait que l'expression régulière est vraie

REGEXP_REPLACE(pr_nom , 'ch[aâ]teau' , 'bodega') 

• QUERYLANGUAGE= fr_FR ou en_US
• RESULTLIMIT=n, pour limiter la réponse aux n premières valeurs.
• SYNONYM = OFF ou ON, pour utiliser ou non les synonymes.