Crosstab query for QMF/DB2

Hi James,

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

Thanks...

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

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.

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.

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.

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.

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

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;

Select allOpen 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

Select allOpen in new window

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

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.

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.

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.

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;

Select allOpen in new window

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;

Select allOpen in new window

Good Luck!
Kent

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

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
),

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

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

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.

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

There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, & UYT001.

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

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;

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

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;

Select allOpen in new window

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

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.

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;

Select allOpen in new window

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

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

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);

Select allOpen in new window

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

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);

Select allOpen in new window

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

That's a bit trickier, but doable.  :)  I'll get it done.

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

Kent

Kent,

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

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

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.

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

It does make sense.  

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

Not every store has an order for each CSG. Maybe we could put zeros in where there's no orders?

Using COALESCE() to put zeros into the sums in place of NUL values is a good idea when possible.

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

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

Attached are how the results currently look in QMF
2017-03-24_7-48-53.png

Attached are the results from your above query.
2017-03-24_11-27-43.png

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

Select allOpen in new window

Ok, thank you very much Kent

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.

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.

Sorry, I've been on vacation. I will be posting​ the query soon.

Thanks again

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

I will see if I can try it out on another client.

Thanks everyone