| 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 |
|
| ||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 64 added one line |
|
| At line 66 added one line |
|
| At line 68 added one line |
|
| At line 70 added one line |
|
| At line 72 added one line |
|
| At line 74 added one line |
|
| At line 76 added one line |
|
| At line 78 added one line |
|
| At line 16 removed 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 18 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+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 22 removed 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' |
| At line 24 removed 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' |
| At line 26 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' |
| FROM |
| At line 28 removed 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' |
| At line 30 removed 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' |
| At line 32 changed 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 |
| QTEMP.XJAC05_SM |
| At line 92 added 50 lines |
|
| 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 |
