AS400

Esempi di DB2 SQL per AS/400

Paolo Finardi 2 Agosto 2011

In questo articolo elenco esempi di DB2 SQL per AS400 (anche conosciuto come IBMi, iSeries, ...).

L'obbiettivo è avere un riferimento unico per l'utilizzo di DB2 SQL aggiornando l'articolo ogni volta che ho esempi da documentare.

Di seguito aggiungo i link ad altri articoli relativi al DB2 SQL su IBMi che trovo utili ed interessanti:

Gestione di campi (colonne) ad incremento automatico nelle tabelle del DB2 for IBMi

Oggetti SEQUENCE in DB2 SQL per AS400

Funzione DB2 SQL AS400 per calcolare anno-settimana di una data

Prestazioni di istruzioni DB2 SQL AS400 complesse utilizzando le Common Table Expression

Clausola HAVING

Permette di fare test sul risultato di una funzione (sommatoria, conteggio, …).

Esempio, estrarre dalla tabella dei movimenti contabili solo i codici dei clienti che quest'anno hanno movimenti in DARE superiore a 50.000 euro:

SELECT clifo, sum(impeu) FROM moaz200f
WHERE fclfo ='C' and damov>20110100 and darav='D'
GROUP BY clifo
HAVING sum(impeu)>50000
ORDER BY clifo

ATTENZIONE: HAVING va indicato dopo GROUP BY e prima di ORDER BY.

… se volessi ordinare il risultato in modo decrescente con DB2 SQL su AS400 sarebbe sufficiente assegnare un nome alias alla sommatoria e utilizzarlo nella clausola ORDER BY:

SELECT clifo, sum(impeu) dare FROM moaz200f
WHERE fclfo ='C' and damov>20110100 and darav='D'
GROUP BY clifo
HAVING sum(impeu)>50000
ORDER BY dare desc, clifo

ATTENZIONE: il nome alias “dare” assegnato alla sommatoria non può essere usato nella clausola HAVING

Clausola FETCH

Se volessi solo le prime 10 righe della query precedente si può utilizzare la clausola “FETCH FIRST 10 ROWS ONLY”. Esempio:

SELECT clifo, sum(impeu) dare FROM moaz200f
WHERE fclfo ='C' and damov>20110100 and darav='D'
GROUP BY clifo
HAVING sum(impeu)>50000
ORDER BY dare desc, clifo
FETCH FIRST 10 ROWS ONLY

Clausola JOIN: unione tabelle

Permette di unire 2 o più tabelle.

Ad esempio, se nella query precedente voglio estrarre anche la ragione sociale del cliente (oltre al codice) che si trova in un altra tabella, l'istruzione sarà la seguente:

SELECT a.clifo, b.rascl, sum(a.impeu) dare
FROM moaz200f a
JOIN ancl201l b ON a.clifo=b.cdcli
WHERE a.FCLFO ='C' and a.damov>20110100 and a.darav='D'
GROUP BY a.clifo, b.rascl
HAVING sum(a.impeu)>50000
ORDER BY dare desc, a.clifo

Questa clausola ha anche le varianti LEFT JOIN e RIGHT JOIN i cui significati e utilizzi sono abbastanza chiari.

In pratica, mentre la JOIN normale è una classica INNER JOIN, ovvero estrae solo le righe che hanno corrispondenza in tutte le tabelle coinvolte, la LEFT JOIN è una OUTER JOIN, ovvero estrae anche le righe della tabella di sinistra che non hanno corrispondenza nell'altra tabella (la RIGHT JOIN fa ovviamente il contrario). Esempio:

SELECT a.clifo, b.rascl, sum(a.impeu) dare
FROM moaz200f a
LEFT JOIN ancl201l b ON a.clifo=b.cdcli
WHERE a.FCLFO ='C' and a.damov>20110100 and a.darav='D'
GROUP BY a.clifo, b.rascl
HAVING sum(a.impeu)>50000
ORDER BY dare desc, a.clifo

in questo caso vengono estratte anche eventuali righe della tabella moaz200f in cui il contenuto del campo codice cliente (a.clifo) non trova corrispondenza nella colonna b.cdcli della tabella ancl201l. Ovviamente questo caso specifico ha poco senso, ma in altri casi può essere utile.

Concatenare campi

Se desidero ottenere un solo campo da due o più campi alfanumerici posso concatenarli usando il doppio pipe: ||.

Considerando sempre il solito esempio potrei volere, oltre alla ragione sociale del cliente, anche la località unita alla provincia in un unico campo:

SELECT a.clifo, b.rascl, TRIM(b.loccl) || ' - ' || b.procl Localita, sum(a.impeu) dare
FROM moaz200f a
JOIN ancl201l b ON a.clifo=b.cdcli
WHERE a.FCLFO ='C' and a.damov>20110100 and a.darav='D'
GROUP BY a.clifo, b.rascl, b.loccl, b.procl
HAVING sum(a.impeu)>50000
ORDER BY dare desc, a.clifo

in questo caso utilizzo anche la funzione TRIM sul campo LOCCL per eliminare gli spazi non significativi.

LIKE con valore esadecimale

A volte può essere necessario fare una query su una colonna per che contenga un determinato valore esadecimale (esempio il line feed "25"). Esempio:

SELECT *
FROM mia_tabella
WHERE mia_colonna like '%'||x'25'||'%'

in sostanza bisogna concatenare i carattere jolly (%) alla costante esadecimale.

Creazione nuova tabella con risultato nella query

A volte può essere utile creare una nuova tabella con la struttura ed il contenuto del risultato di una query ad esempio per poi esportare il file ottenuto in un foglio di calcolo ed inviarlo per e-mail.

Per farlo devo eseguire un istruzione simile alla seguente:

CREATE TABLE mylib/sqltest AS (
SELECT a.clifo, b.rascl, sum(a.impeu) dare
FROM moaz200f a
JOIN ancl201l b ON a.clifo=b.cdcli
WHERE a.FCLFO ='C' and a.damov>20110100 and a.darav='D'
GROUP BY a.clifo, b.rascl
HAVING sum(a.impeu)>50000
ORDER BY dare desc, a.clifo
) with data

in questo modo l'output della query viene scritto in nuova nuova tabella: SQLTEST nella libreria MYLIB.

ATTENZIONE: se si vuole creare solo la definizione della tabella senza i dati usare with no data

Nell'esempio precedente la colonna risultato della funzione SUM avrà una dimensione molto grossa. Se volessi impostare una dimensione diversa posso utilizzare la funzione DEC(, , ). Esempio:

CREATE TABLE mylib/sqltest AS (
SELECT a.clifo, b.rascl, DEC(sum(a.impeu), 12, 2) dare
FROM moaz200f a
JOIN ancl201l b ON a.clifo=b.cdcli
WHERE a.FCLFO ='C' and a.damov>20110100 and a.darav='D'
GROUP BY a.clifo, b.rascl
HAVING sum(a.impeu)>50000
ORDER BY dare desc, a.clifo
) with data

in questo caso, il campo della nuova tabella che conterrà il risultato della funzione SUM sarà di 12 numeri di cui 2 decimali.

MAIUSCOLE E MINUSCOLE

Per fare test su un campo alfanumerico che può contenere caratteri sia MAIUSCOLI che minuscoli si può utilizzare la funzione UPPER per trasformare tutto in MAUISCOLO (oppure LOWER per trasformare tutto in minuscolo).

Ad esempio, se voglio estrarre dalla tabella dei clienti tutti quelli residenti a NAPOLI utilizzerò la seguente istruzione:

select * from ancl200f where upper(LOCCL)='NAPOLI'

in questo modo ottengo il risultato desiderato indipendentemente che la località sia stata inserita con lettere maiuscolo o minuscole. Quindi otterrò quelli che contengono NAPOLI o Napoli o napoli o NaPoLi e via dicendo.

CASE: condizionare il valore di una colonna

Tramite l'istruzione CASE è possibile condizionare il valore di una colonna tra quelle selezionate in base al valore di un determinato campo. Trova molte applicazioni nella clausola SELECT ma ci sono casi in cui può essere comodo usarlo anche in WHERE, ORDER BY e via dicendo.

La sintassi è la seguente:

case
when  then 
when  then 
…
when  then 
else 
end

Esempi di utilizzo di CASE

Esempio 1:

SELECT cdcli, rascl,
CASE WHEN tpiva='E' THEN 'Esente IVA'
WHEN tpiva='S' THEN 'IVA in Sospensione'
ELSE 'IVA Normale'
END AS "Assoggettamento Fiscale"
FROM ancl200f
ORDER BY cdcli

in questo esempio condiziono il valore della terza colonna in base al contenuto del flag TPIVA: se è “E” la colonna assume il valore “Esente IVA”, se è “S” la colonna assume il valore “ IVA in Sospensione” altrimenti assume il valore “IVA Normale”.

Esempio 2:

SELECT cdcli, rascl,
CASE WHEN cdnaz=' ' THEN 'IT - Italia'
ELSE a.cdnaz || '- ' || b.desnaz
END AS "Nazione"
FROM ancl200f a
LEFT JOIN tab_naz b on a.cdnaz=b.codnaz
ORDER BY cdcli

in questo esempio la terza colonna assume un valore fisso se CDNAZ è blanks, altrimenti ricava il valore concatenando il contenuto di due campi.

Esempio 3:

SELECT cdcsb,
CASE WHEN cdnaz=' ' THEN 'IT - Italia'
ELSE a.cdnaz || '- ' || b.desnaz END AS "Nazione",
DEC(IFNULL(SUM(CASE WHEN annnb=2009 THEN vlcnb END), 0), 10, 2) as A_2009,
DEC(IFNULL(SUM(CASE WHEN annnb=2010 THEN vlcnb END), 0), 10, 2) as A_2010,
DEC(IFNULL(SUM(CASE WHEN annnb=2011 THEN vlcnb END), 0), 10, 2) as A_2011
FROM bote261l
WHERE annnb>=2009 and cdagb='852' and (tpdcb='F' or tpdcb='A')
GROUP BY cdcsb
ORDER BY cdcsb

questo esempio mostra il valore venduto di alcuni clienti dal 2009 in poi mettendo il valore di ogni anno in orizzontale su colonne diverse ed è molto interessante per vari aspetti.

Innanzitutto il CASE viene inserito all'interno della funzione SUM, per cui, ad esempio nella seconda colonna, viene fatta la sommatoria di VLCNB solo per i record che hanno ANNNB=2009.

Poi viene utilizzata la funzione IFNULL che permette di assegnare un valore di default nel caso il risultato dell'espressione sia NULLO.

Infine, mostra la potenza della combinazione di queste funzioni dell'SQL per mettere in orizzontale risultati di sommatorie che altrimenti sarebbero state in verticale in quanto l'informazione dell'anno nel file è in un solo campo e non già divisa in più campi.

Esempio 4:

SELECT * FROM bote200f
WHERE dattb>20110000 and cdagb='XYZ'
ORDER BY CASE WHEN dtbob>0 THEN dtbob ELSE dattb END

in questo esempio CASE viene utilizzato nella clausola ORDER BY per modificare l'ordinamento dei record in funzione di valore del campo DTBOB.

Selezione da lista di valori dinamica

Ci sono casi in cui si ha la necessità di selezionare i record di un file in cui il contenuto di un determinato campo faccia parte di una determinata lista dinamica, ovvero che non può essere fissata staticamente all'interno di un'istruzione SQL.

Ad esempio potrei volere l'elenco dei documenti di vendita abbinati ad agenti che hanno un contratto particolare. Siccome la lista di questi agenti può variare in qualunque momento la inserisco in una tabella nella quale ogni record rappresenta un agente che ha questo contratto.

Supponendo che questa tabella si chiami AGE_SPEC l'istruzione che posso utilizzare è la seguente:

SELECT cdagb,dattb,tpdcb,rareb,attnb,cdcfb,vlcnb
FROM bote200f
JOIN age_spec ON CDAGB=CD_AGE
WHERE dattb>20110100 and rgftb<>'N' and atv07=' '
ORDER BY cdagb, dattb, rareb, attnb

in pratica faccio un JOIN dell'archivio da cui estrarre i record con la tabella degli agenti con contratto speciale.

In questo modo però il motore SQL è costretto a caricarsi in memoria un tabella con l'intero JOIN tra le due tabelle per poi fare le selezioni. A volte può offrire prestazioni migliori NON fare il JOIN ed inserire una SELECT nidificata nel WHERE. Esempio:

SELECT cdagb,dattb,tpdcb,rareb,attnb,cdcfb,vlcnb
FROM bote200f a
WHERE dattb>20110100 and rgftb<>'N' and atv07=' ' and
cdagb=(SELECT cd_age FROM age_spec b WHERE b.cd_age=a.cdagb)
ORDER BY cdagb, dattb, rareb, attnb

RUNSQLSTM: eseguire più istruzioni SQL

Per elaborare più istruzioni SQL è possibile servirsi del comando RUNSQLSTM.

E' sufficiente scrivere l'elenco delle istruzioni da eseguire in un membro di un file sorgente e quindi darlo in pasto al comando prima citato. Esempio:

RUNSQLSTM SRCFILE(MIA_LIB/QRPGLESRC) SRCMBR(SQL_02) COMMIT(*NONE) ERRLVL(20)

In questo caso vengono eseguite tutte le istruzioni contenute nel membro SQL_02 del file QRPGLESRC della libreria MIA_LIB.

Note importanti:

ogni istruzione (statement) deve terminare con un punto e virgola (;)
I commenti vanno racchiusi tra “/*” e “*/” come nei programmi CL
se tra le istruzioni c'è anche il DROP di una o più tabelle che potrebbero non esistere conviene utilizza l'opzione ERRLVL(20) nel comando RUNSQLSTM, altrimenti l'esecuzione si interrompe perchè non trova la tabella da “droppare”

Di seguito un esempio di file con l'elenco di istruzioni da dare in pasto a RUNSQLSTM:

/* ==============================
Eventuale eliminazione delle tabelle di lavoro
============================== */
drop table paolo/fornitori;
drop table paolo/TB_PAG;

/* ==============================
creazione tabella pagamenti
============================== */
create table paolo/TB_PAG
(cdpag char(3) not null with default,
depag char(35) not null with default);

insert into paolo/TB_PAG
select
substr(xcdel, 2, 3),
substr(xdtab, 1, 35)
from anta201l
where
XCDTB = 'PAG' and ATA32=' '
order by XCDTB, XCDEL;

/* ==============================
Creazione tabella fornitori.
============================== */
create table paolo/fornitori
(cdfor char(6) not null with default,
rasfo char(35) not null with default,
cdnaz char(3) not null with default,
copag char(40) not null with default);
insert into paolo/fornitori
select
cdfor as "Codice",
rasfo as "Ragione Sociale",
(case when cdnaz=' ' then 'IT ' else CDNAZ end) as "Nazione",
(case when copag=' ' then '= = =' else copag||'- '||b.depag end) as "Pagamento"
from anfo200f a
left join paolo/TB_PAG b on a.copag=b.CDPAG
where
ata19 = ' '
order by rasfo

Join per escludere righe presenti in altra tabella

Supponendo di avere le seguenti due tabelle

Utenti
Id Nome
1 paolo
2 giacomo/span>
3 laura
4 marina

Sport
Id_Utente Sport_praticato
1 calcio
3 tennis

se volessi estrarre solo le righe della prima tabella (Utenti) che non hanno un legame per Id nella seconda tabella (Sport), ovvero

Id nome
2 giacomo
4 marina

potrei agire in due modi diversi.

Soluzione 1:

select *
from Utenti
where Id not in (select Id from Sport)

Soluzione 2:

select a.*
from Utenti a
left join Sport b on a.Id=b.Id
where b.Id is null

in pratica verifico una colonna della seconda tabella sia NULL.
Questa seconda soluzione è più comoda quando le colonne da legare sono più di una.

Numerare le righe risultato di un istruzione SQL

A volte può servire numerare le righe risultato di una SQL.

Per farlo si può usare la funzione ROW_NUMBER() OVER().

Ad esempio:

select 
row_number() over(order by Articolo, DataMovimento),
a.*
from MovimMagazzino a
where Articolo='XYZ'
order by Articolo, DataMovimento

Generare N righe per ogni riga della tabella

Può capitare che vi venga richiesta la generazione un certo numero di righe diverso per ogni riga letta dalla tabella in base al valore di una colonna presente sulla tabella (a me è capitato, altrimenti non mi sarebbe venuto in mente 😀 ).

Esempio avendo la seguente tabella che chiamiamo Clienti:

Cod.Cliente   Rag.Sociale     Numero Brand
  000587      Gino                3
  000879      Pino                2
  001235      Tino                6
  ......      ...........       ...

si vorrebbe ottenere un result set di questo tipo:

Cod.Cliente   Rag.Sociale       Brand
  000587      Gino                1
  000587      Gino                2
  000587      Gino                3
  000879      Pino                1
  000879      Pino                2
  001235      Tino                1
  001235      Tino                2
  001235      Tino                3
  001235      Tino                4
  001235      Tino                5
  001235      Tino                6
  ......   ...........           ...

per ottenerlo è sufficiente la seguente SQL:

with contarighe(nr_riga) as (
    select 1 from SYSIBM.SYSDUMMY1    
    union all
    select nr_riga + 1 from contarighe where nr_riga < (select max(nr_brand) from Clienti)
)
select 
a.Cod_Cliente, 
a.Rag_Sociale,
x.Nr_Riga
from Clienti a 
join contarighe x on x.nr_riga<=a.nr_brand where a.nr_brand>0
order by a.Cod_Cliente

Convertire la data corrente in Integer

Per convertire la current date (o una qualunque colonna data) in intero è sufficiente utilizzare le funzioni YEAR, MONTH e DAY:

Ad esempio:

select 
year(current date) * 10000 + month(current date) * 100 + day(current date)
from sysibm/sysdummy1

o in alternativa

select 
int(replace(char(current date, ISO), '-', ''))
from sysibm/sysdummy1

SQL per arrotondamento per difetto o eccesso

Per arrotondare un numero con SQL si possono utilizzare le funzioni ROUND, CEILING e FLOOR.

ROUND arrotonda alla cifra desiderata per eccesso o per difetto in base al valore della cifra successiva alla cifra a cui arrotondare.

Ad esempio:

select nr_fattura, data_fattura, round(importo, 2)
from fatture

in questo esempio importo viene arrotondato alla SECONDA cifra decimale.

E' anche possibile arrotondare alle cifre a sinistra dell'unità indicando un numero negativo come secondo parametro.

Un altro esempio che rende meglio la logica di arrotondamento della funzione ROUND è il seguente:

select 
ROUND(683.638, 2),
ROUND(683.638, 1),
ROUND(683.638, 0),
ROUND(683.638, -1),
ROUND(683.638, -2),
ROUND(683.638, -3),
ROUND(683.638, -4)
from sysibm.sysdummy1

Il risultato che si ottiene è:

683,640 - 683,600 - 684,000 - 680,000 - 700,000 - 1000,000 - 0000,000

Se si vuole forzare l'arrotondamento per eccesso bisogna utilizzare la funzione CEILING che arrotonda sempre all'unità in eccesso. Esempio:

select 
CEILING(111.35),
CEILING(111.99)
from sysibm.sysdummy1

Il risultato sarà sempre 112.

Viceversa, se si desidera sempre arrotondare per difetto bisogna utilizzare la funzione FLOOR:

select 
FLOOR(111.35),
FLOOR(111.99)
from sysibm.sysdummy1

Il risultato sarà sempre 111.

A questo punto non mi vengono in mente altri esempi particolari. Se ne avete qualcuno da aggiungere inseritelo nei commenti che può essere utile a tutti 🙂

Potrebbero interessarti anche i seguenti articoli:

Disabilitazione automatica utenti inattivi su AS400

Disabilitare menu richiesta sistema ad un utente su AS400

Un AS400 per domare tutti i database

(Letto 29.711 volte di cui 9 negli ultimi 30gg)

58 thoughts on “Esempi di DB2 SQL per AS/400”

maurizio ha detto:

19 Luglio 2012 alle 11:37

Complimenti !

Rispondi
1. Paolo Finardi ha detto:
  
  19 Luglio 2012 alle 13:35
  
  grazie.
  
  Rispondi
benvenuto ha detto:

26 Settembre 2012 alle 13:41

Grazie per il riepilogo: anche se sono abbastanza digiuno di sql, ho creato la tabella che mi serviva!

Rispondi
1. Paolo Finardi ha detto:
  
  27 Settembre 2012 alle 06:07
  
  … si comincia così e poi … 😉
  
  consiglio spassionato, approfondisco SQL perchè è veramente potente!!!!
  
  Rispondi
Domenico ha detto:

17 Ottobre 2012 alle 16:29

come posso effettuare più join nella stessa query esempio:
Select a.campo, b.campo1, c.campo2 from tab1 as a inner join tab2 as b on a.campo = b.campo inner join tab3 as c on a.campo = c.campo

Dove Sbaglio???

Rispondi
1. Paolo Finardi ha detto:
  
  17 Ottobre 2012 alle 17:31
  
  Select a.campo, b.campo1, c.campo2
  from tab1 as a
  inner join tab2 as b on a.campo = b.campo
  inner join tab3 as c on a.campo = c.campo
  
  la tua query è corretta. Ovviamente, avendo legato tutto con “inner join” verranno mostrati solo i record che hanno corrispondenza in tutt’e tre le tabelle. Se invece vuoi tutti i record di tab1 con gli eventuali record di tab2 e tab3 se esistono devi usare “left join”.
  
  Spero di esserti stato d’aiuto
  
  Rispondi
maurizio ha detto:

17 Ottobre 2012 alle 17:36

Salve, credo che sia solo una distrazione di “digitazione” nome campo. Nell’istruzione originale del sig. Domenico , credo che sia sufficiente correggere l’tultima istruzione, da “as c on a.campo = c.campo” a “as c on a.campo = c.campo2”. Saluti mg

Rispondi
1. Paolo Finardi ha detto:
  
  17 Ottobre 2012 alle 17:45
  
  non penso sia una distrazione perchè avrebbe poco senso mostrare 3 colonne con la stessa informazione. Immagino che c.campo2 sia un informazione diversa da a.campo e che quindi il campo di unione di Domenico sia effettivamente c.campo … però sono solo supposizioni 😉
  
  Rispondi
maurizio ha detto:

17 Ottobre 2012 alle 18:01

Si , in effetti vista in quest’ottica è giusta la sua risposta. Ho frainteso la domanda, pensavo che il problema era la mancata elaborazione dell’istruzione in quanto tale (come se si trattasse di un errore di sintassi) e non di come ottenere il risultato finale. Saluti mg

Rispondi
domenico ha detto:

17 Ottobre 2012 alle 18:16

quando eseguo la query nell’editor sql di iNavigator (as400) questo mi ritorna l’errore evidenziando inner.
Quini non riesco ad inserire più di due tabelle, volevo sapere se è una limitazione della versione 5.2 oppure devo guardare altro
Grazie

Rispondi
1. Paolo Finardi ha detto:
  
  17 Ottobre 2012 alle 19:31
  
  Non ricordo cosa supporta la 5.2. Prova a togliere inner lasciando solo join. Dovrebbe considerarla inner implicitamente.
  
  Rispondi
  1. Domenico ha detto:
    
    18 Ottobre 2012 alle 11:14
    
    Già fatto mi restituisce questo errore (naturalmente se faccio un singolo join non ci sono problemi)
    
    Stato SQL: 42601
    Codice fornitore: -199
    Messaggio: [SQL0199] Parola chiave JOIN non prevista. Token validi: FOR WITH FETCH ORDER UNION OPTIMIZE. Causa . . . : Non era prevista la parola chiave JOIN. E’ stato rilevato un errore di sintassi nella parola chiave JOIN. L’elenco parziale dei token validi è FOR WITH FETCH ORDER UNION OPTIMIZE. Questo elenco presuppone che l’istruzione sia corretta fino alla parola chiave imprevista. E’ possibile che l’errore sia precedente nell’istruzione, ma la sintassi dell’istruzione sembrava valida fino a questo punto.Correzione . . . : Verificare l’istruzione SQL nell’area della parola chiave specificata. E’ possibile che manchino i due punti o il delimitatore SQL. SQL richiede parole riservate da delimitare quando vengono utilizzate come nome. Correggere l’istruzione SQL e tentare nuovamente la richiesta.
    
    Processo terminato perché l’istruzione evidenziata non è stata completata con esito positivo.
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      18 Ottobre 2012 alle 13:02
      
      è come se non vedesse chiusa la prima join.
      E’ veramente strano.
      
      Ho provato a eseguire un istruzione simile in V5R1 e funziona tranquillamente, quindi o è un errore di sintassi (io l’istruzione globale non la conosco) oppure hai un problema sul DB, mancanza di PTF o altro.
      
      Hai scritto che la stai eseguendo da iNavigator, so che sembra assurdo ma a volte … hai provato ad eseguirla da STRSQL in 5250?
      
      Rispondi
      1. Domenico ha detto:
        
        18 Ottobre 2012 alle 13:34
        
        Da “System iNavigator/DataBase/Esegui uno script SQL”, l’unica cosa è che utilizzo il client access 6.1 perchè è la prima cersione dove è possibile installare il componente di accesso ai .net su Sistema operativo windows a 64 bit.
        
        Pensavo che il problema fosse di versione ma ho provato anche con la 5.2 e mi ritorna lo stesso errore.
        
        Non so come uscirne, c’è un modo per farsi aiutare direttamente da quelli di IBM, considerando che paghiamo la manutenzione sia hardware che software???
        
        Grazie
      2. Paolo Finardi ha detto:
        
        18 Ottobre 2012 alle 14:07
        
        molto strano.
        Comunque se hai la manutenzione software puoi assolutamente chiamare IBM al numero verde. Apri la chiamata e poi ti richiamano loro anche perchè se il problema è nelle PTF loro riescono a farti fare i controlli corretti.
        
        Good luck 😉
Claudio ha detto:

1 Novembre 2012 alle 19:50

Come si fa a concatenare una clausola where ???

Rispondi
1. Paolo Finardi ha detto:
  
  2 Novembre 2012 alle 09:01
  
  cosa intendi con “concatenare una clausola where” ?
  fare la where su colonne concatenate? inserire una sub-query all’interno della where?
  
  Fammi un esempio
  
  Rispondi
Claudio ha detto:

2 Novembre 2012 alle 23:17

Concatenare più colonne nella clausola where. ad es.
select colonna1, colonna2 from miatabella where (colonna1 & colonna2=valore da ricercare)

Rispondi
1. Paolo Finardi ha detto:
  
  3 Novembre 2012 alle 07:59
  
  devi fare così
  
  select colonna1, colonna2 from miatabella where colonna1 concat colonna2 = ‘valore da ricercare’
  oppure
  select colonna1, colonna2 from miatabella where concat(colonna1,colonna2) = ‘valore da ricercare’
  
  Se preferisci, nella prima istruzione, puoi sostituire la funzione concat con il doppio punto esclamativo “!!” però personalmente preferisco il concat perchè più leggibile.
  
  ciao
  
  Rispondi
  1. Claudio ha detto:
    
    3 Novembre 2012 alle 18:09
    
    Grazie mille mi sei stato di grande aiuto !!!
    
    Rispondi
    1. Claudio ha detto:
      
      4 Novembre 2012 alle 14:30
      
      Ho provato la funzione “concat” però la query mi restituisce l’errore
      “LA QUERY SQL SUPERA IL LIMITE DI TEMPO O IL LIMITE DI MEMEORIA SPECIFICATO”
      
      TI SPIEGO IN DETTAGLIO CHE COSA HO BISOGNO
      
      LA MIA TABELLA CONTIENE:
      CAMPO1=ANNO
      CAMPO2=MESE
      CAMPO3=GIORNO
      ECC…….
      
      HO BISOGNO DI ESTRAPOLARE I DATI DI UN DETERMINATO PERIODO DI TEMPO.
      IO HO FATTO IN QUESTO MODO:
      SELECT * MIATABELLA WHERE (CAMPO1 CONCAT CAMPO2 CONCAT CAMPO3)>20121010 AND (CAMPO1 CONCAT CAMPO2 CONCAT CAMPO3)<20121031
      
      Rispondi
  2. Claudio ha detto:
    
    4 Novembre 2012 alle 20:56
    
    Ho provato la funzione “concat” però la query mi restituisce l’errore
    “LA QUERY SQL SUPERA IL LIMITE DI TEMPO O IL LIMITE DI MEMEORIA SPECIFICATO”
    
    TI SPIEGO IN DETTAGLIO CHE COSA HO BISOGNO
    
    LA MIA TABELLA CONTIENE:
    CAMPO1=ANNO
    CAMPO2=MESE
    CAMPO3=GIORNO
    ECC…….
    
    HO BISOGNO DI ESTRAPOLARE I DATI DI UN DETERMINATO PERIODO DI TEMPO.
    IO HO FATTO IN QUESTO MODO:
    SELECT * MIATABELLA WHERE (CAMPO1 CONCAT CAMPO2 CONCAT CAMPO3)>20121010 AND (CAMPO1 CONCAT CAMPO2 CONCAT CAMPO3)<20121031
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      5 Novembre 2012 alle 08:45
      
      l’istruzione dipende dal tipo dei campi (colonne): sono DECIMAL e CHAR?
      
      Caso 1: campi DECIMAL ==> non puoi usare il CONCAT in quel modo perchè ti darebbe errore perchè puoi usarlo solo con campi CHAR, quindi
      SELECT * MIATABELLA WHERE ((CAMPO1*10000) + (CAMPO2*100) + CAMPO3) between 20121010 AND 20121031
      
      Caso 2: campi CHAR
      SELECT * MIATABELLA WHERE (CAMPO1 CONCAT CAMPO2 CONCAT CAMPO3) between ‘20121010’ AND ‘20121031’
      
      a occhio direi che i tuoi campi sono numerici e il “SUPERO TEMPO” è probabilmente dovuto al fatto che la query va in errore
      
      Try! 😀
      
      Rispondi
      1. Claudio ha detto:
        
        6 Novembre 2012 alle 14:01
        
        Ottimo ha funzionato alla perfezione !!! UN GRANDE GRAZIE
Claudio ha detto:

3 Novembre 2012 alle 20:39

Come faccio a fare una funzione di aggregazione settimanale su un campo che contiene le date..
Es. campo A=date, campo B=descrizione articolo, campo C= quantità, campo D=prezzo.

Voglio sapere la quantità totale venduta settimanalmente di un determinato prodotto.

valore che deve restituire la query:
1° settimana quantità totale venduto del prodotto x;
2° settimana quantità totale venduto del prodotto x;
3° settimana quantità totale venduto del prodotto x.
ecc ecc

Rispondi
1. Paolo Finardi ha detto:
  
  4 Novembre 2012 alle 12:11
  
  Interessante!
  
  devi raggruppare usando la funzione WEEK sul campo date (quindi campoA nel tuo esempio).
  Esempio:
  
  SELECT
  campoB, WEEK(campoA), SUM(campoC), SUM(campoD)
  FROM MiaTabella
  GROUP BY campoB, WEEK(campoA)
  
  Devi dare però attenzione al tipo del tuo campo data. Mi spiego meglio, l’esempio che t’ho scritto va bene se il tuo campo data è effettivamente di tipo “DATE”,
  spesso, però, su AS400 le date vengono registrate in campi numerici (decimal (8, 0) ) che non possono utilizzati dalla funzione WEEK.
  In questo caso devi formattare il valore del tuo campo come un vero campo DATE ovvero AAAA-MM-GG.
  Quindi, se il campoA è un campo numerico che contiene una data nel formato AAAAMMGG, l’esempio precedente diventa così:
  
  SELECT
  campoB,
  WEEK( SUBSTR(CHAR(campoA), 1, 4) concat ‘-‘ concat
  SUBSTR(CHAR(campoA), 5, 2) concat ‘-‘ concat
  SUBSTR(CHAR(campoA), 7, 2) ) as Settimana,
  SUM(campoC), SUM(campoD)
  FROM MiaTabella
  GROUP BY campoB, WEEK( SUBSTR(CHAR(campoA), 1, 4) concat ‘-‘ concat
  SUBSTR(CHAR(campoA), 5, 2) concat ‘-‘ concat
  SUBSTR(CHAR(campoA), 7, 2) )
  
  Spero di essere stato chiaro.
  
  Giusto per completezza, oltre alla funzione WEEK c’è anche la funzione WEEK_ISO che funziona nello stesso modo ma differisce da WEEK per il criterio con cui viene conteggiata la prima settimana dell’anno (e di conseguenza le altre).
  
  Rispondi
  1. Claudio ha detto:
    
    7 Novembre 2012 alle 21:38
    
    Ottimo anche questo ha funzionato alla perfezione… Pian piano sto costruendo la mia applicazione…
    
    Rispondi
  2. Claudio ha detto:
    
    9 Novembre 2012 alle 17:58
    
    Ho notato che la funzione “WEEk” considera la settimana da domenica a sabato. Non si può modificare Lunedi-Domenica ???
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      10 Novembre 2012 alle 07:26
      
      Devi usare la funzione WEEK_ISO. La sinstassi è uguale a WEEK ma considera Lunedì come primo giorno della settimana
      
      Rispondi
      1. Claudio ha detto:
        
        18 Novembre 2012 alle 18:06
        
        Se io volessi ottenere in un campo da che giorno a che giorno prendendo in considerazione il risultato di una funzione week_iso. Come andrebbe costruita la query ??
        
        Es. week_iso=1 dal 2/01/2012 al 08/01/2012
        week_iso=2 dal 9/01/2012 al 16/01/2012
        ecc.
      2. Paolo Finardi ha detto:
        
        19 Novembre 2012 alle 22:33
        
        condiderando che WEEK_ISO considera Lunedì come primo giorno della settimana e considera la prima settimana dell’anno quella che contiene il primo giovedì dell’anno …
        
        lascio a te il piacere di trovare la soluzione 😀
        
        ciao
      3. Claudio ha detto:
        
        21 Novembre 2012 alle 15:00
        
        Per trovare la soluzione ho pensato che fosse più idoneo utilizzare la funzione “DAYOFWEEK_ISO”.
        
        select ‘Settimana dal ‘ || CAST(CAST((ANNO || ‘-‘ || MESE || ‘-‘ || GIORNO)AS DATE)-(DAYOFWEEK_ISO(ANNO || ‘-‘ || MESE || ‘-‘ || GIORNO)) DAY + 1 DAY AS CHAR(12)) || ‘ AL ‘ || CAST(CAST((ANNO || ‘-‘ || MESE || ‘-‘ || GIORNO)AS DATE) – (DAYOFWEEK_ISO(ANNO || ‘-‘ || MESE || ‘-‘ || GIORNO)) DAY + 7 DAY AS CHAR(10)) AS SETTIMANA, ECC
        OVVIAMENTE POI CI STA IL GROUP BY CON TUTTA LA PAPPARDELLA DI SOPRA
        
        PROF. CHE NE PENSI ????
      4. Paolo Finardi ha detto:
        
        22 Novembre 2012 alle 07:05
        
        dico che l’allievo ha superato il maestro, complimenti!!!!
        Veramente ottima 😀
    2. Claudio ha detto:
      
      22 Novembre 2012 alle 17:07
      
      Grazie del complimento. Ma il maestro rimane sempre il maestro !!!
      
      Rispondi
Claudio ha detto:

6 Novembre 2012 alle 23:06

Come faccio a sapere in quale tabella dell’as400 risiedono i dati che mi occorrono.

Es. Attraverso un terminale 5250 visualizzo i dati di un articolo, come faccio a sapere in quale tabella estrapola quei dati.

Rispondi
1. Paolo Finardi ha detto:
  
  7 Novembre 2012 alle 08:39
  
  mi sa che tu e l’AS non siete amici da molto tempo 😉
  
  da 5250 fai “richiesta sistema” (da PC fai SHIFT+ESC), appare una linea in fondo allo schermo, scrivi “3” sulla linea e dai “Invio”. Poi scegli l’opzione 14 e dai Invio. A questo punto vedi tutte le tabelle aperte dall’applicazione che stai vedendo sul 5250.
  
  Rispondi
  1. Claudio ha detto:
    
    7 Novembre 2012 alle 21:36
    
    Da poco che sto utilizzando questo gestionale, inoltre girando su internet non ho trovato un granchè come documentazione…
    
    Ti ringrazio vivamente per il tuo supporto mi sei veramente di grande aiuto.
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      8 Novembre 2012 alle 06:59
      
      Di niente
      
      Rispondi
  2. Claudio ha detto:
    
    8 Novembre 2012 alle 17:49
    
    Oggi ho provato il tuo suggerimento e sono rimasto sorpreso del numero di tabelle che va aprire quando chiedo dell’informazioni tramite il’5250.
    
    Rispondi
  3. Claudio ha detto:
    
    9 Novembre 2012 alle 18:29
    
    Come faccio a vedere il contenuto di una tabella oppure ad eseguire una query dal terminale 5250 ???
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      10 Novembre 2012 alle 07:27
      
      da 5250 esegui il comando STRSQL
      a quel punto puoi eseguire tutte le query SQL che vuoi.
      Quando hai finito esci col tasto funzionale F3.
      
      Rispondi
      1. Claudio ha detto:
        
        12 Novembre 2012 alle 20:35
        
        Dove devo scrivere il comando “STRSQL” ???
        Una volta che accedo con la mia username e la password mi ritrovo dentro al menu del punto vendita.
      2. Claudio ha detto:
        
        13 Novembre 2012 alle 22:17
        
        Ok risolto !!! Premendo il pulsante ‘ESC’.
  4. Claudio ha detto:
    
    30 Ottobre 2013 alle 23:29
    
    Tramite “Operations Navigator” ho la possibilità di vedere le librerie aperte ???
    
    Rispondi
Claudio ha detto:

24 Novembre 2012 alle 20:45

In alcune tabelle quando utilizzo la clausola where per filtrare uno di questi tre campi (anno, mese o giorno) ci impiega molto tempo per resituirmi il il risultato di una query a differenza di altri campi in cui il tempo di attesa è minimo.

Per quale motivo ??? Grazie

Rispondi
1. Paolo Finardi ha detto:
  
  25 Novembre 2012 alle 09:30
  
  difficile darti una risposta senza avere altre informazioni, potrebbe essere che quei campi non sono in chiave, quindi le prestazioni ne risentono moltissimo.
  
  I filtri sui campo che sono in chiave sono nettamente più veloci.
  
  Rispondi
  1. Claudio ha detto:
    
    27 Novembre 2012 alle 23:00
    
    Siccome la mia tabella sta in ordine di data e io avrei bisogno di estrapolare solo le ultime righe che riguardano la giornata odierna.
    Ci sta una funzione che mi da la possibilità di estrapolare un certo numero di righe dalla fine???
    
    Sui tuoi esempi ho visto la funzione “FETCH FIRST 10 ROWS ONLY” (indubbiamente rende la mia query più veloce), l’ho utilizzata con un ordine decrescente di data però nel momento che utilizzo ‘order by’ nella mia stringa sql mi restituisce l’errore “LA QUERY SQL SUPERA IL LIMITE DI TEMPO O IL LIMITE DI MEMEORIA SPECIFICATO”.
    
    Mica dipende dal fatto che sto eseguendo la query su una vista logica e non sul file fisico ???
    
    Rispondi
Claudio ha detto:

21 Aprile 2013 alle 20:21

Come faccio a selezionare i campi presenti in una stampa ???

Rispondi
1. Paolo Finardi ha detto:
  
  22 Aprile 2013 alle 06:25
  
  in che senso?
  
  Rispondi
  1. Claudio ha detto:
    
    22 Aprile 2013 alle 18:12
    
    Es. Quando eseguo degli spostamenti della merce nel magazzino dal terminale 5250 mi lancia una stampa in cui è inidicato la posizione d’origine e quella di destinazione e altre informazioni. Se io volessi modificare il layout di stampa, magari aggiungendo altre informazioni come devo fare ???
    
    Rispondi
    1. Paolo Finardi ha detto:
      
      23 Aprile 2013 alle 06:12
      
      è tutta un’altra storia con cui SQL non c’entra niente.
      
      La stampa sarà generata da un programma RPG (in quel caso devi modificare quello) o da un query realizzato con un tool chiamato Query/400. In quel caso devi modificare la definizione del query con il comando WRKQRY (devi però conoscere il nome del query da modificare).
      
      Rispondi
Claudio ha detto:

23 Aprile 2013 alle 18:25

Grazie mille. Ma come faccio a sapere se la stampa è generata dal RPG oppure dal tool Query/400 ???

Rispondi
Luca ha detto:

7 Novembre 2013 alle 11:37

Ciao Paolo,
innanzitutto complimenti per la bellissima guida.
Mi permetto di sottoporti il mio problema facendoti notare che:

1° sono un neofita di SQL
2° le query le sto scrivendo in report-designer di Pentaho BI interrogando un DB2 su As400 (che non gestisco io direttamente)

Ho scritto una query con questi campi:
cliente | importo
———————
per realizzare un grafico a torta

questo il codice:

SELECT DISTINCT
sum(“BI01IMPVAL”) AS IMPORTO,
“BI01CL” AS CLIENTI
FROM
“FLBI01”
WHERE
YEAR(“BI01DT”) = 2013 AND “FLBI01″.”BI01IMPVAL” > 50000
GROUP BY
“FLBI01″.”BI01CL”
ORDER BY
IMPORTO DESC

così facendo mi perdo una fetta importante di tutti quei clienti che hanno un importo inferiore a 50000 che presi singolarmente sembrano ininfluenti, ma tutti insieme generano una bella “fetta” .
mi chiedevo se in SQL è possibile in qualche modo raggrupparli tutti in un’unica riga assieme a tutte le altre già presenti con importi superiori ottenendo una tabella cosi fatta:

cliente | importo
——————————————
aaa | 1000000
bbb | 800000
ccc | 700000
tutti sotto i 50000 | 400000 <— questa è la riga che vorrei aggiungere

Grazie anticipatamente per qualsiasi suggerimento.
Luca

Rispondi

Scusa il ritardo nella risposta ma sono stato incasinato.

Complimenti per l’uso dei prodotti di Pentaho. Sono ottimi!!!

Venendo alla tua SQL può esserti d’aiuto la struttura WITH che ho spiegato in quest’articolo:
http://www.sefi.it/as400/performance-di-db2-sql-complesse-cte

In sostanza prova a vedere se va bene una cosa di questo genere:

with 
Clienti_Top as (
SELECT DISTINCT
sum(BI01IMPVAL) AS IMPORTO,
BI01CL AS CLIENTI
FROM FLBI01
WHERE YEAR(BI01DT) = 2013
GROUP BY FLBI01.BI01CL
HAVING sum(BI01IMPVAL) > 50000
),

Altri_Clienti as (
SELECT DISTINCT
sum(BI01IMPVAL) AS IMPORTO,
'ALTRI_CLIENTI' AS CLIENTI
FROM FLBI01
WHERE YEAR(BI01DT) = 2013 
  AND BI01CL not in (select clienti from Clienti_Top)
)

select 
*
from Clienti_Top

union all

select
* 
from Altri_Clienti

ORDER BY IMPORTO DESC

ciao

Rispondi

Luca ha detto:

18 Novembre 2013 alle 09:23

Grazie Paolo,
ottimo e pulito! Ho raggiunto lo stesso risultato con questa query annidando le select:

SELECT DISTINCT
“SQCL” AS CLIENTI, sum(“SQIMPO”) AS IMPORTO, ‘1’ AS INDICE
FROM
(SELECT DISTINCT “BI01CL” AS SQCL, SUM(“BI01IMPVAL”) AS SQIMPO
FROM “FLBI01”
WHERE YEAR(“FLBI01″.”BI01DT”) = 2013
GROUP BY “BI01CL”) SUBQUERY_2
WHERE
SUBQUERY_2.SQIMPO >= 50000
GROUP BY
“SQCL”
UNION
SELECT
‘Sotto i 50.000’ AS CLIENTI,
sum(“SQIMPO”) AS IMPORTO, ‘2’ AS INDICE
FROM
(SELECT DISTINCT “BI01CL” AS SQCL, SUM(“BI01IMPVAL”) AS SQIMPO
FROM “FLBI01”
WHERE YEAR(“FLBI01″.”BI01DT”) = 2013
GROUP BY “BI01CL”) SUBQUERY_1
WHERE
SUBQUERY_1.SQIMPO < 50000
ORDER BY
INDICE ASC , IMPORTO DESC

Devo dire che la tua soluzione mi piace MOLTO di più!
Ho aggiunto anche un campo INDICE per mettere il valore ALTRI_CLIENTI alla fine dell'elenco.

Grazie mille per il supporto.

Ciao
Luca

Rispondi
1. Paolo Finardi ha detto:
  
  19 Novembre 2013 alle 07:28
  
  Complimenti Luca,
  non male nemmeno la tua soluzione!
  
  ciao
  
  Rispondi
Francesco ha detto:

25 Giugno 2021 alle 11:12

Buongiorno Paolo,
complementi per il Blog, le guide sono veramente ben fatte!
Parto dal fatto che sono un neofita di AS/400 e SQL DB2.
La mia necessità è la seguente :
nella tabella dei movimenti di magazzino (MOVMA) devo sommare un articolo che ha la causale d’entrata (10G) – (meno) un eventuale storno (causale 90G), così’ da avere la quantità giusta delle entrate.
Come posso fare per eseguire tale operazione?

Rispondi
FRANCESCO ha detto:

11 Settembre 2023 alle 11:42

Ciao, è la prima volta che scrivo su questo blog e mi scuso se sto sbagliano qualcosa.
Ho necessità di convertire una data giuliana YYYYDDD espressa come un integer in una data gregoriana YYYYMMDD sempre espressa come integer.
Come posso fare? E’ fattibile?
Grazie mille Francesco.

Rispondi

Flash News

Esempi DB2 SQL per IBMi – parte 2

Opendata e Opensource per statistiche sul COVID-19

Un AS400 per domare tutti i database

Chi utilizza Linux e software OpenSource?

I migliori Cloud Storage per Linux (e non solo)

Esempi di DB2 SQL per AS/400

Clausola HAVING

Clausola FETCH

Clausola JOIN: unione tabelle

Concatenare campi

LIKE con valore esadecimale

Creazione nuova tabella con risultato nella query

MAIUSCOLE E MINUSCOLE

CASE: condizionare il valore di una colonna

Esempi di utilizzo di CASE

Selezione da lista di valori dinamica

RUNSQLSTM: eseguire più istruzioni SQL

Join per escludere righe presenti in altra tabella

Numerare le righe risultato di un istruzione SQL

Generare N righe per ogni riga della tabella

Convertire la data corrente in Integer

SQL per arrotondamento per difetto o eccesso

Potrebbero interessarti anche i seguenti articoli:

58 thoughts on “Esempi di DB2 SQL per AS/400”

Lascia un commento Annulla risposta