db2 sql

Esempi di DB2 SQL per AS/400

db2 sqlIn questo articolo elenco esempi di DB2 SQL per AS400 (anche conosciuto come IBMiiSeries, ...).

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 🙂

(Letto 29.685 volte di cui 3 negli ultimi 30gg)
twitterlinkedinmailby feather

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

  1. 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???

    1. 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

  2. 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

    1. 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 😉

  3. 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

  4. 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

    1. Non ricordo cosa supporta la 5.2. Prova a togliere inner lasciando solo join. Dovrebbe considerarla inner implicitamente.

      1. 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.

        1. è 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?

          1. 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. 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 😉

    1. cosa intendi con “concatenare una clausola where” ?
      fare la where su colonne concatenate? inserire una sub-query all’interno della where?

      Fammi un esempio

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

    1. 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

        1. 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

      1. 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

        1. 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! 😀

  6. 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

    1. 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).

      1. Ho notato che la funzione “WEEk” considera la settimana da domenica a sabato. Non si può modificare Lunedi-Domenica ???

        1. Devi usare la funzione WEEK_ISO. La sinstassi è uguale a WEEK ma considera Lunedì come primo giorno della settimana

          1. 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. 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. 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. dico che l’allievo ha superato il maestro, complimenti!!!!
            Veramente ottima 😀

  7. 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.

    1. 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.

      1. 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.

      2. Oggi ho provato il tuo suggerimento e sono rimasto sorpreso del numero di tabelle che va aprire quando chiedo dell’informazioni tramite il’5250.

        1. 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.

          1. 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.

  8. 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

    1. 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.

      1. 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 ???

      1. 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 ???

        1. è 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).

  9. 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

  10. 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

  11. 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

  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?

  13. 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.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *