Di seguito alcuni comodi esempi di alcune comode e particolari istruzioni SQL su DB2 per IBMi.
Ordinare per numero colonna
select Campo1,(Campo2 + Campo3) from tabName order by 2, 1
in quest’esempio l’ordinamento avviene in base al numero colonna e non al suo nome. Può essere comodo soprattutto per colonne calcolate.
Numero relativo di record (RRN)
SELECT rrn(N), n.* FROM TabName AS N WHERE rrn(N) BETWEEN 1 AND 100 order by rrn(N)
in quest’esempio viene reperito il numero relativo di record con la funzione rrn, quindi viene utilizzato nella where per leggere solo i primi 100 record ed infine lo si utilizza per l’ordinamento.
Selezione solo dei primi N record
SELECT * FROM TabName ORDER BY Campo3, Campo5 FETCH FIRST 10 ROWS ONLY
in questo caso vengono selezionati solo i primi 10 record.
Creazione tabella temporanea in QTEMP
DECLARE GLOBAL TEMPORARY TABLE TmpTab AS (SELECT * FROM TabName WHERE SUBSTR(Campo7, 4, 1) = 'X') WITH DATA
in questo modo viene creata una tabella temporanea nella libreria QTEMP del lavoro con la struttura risultante dall’istruzione SELECT.
Specificando WITH DATA la nuova tabella verrà riempita con i dati selezionati. Oltre a WITH DATA è possibile specificare:
WITH NO DATA: crea la tabella vuota
WITH REPLACE: se esiste sostituisce la tabella (senza necessità di DROP) e la riempie con i dati della selezione.
ATTENZIONE: se si creano problemi nella creazione delle temporary table con segnalazione di oggetto danneggiato, bisogna ricreare il ricevitore di giornale con il seguente comando:
CHGJRN JRN(QRECOVERY/QSQTTJRN) JRNRCV(*GEN)
Inserimento di più record con un unica istruzione
INSERT INTO TabName VALUES (123,'valore1'), (112, 'valore2')
in questo caso vengono inseriti due record contemporaneamente.
Crea tabella con nome formato record diverso dal nome del file
CREATE TABLE TabName (name char (20), number dec (5)) RCDFMT RcdName
può essere comodo se la tabella viene utilizzata in un programma RPG che non accetta tabella che hanno il nome formato record uguale alla tabella stessa … anche se la cosa si può aggirare con un semplice RENAME ;-)
Confronto tra più colonne
SELECT * FROM TabName WHERE (Campo1, Campo5) = ('SERGIO', 20120815)
quest’istruzione è più curiosa che utile nel senso che la condizione WHERE può essere scritta anche nel seguente, più classico, modo:
… WHERE Campo1='SERGIO' AND Campo5=20120815
Eliminare blanks in eccesso in una stringa utilizzando la funzione REPLACE
UPDATE TabName SET name = REPLACE(REPLACE(REPLACE(name,' ','<>'),'><',''),'<>',' ')
con quest’istruzione vengono eliminati i blanks non significativi mantenendone solo uno.
Ad esempio, se la stringa contiene “Gino Carrasco ” diventa “Gino Carrasco”
Operazioni conseguenti ad un altra operazione di I/O
EXEC SQL DELETE FROM TabName WHERE PrcFlag = 'X'; IF SQLER3 > *zero;
effettua il controllo di SQLER3 or SQLERR(3) per vedere gli effetti della precedente operazione di I/O (fetch, insert, update, delete).
Leggere dati in file multimembro
CREATE ALIAS qtemp.File for Lib.TabName(Membro); SELECT * FROM qtemp.File
Elimina righe doppie di un file
DELETE FROM TabName b WHERE RRN(b) IN (SELECT MAX(RRN(a)) FROM TabName a GROUP BY Campo1, Campo2 HAVING COUNT(*)>1)
con quest’istruzione vengono eliminate le righe doppie della tabella TabName in base al criterio di raggruppamento specificato in GROUP BY.
Aggiornare record senza specificare i nomi delle colonne
UPDATE TabName SET ROW = ('A', 7, 8, 'B', 1) WHERE Campo5 = 'EUSTACHIO'
Totale e sub-totali multi-livello
SELECT JCDAGD,JCDCFF,JCSPES,JCDA3F, COUNT(*), SUM(JQTFTF ) FROM mylib.mytab WHERE JDTFTF between 20120501 and 20120531 GROUP BY JCDAGD,JCDCFF,JCSPES,JCDA3F UNION ALL SELECT JCDAGD,JCDCFF,JCSPES,' TOTALE CLI SPED ' , COUNT(*), SUM(JQTFTF ) FROM mylib.mytab where JDTFTF between 20120501 and 20120531 GROUP BY JCDAGD,JCDCFF,JCSPES UNION ALL SELECT JCDAGD,JCDCFF,' TOTALE CLI FATT ',' ' , COUNT(*), SUM(JQTFTF ) FROM mylib.mytab where JDTFTF between 20120501 and 20120531 GROUP BY JCDAGD,JCDCFF UNION ALL SELECT JCDAGD,' TOTALE AGENTE ',' ',' ', COUNT(*), SUM(JQTFTF ) FROM mylib.mytab where JDTFTF between 20120501 and 20120531 GROUP BY JCDAGD UNION ALL SELECT 'Š TOTALE',' ',' ',' ',COUNT(*), SUM(JQTFTF) FROM mylib.mytab where JDTFTF between 20120501 and 20120531 order by 1,2,3,4
ATTENZIONE: USARE (ALT 254)—> Š per spostare in fondo i record di Totali ed inserirlo nel primo byte della descrizione ‘ ” ” <- TOTALE …..’