Crosstab query for QMF/DB2

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
James LeaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi James,

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

Thanks...
0
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

James LeaAuthor Commented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
tliottaCommented:
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
James LeaAuthor Commented:
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
tliottaCommented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
James LeaAuthor Commented:
There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, & UYT001.
0
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
Kent,

How could I import the results into Excel and have the results aligned under the corresponding column headers (CSG's)?
0
Kent OlsenData Warehouse Architect / DBACommented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
It does make sense.  

Let me check the results and make sure that they line up in the text we're producing.
0
James LeaAuthor Commented:
Not every store has an order for each CSG. Maybe we could put zeros in where there's no orders?
0
tliottaCommented:
Using COALESCE() to put zeros into the sums in place of NUL values is a good idea when possible.
0
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
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
James LeaAuthor Commented:
Attached are how the results currently look in QMF
2017-03-24_7-48-53.png
0
Kent OlsenData Warehouse Architect / DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James LeaAuthor Commented:
Attached are the results from your above query.
2017-03-24_11-27-43.png
0
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
Ok, thank you very much Kent
0
tliottaCommented:
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
tliottaCommented:
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
James LeaAuthor Commented:
Sorry, I've been on vacation. I will be posting​ the query soon.

Thanks again
0
James LeaAuthor Commented:
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
Kent OlsenData Warehouse Architect / DBACommented:
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
James LeaAuthor Commented:
I will see if I can try it out on another client.

Thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.