View Attach (1) Info

Add new attachment

Only authorized users are allowed to upload new attachments.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
rpgle
 XJAC05_SM.rpgle 112.3 kB 1 14-Apr-2010 14:30 MattiaRocchi SQLRPGLE per Web.up

This page (revision-16) was last changed on 14-Apr-2010 14:30 by MarcoDePasquale  

This page was created on 14-Apr-2010 14:30 by Administrator

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Difference between version and

At line 1 added 6 lines
!! UDF standard
----
! Sviluppo quantità MPPIAN
B£UDF_xx TODO
At line 3 changed one line
Aggregazione BUDGET e confroni con venduto
Aggregazione BUDGET e confroni con venduto a 3 anni.
At line 11 added one line
--> vedi XJAC05_SM allegato (some si può linkare?)
At line 6 changed one line
SELECT CAW2009.MPCD01,CAW2009.MPCD02,
! File interessati
At line 15 added 48 lines
||File||Significato
| MPPIAN0F | Piani per budget qtà/valori
| XCOCLM0F | Storico venduto qtà/valori
! Significato campi
||MPCD01||MPCD02
| Cliente | Articolo
! Istruzione SQL
----
estrae i clienti/articoli presenti fra gli anni 2006 e 2009.
il DB2/400 non supporta full JOIN -> creazione tabelle temporanee
----
commit;
drop table QTEMP.XJAC05_SM;
DECLARE GLOBAL TEMPORARY TABLE
XJAC05_SM ( CLI VARCHAR(06), ART VARCHAR(15) ) ;
insert into QTEMP.XJAC05_SM
select distinct MPCD01 as CLI,MPCD02 as ART from SMEUP_TPER.MPPIAN0F where MPCDPI='BUD09'
and MPCD01='005948' and MPCD02 like '6028005%'
union
(select distinct CLCC06 as CLI,CLCA15 as ART from SMEUP_TPER.XCOCLM0F where (CLANNO BETWEEN 07 and 08) and CLCC06='005948' and CLCA15 like '6028005%')
order by CLI,ART
;
SELECT CLI,ART,
At line 10 changed one line
ifNull((0+VAW2009.MPQ001+VAW2009.MPQ002+VAW2009.MPQ003+VAW2009.MPQ004+VAW2009.MPQ005+VAW2009.MPQ006+VAW2009.MPQ007+VAW2009.MPQ008+VAW2009.MPQ009+VAW2009.MPQ010+VAW2009.MPQ011+VAW2009.MPQ012), 0),ifNull((0+CAO2009.MPQ001+CAO2009.MPQ002+CAO2009.MPQ003+CAO2009.MPQ004+CAO2009.MPQ005+CAO2009.MPQ006+CAO2009.MPQ007+CAO2009.MPQ008+CAO2009.MPQ009+CAO2009.MPQ010+CAO2009.MPQ011+CAO2009.MPQ012), 0),ifNull((0+VAO2009.MPQ001+VAO2009.MPQ002+VAO2009.MPQ003+VAO2009.MPQ004+VAO2009.MPQ005+VAO2009.MPQ006+VAO2009.MPQ007+VAO2009.MPQ008+VAO2009.MPQ009+VAO2009.MPQ010+VAO2009.MPQ011+VAO2009.MPQ012), 0),ifNull((0+COC2008.CLQT01+COC2008.CLQT02+COC2008.CLQT03+COC2008.CLQT04+COC2008.CLQT05+COC2008.CLQT06+COC2008.CLQT07+COC2008.CLQT08+COC2008.CLQT09+COC2008.CLQT10+COC2008.CLQT11+COC2008.CLQT12), 0),ifNull((0+COC2008.CLVA01+COC2008.CLVA02+COC2008.CLVA03+COC2008.CLVA04+COC2008.CLVA05+COC2008.CLVA06+COC2008.CLVA07+COC2008.CLVA08+COC2008.CLVA09+COC2008.CLVA10+COC2008.CLVA11+COC2008.CLVA12), 0),ifNull((0+COC2007.CLQT01+COC2007.CLQT02+COC2007.CLQT03+COC2007.CLQT04+COC2007.CLQT05+COC2007.CLQT06+COC2007.CLQT07+COC2007.CLQT08+COC2007.CLQT09+COC2007.CLQT10+COC2007.CLQT11+COC2007.CLQT12), 0),ifNull((0+COC2007.CLVA01+COC2007.CLVA02+COC2007.CLVA03+COC2007.CLVA04+COC2007.CLVA05+COC2007.CLVA06+COC2007.CLVA07+COC2007.CLVA08+COC2007.CLVA09+COC2007.CLVA10+COC2007.CLVA11+COC2007.CLVA12), 0),ifNull((0+COC2006.CLQT01+COC2006.CLQT02+COC2006.CLQT03+COC2006.CLQT04+COC2006.CLQT05+COC2006.CLQT06+COC2006.CLQT07+COC2006.CLQT08+COC2006.CLQT09+COC2006.CLQT10+COC2006.CLQT11+COC2006.CLQT12), 0),ifNull((0+COC2006.CLVA01+COC2006.CLVA02+COC2006.CLVA03+COC2006.CLVA04+COC2006.CLVA05+COC2006.CLVA06+COC2006.CLVA07+COC2006.CLVA08+COC2006.CLVA09+COC2006.CLVA10+COC2006.CLVA11+COC2006.CLVA12), 0)
ifNull((0+VAW2009.MPQ001+VAW2009.MPQ002+VAW2009.MPQ003+VAW2009.MPQ004+VAW2009.MPQ005+VAW2009.MPQ006+VAW2009.MPQ007+VAW2009.MPQ008+VAW2009.MPQ009+VAW2009.MPQ010+VAW2009.MPQ011+VAW2009.MPQ012),0),
At line 12 changed 3 lines
FROM SMEUP_TPER.MPPIAN0F as CAW2009
join SMEUP_TPER.BRARTI0F on CAW2009.MPCD02=A§ARTI
left join SMEUP_TPER.MPPIAN0F as VAW2009 on CAW2009.MPCD01=VAW2009.MPCD01 and CAW2009.MPCD02=VAW2009.MPCD02 and VAW2009.MPCDPI='BUD09' and VAW2009.MPTPRC='VAW'
ifNull((0+CAO2009.MPQ001+CAO2009.MPQ002+CAO2009.MPQ003+CAO2009.MPQ004+CAO2009.MPQ005+CAO2009.MPQ006+CAO2009.MPQ007+CAO2009.MPQ008+CAO2009.MPQ009+CAO2009.MPQ010+CAO2009.MPQ011+CAO2009.MPQ012),0),
At line 16 changed one line
left join SMEUP_TPER.MPPIAN0F as CAO2009 on CAW2009.MPCD01=CAO2009.MPCD01 and CAW2009.MPCD02=CAO2009.MPCD02 and CAO2009.MPCDPI='BUD09' and CAO2009.MPTPRC='CAO'
ifNull((0+VAO2009.MPQ001+VAO2009.MPQ002+VAO2009.MPQ003+VAO2009.MPQ004+VAO2009.MPQ005+VAO2009.MPQ006+VAO2009.MPQ007+VAO2009.MPQ008+VAO2009.MPQ009+VAO2009.MPQ010+VAO2009.MPQ011+VAO2009.MPQ012),0),
At line 18 changed one line
left join SMEUP_TPER.MPPIAN0F as VAO2009 on CAW2009.MPCD01=VAO2009.MPCD01 and CAW2009.MPCD02=VAO2009.MPCD02 and VAO2009.MPCDPI='BUD09' and VAO2009.MPTPRC='VAO'
ifNull((0+COC2008.CLQT01+COC2008.CLQT02+COC2008.CLQT03+COC2008.CLQT04+COC2008.CLQT05+COC2008.CLQT06+COC2008.CLQT07+COC2008.CLQT08+COC2008.CLQT09+COC2008.CLQT10+COC2008.CLQT11+COC2008.CLQT12),0),
At line 20 changed one line
left join SMEUP_TPER.XCOCLM0F as COC2008 on CAW2009.MPCD01=COC2008.CLCC06 and CAW2009.MPCD02=COC2008.CLCA15 and COC2008.CLANNO=08 and COC2008.CLBGVE='V'
ifNull((0+COC2008.CLVA01+COC2008.CLVA02+COC2008.CLVA03+COC2008.CLVA04+COC2008.CLVA05+COC2008.CLVA06+COC2008.CLVA07+COC2008.CLVA08+COC2008.CLVA09+COC2008.CLVA10+COC2008.CLVA11+COC2008.CLVA12),0),
At line 22 changed one line
left join SMEUP_TPER.XCOCLM0F as COC2007 on CAW2009.MPCD01=COC2007.CLCC06 and CAW2009.MPCD02=COC2007.CLCA15 and COC2007.CLANNO=07 and COC2007.CLBGVE='V'
ifNull((0+COC2007.CLQT01+COC2007.CLQT02+COC2007.CLQT03+COC2007.CLQT04+COC2007.CLQT05+COC2007.CLQT06+COC2007.CLQT07+COC2007.CLQT08+COC2007.CLQT09+COC2007.CLQT10+COC2007.CLQT11+COC2007.CLQT12),0),
At line 24 changed one line
left join SMEUP_TPER.XCOCLM0F as COC2006 on CAW2009.MPCD01=COC2006.CLCC06 and CAW2009.MPCD02=COC2006.CLCA15 and COC2006.CLANNO=06 and COC2006.CLBGVE='V'
ifNull((0+COC2007.CLVA01+COC2007.CLVA02+COC2007.CLVA03+COC2007.CLVA04+COC2007.CLVA05+COC2007.CLVA06+COC2007.CLVA07+COC2007.CLVA08+COC2007.CLVA09+COC2007.CLVA10+COC2007.CLVA11+COC2007.CLVA12),0),
At line 79 added one line
ifNull((0+COC2006.CLQT01+COC2006.CLQT02+COC2006.CLQT03+COC2006.CLQT04+COC2006.CLQT05+COC2006.CLQT06+COC2006.CLQT07+COC2006.CLQT08+COC2006.CLQT09+COC2006.CLQT10+COC2006.CLQT11+COC2006.CLQT12),0),
At line 81 added one line
ifNull((0+COC2006.CLVA01+COC2006.CLVA02+COC2006.CLVA03+COC2006.CLVA04+COC2006.CLVA05+COC2006.CLVA06+COC2006.CLVA07+COC2006.CLVA08+COC2006.CLVA09+COC2006.CLVA10+COC2006.CLVA11+COC2006.CLVA12), 0)
At line 28 removed one line
WHERE A§CLGE='F' and CAW2009.MPCD01='014401' and CAW2009.MPCD02 like '9033030300%' and CAW2009.MPCDPI='BUD09' and CAW2009.MPTPRC='CAW' ORDER BY CAW2009.MPCD01,CAW2009.MPCD02
At line 85 added 57 lines
FROM
QTEMP.XJAC05_SM
join SMEUP_TPER.BRARTI0F on ART=A§ARTI
left join SMEUP_TPER.MPPIAN0F as CAW2009 on CLI=CAW2009.MPCD01 and ART=CAW2009.MPCD02 and CAW2009.MPCDPI='BUD09' and CAW2009.MPTPRC='CAW'
left join SMEUP_TPER.MPPIAN0F as VAW2009 on CLI=VAW2009.MPCD01 and ART=VAW2009.MPCD02 and VAW2009.MPCDPI='BUD09' and VAW2009.MPTPRC='VAW'
left join SMEUP_TPER.MPPIAN0F as CAO2009 on CLI=CAO2009.MPCD01 and ART=CAO2009.MPCD02 and CAO2009.MPCDPI='BUD09' and CAO2009.MPTPRC='CAO'
left join SMEUP_TPER.MPPIAN0F as VAO2009 on CLI=VAO2009.MPCD01 and ART=VAO2009.MPCD02 and VAO2009.MPCDPI='BUD09' and VAO2009.MPTPRC='VAO'
left join SMEUP_TPER.XCOCLM0F as COC2008 on CLI=COC2008.CLCC06 and ART=COC2008.CLCA15 and COC2008.CLANNO=08 and COC2008.CLBGVE='V'
left join SMEUP_TPER.XCOCLM0F as COC2007 on CLI=COC2007.CLCC06 and ART=COC2007.CLCA15 and COC2007.CLANNO=07 and COC2007.CLBGVE='V'
left join SMEUP_TPER.XCOCLM0F as COC2006 on CLI=COC2006.CLCC06 and ART=COC2006.CLCA15 and COC2006.CLANNO=06 and COC2006.CLBGVE='V'
WHERE A§CLGE='F' ORDER BY CAW2009.MPCD01,CAW2009.MPCD02;
----
Due annotazioni...
!! Clausola Order in inserimento
insert into QTEMP.XJAC05_SM
select distinct MPCD01 as CLI,MPCD02 as ART from SMEUP_TPER.MPPIAN0F where MPCDPI='BUD09' and MPCD01='005948' and MPCD02 like '6028005%'
union
(select distinct CLCC06 as CLI,CLCA15 as ART from SMEUP_TPER.XCOCLM0F where (CLANNO BETWEEN 07 and 08) and CLCC06='005948' and CLCA15 like '6028005%')
order by CLI,ART
la __order by CLI,ART__ non dovrebbe essere (almeno a livello generale) significativa giusto?
!! Meglio nella WHERE O nella JOIN?
Da quello che ho capito l'istruzione di WHERE è riferita ad una tabella joinata.
Se viene effettuata una "inner join" dovrebbero essere equivalenti
Se viene effettuata una "left join" verrebbero invece esclusi tutti i record non presenti nella tabella joinata, riconducendola quindi ad una "inner join".
Non sarebbe meglio utilizzare come "best practice" la selezione a livello di clausola JOIN?
Cosa ne pensate?
WHERE A§CLGE='F'
--MarcoDePasquale, 30-ott-2008
Version Date Modified Size Author Changes ... Change note
16 14-Apr-2010 14:30 5.25 kB MarcoDePasquale to previous Comment by MarcoDePasquale
15 14-Apr-2010 14:30 4.218 kB MattiaRocchi to previous | to last
14 14-Apr-2010 14:30 4.218 kB MattiaRocchi to previous | to last
13 14-Apr-2010 14:30 4.216 kB MattiaRocchi to previous | to last
12 14-Apr-2010 14:30 3.758 kB MattiaRocchi to previous | to last
11 14-Apr-2010 14:30 3.736 kB MattiaRocchi to previous | to last
10 14-Apr-2010 14:30 3.658 kB MattiaRocchi to previous | to last
9 14-Apr-2010 14:30 3.605 kB MattiaRocchi to previous | to last
8 14-Apr-2010 14:30 3.466 kB MattiaRocchi to previous | to last
7 14-Apr-2010 14:30 3.399 kB MattiaRocchi to previous | to last
6 14-Apr-2010 14:30 3.398 kB Administrator to previous | to last
5 14-Apr-2010 14:30 3.383 kB Administrator to previous | to last
4 14-Apr-2010 14:30 3.328 kB Administrator to previous | to last
3 14-Apr-2010 14:30 3.31 kB Administrator to previous | to last
2 14-Apr-2010 14:30 3.311 kB Administrator to previous | to last
1 14-Apr-2010 14:30 0.067 kB Administrator to last
« This page (revision-16) was last changed on 14-Apr-2010 14:30 by MarcoDePasquale