Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?

Crosstab query for QMF/DB2

Posted on 2017-03-22
55
Medium Priority
?
63 Views
Last Modified: 2017-07-31
Hello. I am trying to create a crosstab query in QMF/DB2. I know that there is no Transform function so can this be done in QMF/DB2?
The fields I'm working with are Store#, CSG, and Order#. I would like the row headings to be the Store#, the column headings to be the CSG,
and I want to calculate the count of Order#.
Thanks,
James
0
Comment
Question by:James Lea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 28
  • 22
  • 5
55 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42059998
Hi James,

Can you provide a brief description of what the table(s) look like?

Thanks...
0
 

Author Comment

by:James Lea
ID: 42060029
Hi Kent,

It is just one table named MRR. The Store# is a 7 digit alpha numeric field, the CSG is a 3 digit alpha numeric field, and the Order# is a 14 digit alpha numeric field. Is this what you needed?
Thanks,
James
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42060049
The traditional query would look something like this:

  SELECT StoreNumber, CSG, count(OrderNumber) Count FROM ...

And you'd get results like

  StoreNumber   CSG   Count
   AAAAAAA          A01     1
   AAAAAAA          G12     4
   BBBBBBB          A04     2

To Pivot (Crosstab) this in DB2 is a bit tricky, especially if there are a lot of stores.  Can you live with each row being a single string?  If so, recursive SQL will do this quite nicely.  Otherwise, we'll need to be creative and the query source will grow with the number of stores (columns).

A single string, delimited by tabs, commas, semi-colons, etc. is pretty easy.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:James Lea
ID: 42060079
Kent,

I would like the end result to look like this:

Store#      CSG(1)  CSG(2)   CSG(3)  CSG(4)  etc...
szd3279     1000        550         772        124     etc...
uyt0019        623        429         333          44     etc...

There are only up to 6 Store#'s and up to about 30 CSG's.
0
 

Author Comment

by:James Lea
ID: 42060083
Kent,

 I forgot to add a totals column at the end:

 Store#      CSG(1)  CSG(2)   CSG(3)  CSG(4)  etc...  Total
 szd3279     1000        550         772        124     etc...  2446
 uyt0019        623        429         333          44     etc...  1429

 There are only up to 6 Store#'s and up to about 30 CSG's.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42060094
Is that up to 30 CSGs per store (implying up to 180 columns) or 30 combinations?

If this is going to feed into a spreadsheet, I'll suggest the single string per line.  If it will populate a grid in your database client we'll need to build out the full SQL, or build meta-SQL.  That might actually be easier.
0
 

Author Comment

by:James Lea
ID: 42060115
Kent,

It would be 30 possible combinations, only 30 columns max. I am working in QMF and would be exporting it to a word or excel document. Hoping that the end result would look like the attached file.
QMF-Qry-Ex1.xlsx
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42060399
Hi James,

I've got a query that should do the job.  It writes the selected data out in a csv (comma delimited) file that's suitable for importing into Excel.  The result of the query with the data described below is:

Store#, C01, C02, C03, C04
AAA000001, 2, 0, 0, 0
BBB000001, 1, 2, 0, 0
CCC000001, 2, 0, 1, 1
DDD000001, 3, 1, 1, 0


Will this work for you?

WITH g (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
b (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join g t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
c (ln, store, text) as
(
  SELECT 1, b.store, cast (b.store as varchar (100)) FROM b WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM c, b 
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
H (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (100)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM g, h
  WHERE tn = rn
)
SELECT Text 
FROM c
WHERE c.ln = (SELECT count(*) from g) + 1
UNION ALL
SELECT text FROM H
WHERE h.tn = (SELECT count(*) from g) + 1;

Open in new window


I tested it by creating the table and data:

CREATE TABLE a (store varchar(20), csg varchar(10), ordernumber integer)
INSERT INTO a values ('AAA000001', 'C01', 1)
INSERT INTO a values ('AAA000001', 'C01', 2)
INSERT INTO a values ('BBB000001', 'C01', 3)
INSERT INTO a values ('BBB000001', 'C02', 4)
INSERT INTO a values ('BBB000001', 'C02', 5)
INSERT INTO a values ('CCC000001', 'C01', 6)
INSERT INTO a values ('CCC000001', 'C03', 7)
INSERT INTO a values ('CCC000001', 'C01', 8)
INSERT INTO a values ('CCC000001', 'C04', 9)
INSERT INTO a values ('DDD000001', 'C01', 10)
INSERT INTO a values ('DDD000001', 'C01', 11)
INSERT INTO a values ('DDD000001', 'C02', 12)
INSERT INTO a values ('DDD000001', 'C03', 13)
INSERT INTO a values ('DDD000001', 'C01', 14)
COMMIT

Open in new window

0
 

Author Comment

by:James Lea
ID: 42060539
Kent,

I am getting the following error:

BIC0004. DAL01008. An error occurred while accessing the database. VALUE SZ3279, 5, 1779, 465, 1614, 2777, 160, 110, 3, 338, 2173, 5, 8, 36, 3 IS TOO LONG. SQL CODE=-433, SQLSTATE=2201,DRIVER=4.17.29
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42060649
You said "CSG is a 3 digit alpha numeric field", but you're showing us multiple CSG values that apparently have 4 digits. Please provide actual database column definitions. Thirty 3-digit values account for 90 positions, but thirsty 4-digit values need a larger space, 120 positions. Declaring lengths requires knowing some details.
0
 

Author Comment

by:James Lea
ID: 42060788
The CSG is a 3 digit alpha numeric such as B15, B16, B17, etc. The values that the error message shows are the total number (count) of the orders for the store# (SZ3279) by each CSG. For example, for SZ3279, CSG B15 had 5 orders, CSG B16 had 1779 orders, CSG B17 had 465 orders, etc. There are no thirsty 4 digit CSG values.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42061008
Ah. Those are order counts... so "CSG(1)" doesn't mean "the 1st CSG", and below that the order count is 1000 for that CSG.

For example, for SZ3279, CSG B15 had 5 orders, CSG B16 had 1779 orders, CSG B17 had 465 orders, etc.
So would the column headings then be "CSG(B15)  CSG(B16)  CSG(B17) ... etc."? Or would they remain as "CSG(1)  CSG(2)   CSG(3)  CSG(4)  etc."?

That question essentially asks whether all vertical CSG columns will provide order counts for the same CSGs or if they're simple ordinal positions in each row.

Regardless, in the provided test script, try replacing all "varchar (100)" with "varchar (200)" or maybe "varchar (500)". Lengths simply seem a bit short, so make them longer.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061294
Hi James,

One of the oddities of recursive SQL in DB2 is that string (varchar) lengths are determined by the first result.  The item can grow during the recursive process, but only up to that limit.  The good news is that it's easy to fix be recasting the varchar item in the upper sub-query.  Here's the same query with a longer string length for the returned item.  I realize that I also didn't include the totals by line column, so I'll work that up and post it shortly.

The only change between the two queries is that the two lines above the UNION ALL statements recast the string to varchar(1000) instead of 100.

WITH g (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
b (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join g t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
c (ln, store, text) as
(
  SELECT 1, b.store, cast (b.store as varchar (1000)) FROM b WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM c, b 
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
H (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (1000)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM g, h
  WHERE tn = rn
)
SELECT Text 
FROM c
WHERE c.ln = (SELECT count(*) from g) + 1
UNION ALL
SELECT text FROM H
WHERE h.tn = (SELECT count(*) from g) + 1;

Open in new window

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061480
Hi James,

This should be the last significant change (unless the needs change or I've misunderstood them).  I've changed the sub-query names to have some meaning and added the totals column in the final results.

Here's a rundown of each of the sub-queries:

- csg_list -- generate a list of all the CSG names that will appear in the result.
- Store_Summary -- generate a list of all stores, applicable CSGs, and the number of invoices for the store.
- Results - generates the output string for each store.
- Heading - Generates the heading row.

You can see the results of each of them, and the steps taken to build the result by executing portions of the CTE.
- replace the comma and following text after the csg_list definition with "select * from csg_list" to see all of the csg values.
- replace the comma and following text after the Store_Summary definition with "select * from Store_Summary"
- replace the comma and following text after the Results definition with "Select * from Results"
- replace the comma and following text after the Heading definition with "Select * from Heading"

DB2 is very different from SQL Server in handling recursive SQL and CTE structure.  
- Some of the tests above will fail on SQL Server as it rejects a CTE where all sub-queries aren't used, making testing difficult.
- As mentioned earlier, DB2 doesn't automatically extend string definitions, SQL Server does.
- DB2 will continue the recursive process until an answer is found or the stack overflows, SQL Server defaults to a depth of 100.
- The two sub-queries that produce the final result will need to be in the other order on SQL Server.

You probably don't need to know that, but if you have a SQL Server person on staff he'll need to know that.

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Heading (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results c
WHERE c.ln = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1;

Open in new window



Good Luck!
Kent
0
 

Author Comment

by:James Lea
ID: 42061605
Kent,

When I run this query, only the header line (I think) is returned (see attachment). What could I be doing wrong?

Thanks everyone for your help.

James
2017-03-23_7-44-18.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061638
It looks like a data issue.  Note that the second item in the header is blank, suggesting one or more store has a blank or NULL for a csg value (I suspect NULL).  Let's check it with the first sub-query in the CTE.

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
)
SELECT * FROM csg_list;

If you see a blank or NULL value, modify the csg_list sub-query to be:

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
0
 

Author Comment

by:James Lea
ID: 42061701
There was a blank and I modified the query as you instructed. I now get 2 lines of data, the header line and data for the first store. I've attached the results.

Thank you,

James
2017-03-23_8-19-19.png
0
 

Author Comment

by:James Lea
ID: 42061748
When I run just the csg_list and Store_summary subqueries together, I get 932 lines of data, but at line 79 the store# becomes 'Ÿ&#0;&#0;&#0;&#0;&#0;'. I've attached the results.

Thanks,

James
2017-03-23_8-34-40.png
2017-03-23_8-35-19.png
2017-03-23_8-35-45.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061758
That's odd.  It looks like there may be binary data in one of the store numbers.

Can you select all of your store numbers from the table, sorting by name?  If binary data is in one of the names it should sort to the top and/or bottom of the list.

Also, it looks like we'll need to swap the two items in the final query.  DB2 for that level on the AS400 seems to unwind the stack differently than does DB2 on my system.  But that's a minor cleanup for the end.
0
 

Author Comment

by:James Lea
ID: 42061834
Ok, I figured out my mistake and the 'Ÿ&#0;&#0;&#0;&#0;&#0;' is gone. Now I am getting the 2 lines again when running the query. See the attached results.

Thanks,

James
2017-03-23_9-21-47.png
0
 

Author Comment

by:James Lea
ID: 42061839
There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, & UYT001.
0
 

Author Comment

by:James Lea
ID: 42061905
There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, & UYT001.
There are currently 27 CSGs in my table (BK1 through ZZZ).
There are a total of 15,121 orders in my table.

Thanks,

James
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061917
I'm not sure why we're not seeing all 4 stores.  What do you get when you run just the first two sub-queries?

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
)
SELECT * FROM Store_Summary;

and

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
)
SELECT distinct store FROM Store_Summary;
0
 

Author Comment

by:James Lea
ID: 42061941
When running the first query, I get 78 lines.

When running the second query, I get 4 lines (the 4 store#s).
2017-03-23_10-14-38.png
2017-03-23_10-15-39.png
2017-03-23_10-16-35.png
2017-03-23_10-11-53.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061957
Ok.  Something in building the row data isn't behaving as expected.  (Probably another data issue.)

What do we get from this:

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
)
SELECT * FROM Results;

Open in new window

0
 

Author Comment

by:James Lea
ID: 42061962
When I put 'Select * from Heading' after the first part of the Heading query I get the attached results.

...
Heading (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM csg_list, Heading
  WHERE tn = rn
)
Select * from Heading
2017-03-23_10-31-00.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061974
That looks normal.  The recursive query builds up the row by appending the text to the previously created row.  That's why the final query selects the row with the highest ID value, to get only the row with all of the data.

The missing data probably comes from something in the Results sub-query though.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42061989
It looks like I didn't carry forward a change to the csg_list sub-query.  Let's go back to a baseline.

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Heading (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results c
WHERE c.ln = (SELECT count(*) from csg_list) + 1;

Open in new window

0
 

Author Comment

by:James Lea
ID: 42061990
From your above question, I get the attached results.
2017-03-23_10-41-26.png
2017-03-23_10-41-53.png
2017-03-23_10-42-16.png
0
 

Author Comment

by:James Lea
ID: 42062012
From the query in your response 'It looks like I didn't carry forward a change to the csg_list sub-query.  Let's go back to a baseline.', I get the attached results.
2017-03-23_10-51-27.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062050
Man, but I need to apologize.  I copied the wrong thing.  :(  One more time...

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Heading (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results r
WHERE r.ln = (SELECT max(ln) from Result r2 WHERE r.store = r2.store);

Open in new window

0
 

Author Comment

by:James Lea
ID: 42062084
Looking better. Attached are the results. Can I get it in columns and rows like the 2nd attachment so that the order counts will be below the correct CSG's?

Thank you,

James
2017-03-23_11-30-56.png
2017-03-23_11-34-30.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062229
Ok.  This looks pretty good to me.  :)

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5)) 
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Footing (ln, text) as
(
  SELECT 0 ln, cast ('Totals' as varchar (400)) FROM sysibm.sysdummy1
  UNION ALL
  SELECT ln+1, Text || ', ' || cast ((SELECT coalesce (SUM (count), 0) FROM Store_Summary SS WHERE SS.csg = csg_list.csg) as varchar (10))
  FROM csg_list, Footing
  WHERE rn = ln+1
),
Heading (tn, text) as
(
  SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ', ' || csg
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results r
WHERE r.ln = (SELECT max(ln) from Results r2 WHERE r.store = r2.store)
UNION ALL
SELECT Text || ', ' || cast ((SELECT sum(count) FROM Store_Summary) as varchar(10))
FROM Footing F
WHERE f.ln = (SELECT max(ln) FROM footing);

Open in new window

0
 

Author Comment

by:James Lea
ID: 42062259
Attached are the results. Can I get it in columns and rows like the 2nd attachment so that the order counts will be aligned below the corresponding CSG's?

Thanks,

James
2017-03-23_13-32-31.png
2017-03-23_11-34-30.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062314
That's a bit trickier, but doable.  :)  I'll get it done.

The comma separated results are intended for opening/importing into Excel.

Kent
0
 

Author Comment

by:James Lea
ID: 42062327
Kent,

How could I import the results into Excel and have the results aligned under the corresponding column headers (CSG's)?
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062351
Save the results in a file (on a PC, not the AS400) with a file extension of .csv.  Then open the file with excel.

You can also copy and paste the contents into Excel, but they'll probably all wind up in column A.  If so, click on the Data tab and then click on the "Text To Columns" icon.

Either should work just fine.

Kent
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062358
Excel also has a Pivot function.  A much simpler query with the CSG headings down the left side and the store numbers across the top is available to us.  Once imported into Excel, Click on the Insert tab (icon), then on Pivot Table.  Excel should walk you through pivoting the data so that the store numbers are on the left and the CSG values along the top.
0
 

Author Comment

by:James Lea
ID: 42062377
The problem I'm having with it is that the data is not aligned under the corresponding column header (CSG). When I put it into Excel, it all goes into column A as you said. When I select column A and then click on the Data tab and then click on the "Text To Columns" icon, and I convert the text to columns using commas as the delimiter, the data is separated in columns from left to right but not aligned in the correct cells under the corresponding column headers (CSG's). The data is just filled into the cells from left to right. Hope that makes sense.

Thanks,

James
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062437
It does make sense.  

Let me check the results and make sure that they line up in the text we're producing.
0
 

Author Comment

by:James Lea
ID: 42062488
Not every store has an order for each CSG. Maybe we could put zeros in where there's no orders?
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42062565
Using COALESCE() to put zeros into the sums in place of NUL values is a good idea when possible.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42062947
Hi James,

Using my (admittedly limited) sample data everything lines up fine.  And I see zeroes (not NULL or blank).

When you select "Text to Columns", make sure you also select "delimited" and that the comma is the delimiter.

Also, when I save the output to a file as "sample.csv" and open it with Excel it also formats correctly.

Kent
0
 

Author Comment

by:James Lea
ID: 42063351
Kent,

Thank you. I did get it to format correctly in Excel. I am working with QMF and trying to get the results to export into Excel with a procedure like many of my other queries. Is there a way to have the data display in QMF like one of the examples in the attached file so that it doesn't need manual formatting in Excel?

Thanks again,

James
0
 

Author Comment

by:James Lea
ID: 42063355
Attached are how the results currently look in QMF
2017-03-24_7-48-53.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42063600
This should be pretty close.  

WITH csg_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn, csg
  FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
  SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
  FROM (select distinct store from a) t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total, cast (cast (Store_Summary.store as char(10)) as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ' | ' || right ('       ' || cast (coalesce (count,0) as varchar(8)), 8)
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Footing (ln, text) as
(
  SELECT 0 ln, cast (cast ('Totals' as char(10)) as varchar (400)) FROM sysibm.sysdummy1
  UNION ALL
  SELECT ln+1, Text || ' | ' || right ('       ' || cast ((SELECT coalesce (SUM (count), 0) FROM Store_Summary SS WHERE SS.csg = csg_list.csg) as varchar (8)), 8)
  FROM csg_list, Footing
  WHERE rn = ln+1
),
Heading (tn, text) as
(
  SELECT 1, cast(cast ('Store#' as char(10)) as varchar (400)) text from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ' | ' || cast (csg as char(8))
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT text || ' | Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ' | ' || right ('       ' || cast (total as varchar(8)), 8)
FROM Results r
WHERE r.ln = (SELECT max(ln) from Results r2 WHERE r.store = r2.store)
UNION ALL
SELECT Text || ' | ' || right ('       ' || cast ((SELECT sum(count) FROM Store_Summary) as varchar(10)), 8)
FROM Footing F
WHERE f.ln = (SELECT max(ln) FROM footing);

Open in new window

0
 

Author Comment

by:James Lea
ID: 42063696
Attached are the results from your above query.
2017-03-24_11-27-43.png
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42063713
Let's see if Tom (tliotta) is still lurking here.

When I run that on my version of DB2, I get the expected results (below).  Of course, I don't have QMF available to me.  I suspect that QMF is compacting consecutive spaces, but I can't test or prove that.

Tom.  Do you have an idea of what's happening or what our next step should be?

Thanks,
Kent

Store#     | C01      | C02      | C03      | C04      | Total
AAA000001  |        2 |        0 |        0 |        0 |        2
BBB000001  |        1 |        2 |        0 |        0 |        3
CCC000001  |        2 |        0 |        1 |        1 |        4
DDD000001  |        3 |        1 |        1 |        0 |        5
Totals     |        8 |        3 |        2 |        1 |        14

Open in new window

0
 

Author Comment

by:James Lea
ID: 42063717
Ok, thank you very much Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42064939
Also no "QMF" here, but I'll set up DB2 with test data above (this evening perhaps) and see what happens in a couple different clients. I see at least one change in test data that could help narrow down any issue. I'll post back when I know something.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 42065150
No good news from me. Exact match with Kent's results. I also made a minor change to Kent's test data to verify an odd possibility, but the result was still perfect.

We need to see the exact query that gets the bad result. Please post it as code (or text) and not as an image because we'll need to edit it.
0
 

Author Comment

by:James Lea
ID: 42070467
Sorry, I've been on vacation. I will be posting​ the query soon.

Thanks again
0
 

Author Comment

by:James Lea
ID: 42077184
Here is my query:

WITH consol_sel_grp_list (rn, csg) as
(
  SELECT row_number () over (order by csg) rn,
csg
  FROM (SELECT distinct csg FROM ddtcenvr.mrr
WHERE csg <> ' ' AND csg is not NULL
and doc_id = 'A5J') t3
),
Store_Summary (rn, store, csg, count)
as
(
  SELECT row_number () over (partition by t0.store
order by t0.store, t1.csg) RN, t0.store,
t1.csg, t2.count
  FROM (select distinct store from ddtcenvr.mrr
where doc_id = 'A5J') t0 cross join csg_list t1
  LEFT JOIN (SELECT store, csg,
count(ordernumber) count FROM ddtcenvr.mrr where doc_id = 'A5J'
group by store, csg) t2
    ON t0.store = t2.store
   AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
  SELECT 1 ln, Store_Summary.store, 0 Total,
cast (cast (Store_Summary.store as char(10))
as varchar (400)) FROM Store_Summary WHERE rn = 1
  UNION ALL
  SELECT ln+1, c.store, Total+coalesce(count,0), text || ' | ' ||
right ('       ' || cast (coalesce (count,0) as varchar(8)), 8)
  FROM Results c, Store_Summary b
  WHERE  c.store = b.store
    AND c.ln = b.rn
),
Footing (ln, text) as
(
  SELECT 0 ln, cast (cast ('Totals' as char(10)) as varchar (400))
FROM sysibm.sysdummy1
  UNION ALL
  SELECT ln+1, Text || ' | ' || right ('       ' ||
cast ((SELECT coalesce (SUM (count), 0)
FROM Store_Summary SS
WHERE SS.csg = csg_list.csg)
as varchar (8)), 8)
  FROM csg_list, Footing
  WHERE rn = ln+1
),
Heading (tn, text) as
(
  SELECT 1, cast(cast ('Store#' as char(10))
as varchar (400)) text
from sysibm.sysdummy1
  UNION ALL
  SELECT tn+1, text || ' | ' || cast (csg as char(8))
  FROM csg_list, Heading
  WHERE tn = rn
)
SELECT text || ' | Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ' | ' || right ('       ' || cast (total as varchar(8)), 8)
FROM Results r
WHERE r.ln = (SELECT max(ln) from Results r2
WHERE r.store = r2.store)
UNION ALL
SELECT Text || ' | ' || right ('       ' || cast ((SELECT sum(count)
FROM store_Summary) as varchar(10)), 8)
FROM Footing F
WHERE f.ln = (SELECT max(ln) FROM footing)


Thanks everyone,

James
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 42078306
Hi James,

The query that you posted is fundamentally the same one that I provided.  I would expect the two queries to produce the same results.

When I look at my test data, I see this:

db2 => select * from a;

STORE                CSG        ORDERNUMBER
-------------------- ---------- -----------
AAA000001            C01                  1
AAA000001            C01                  2
BBB000001            C01                  3
BBB000001            C02                  4
BBB000001            C02                  5
CCC000001            C01                  6
CCC000001            C03                  7
CCC000001            C01                  8
CCC000001            C04                  9
DDD000001            C01                 10
DDD000001            C01                 11
DDD000001            C02                 12
DDD000001            C03                 13
DDD000001            C01                 14

Open in new window


The key point is that ORDERNUMBER contains 1 and 2 digit numbers so there's something to use to test spacing.

SELECT cast (ordernumber as char(8)) FROM a;
SELECT cast (ordernumber as varchar(8)) FROM a;

Both queries return exactly the same (visual) text, with the ordernumber being left justified.  There is a subtle, unseen difference in that casting to char(3) retains trailing spaces.  To properly format the output we need to use varchar and drop the trailing spaces.

SELECT cast (ordernumber as char(8)) FROM a;
SELECT cast (ordernumber as varchar(8)) FROM a;

SELECT cast (ordernumber as char(8)) || ' .'FROM a;
SELECT cast (ordernumber as varchar(8)) || ' .' FROM a;

Right justifying the ordernumber as an 8 character string should be as simple as:

SELECT right ('        '  || cast (ordernumber as varchar(8)), 8) FROM a;

Casting to char(8) instead of varchar(8) produces the wrong result.  The 8 leading spaces are concatenated to the left justified 8 character ordernumber, and the rightmost 8 characters extracted.  That's exactly the same as just casting to char(8) and we get the left justified value.

The queries look fine.  The question now is why are not seeing the expected results.

Neither Tom nor I can test QMF.  (I wish we could.)  

Do you have another client that you can connect to DB2?  It might be as simple as running the query through a different interface.

Kent
0
 

Author Comment

by:James Lea
ID: 42084092
I will see if I can try it out on another client.

Thanks everyone
0

Featured Post

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question