page d'accueil
Boite à outils

Welcome page
Tools box

You have a file with this kind of statistic data :

(dds)
0001.00 R PIVOTF 0002.00 DATE L 0003.00 REGION 20 0004.00 QUANTITY 10S 3
0005.00 AMOUNT 15S 4
(sql)
DATE PIVOTP DATE REGION PIVOTP CHARACTER 20 QUANTITY PIVOTP NUMERIC 10 3
AMOUNT PIVOTP NUMERIC 15 4

for example, with these values :

DATE REGION QUANTITY AMOUNT 01/05/05 one 10,000 10,0000 01/05/05 two 20,000 40,0000 01/05/05 three 30,000 90,0000 02/05/05 one 10,000 10,0000 02/05/05 two 20,000 40,0000 02/05/05 three 30,000 90,0000 03/05/05 one 10,000 10,0000 03/05/05 two 20,000 40,0000 03/05/05 three 30,000 90,0000 

un tableau coisé montrera ces colonnes : 

• Date 
• Quantity--Region 1 
• Quantity--Region 2 
• Quantity--Region 3 
• Quantity--Total Regions 
• Amount--Region 1 
• Amount--Region 2 
• Amount--Region 3 
• Amount--Total Regions 

La clé de ce rapport est que le nombre de colonnes dépend du nombre de régions

ce sql :

a pivot report will shows colums : 

• Date Sold 
• Quantity--Region 1 
• Quantity--Region 2 
• Quantity--Region 3 
• Quantity--Total for All Regions 
• Amount--Region 1 
• Amount--Region 2 
• Amount--Region 3 
• Amount--Total for All Regions 

The key of the report is that the number of columns is depending on the number of distinct REGION values 

this sql :

select distinct region from pivotp 

answers :

REGION
one two three 

that are the key we search. With these keys, we can simply construct this string

select char(date, iso) date, sum(quantity_one ) quantity_one ,
sum(quantity_two ) quantity_two ,
sum(quantity_three ) quantity_three ,
sum(quantity_total ) quantity_total ,
sum(amount_one ) amount_one ,
sum(amount_two ) amount_two ,
sum(amount_three ) amount_three ,
sum(amount_total ) amount_total from ( SELECT DATE , case when region = 'one' then quantity end quantity_one , case when region = 'two' then quantity end quantity_two , case when region = 'three' then quantity end quantity_three
, quantity quantity_total
, case when region = 'one' then amount end amount_one , case when region = 'two' then amount end amount_two , case when region = 'three' then amount end amount_three , amount amount_total FROM pivotp ) as pivot group by date 

réponse :

that is to be run as dynamic rpg sql (rpg sample is sylk21 at SYLK2) (or with RUNSQLSTM or with STRQMQRY ...)

it answers :

 
DATE QUANTITY_ONE QUANTITY_TWO QUANTITY_THREE QUANTITY_TOTAL AMOUNT_ONE AMOUNT_TWO AMOUNT_THREE AMOUNT_TOTAL
01/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140
02/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140
03/05/05 10,000 20,000 30,000 60,000 10,0000 40,0000 90,0000 140

Il ne manque que le total général. Ce SQL s'en charge

actual sql400 response width is here 350+ characters, that is not easy to read. It's the reason why you have a better choice to export directly in excel format. Yes, I know I could have added some DECIMAL function, but excel is a better choice.

Now the horizontal total is missing. This SQL add the missing line :

select 'total',sum(quantity_one ) quantity_one , sum(quantity_two ) quantity_two , sum(quantity_three ) quantity_three , sum(quantity_total ) quantity_total , sum(amount_one ) amount_one , sum(amount_two ) amount_two , sum(amount_three ) amount_three , sum(amount_total ) amount_total from ( SELECT DATE , case when region = 'one' then quantity end quantity_one , case when region = 'two' then quantity end quantity_two , case when region = 'three' then quantity end quantity_three
, quantity quantity_total
, case when region = 'one' then amount end amount_one , case when region = 'two' then amount end amount_two , case when region = 'three' then amount end amount_three , amount amount_total FROM pivotp ) as pivot 

réponse :

it answers

total 30,000 60,000 90,000 180,000 30,0000 120,0000 270,0000 420,0000 

Si vous travaillez en RPG, utilisez deux SQL séparés, c'est plus pratique

Si vous travaillez avec STRQMQRY (donc vous devrez compiler le qmqry avant de l'utiliser, par exemple dans QTEMP) pour obtenir le résultat dans un fichier créé à la volée, préférez une union des deux sql ... ou ajoutez un ; (point virgule) entre les deux sql

Si vous travaillez avec RUNSQLSTM (donc pas besoin de recompiler), vous devrez ajouter un CREATE avant le premier SQL ... et incorporer vos SELECT dans des INSERT INTO

If you work within an RPG program, you have a good choice to work with two independant sql select.

If you work with a STRQMQRY (ie you have to compile the qmqry before running it, for example in QTEMP) to output the select to a dynamicaly created file, you can have a better choice to run an union of the two sqls. Or run each select separated by a semicolon

If you work with RUNSQLSTM (ie you don't want to compile an object), you have to add a sql CREATE before the first select ... and embed the SELECTs into INSERT INTO

Procédure de test :

• adapter et vérifier le code SQL avec STRSQL

• vérifier l'extraction vers un fichier, toujours avec STRSQL

c'est comme ça que j'ai construit cette page

• créer le programme RPG qui va ensuite construire le SQL dynamique

• exécuter ce SQL. De préférence dans le RPG ci-dessus, c'est plus pratique pour la maintenance

• adapt and verify the SQL code with STRSQL 
• verify the extraction to a file, always with STRSQL 

it is like that that I constructed this page 

• create the RPG program that is going to construct the dynamic SQL
• then execute this SQL. Prefer in the above RPG, more convenient for the maintenance

RUNSQLSTM SRCMBR(PIVOTCOUNT) COMMIT(*NONE) SQL2SYLK SQL('select * from pivotcount') IFSDIR(TEMP) IFSFILE('pivotcount.slk') RUNSQLSTM SRCMBR(PIVOTSUM) COMMIT(*NONE) SQL2SYLK SQL('select * from pivotsum')
IFSDIR(TEMP) IFSFILE('pivotsum.slk')