0

I've listed my query below and the results that I'm currently getting. I would like to combine the multiple returns in the "CLASS" column to only return one value if possible.

results

SH DATE    NUMBER     CLASS
2014-7-1   123456     125
2014-7-1   123456     150

I would like to see it as

SH DATE    NUMBER     CLASS
2014-7-1   123456     125, 150

QUERY 

SELECT 
DATE(B.TSTAMP) AS "SH DATE"
,TRIM(B.NUMBER) AS "NUMBER"
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END AS "DATE"
,F.CLASS AS "CLASS"
,S.CITY AS "ORIGIN CITY"
,S.STATE AS "ORIGIN STATE"
,S.ZIPCODE AS "ORIGIN ZIP"
,C.CITY AS "DESTINATION CITY"
,C.STATE AS "DESTINATION STATE"
,C.ZIPCODE AS "DESTINATION ZIP"
,B.WEIGHT

FROM AF.BLUE B

INNER JOIN AF.BILL F
ON F.NUMBER = B.NUMBER
AND F.CORRECTION = B.CORRECTION
AND F.CLASS <> ''

INNER JOIN AF.NAME S
ON S.NUMBER = B.NUMBER
AND S.CORRECTION = B.CORRECTION
AND S.TYPE = 'S'

INNER JOIN AF.NAME C
ON C.NUMBER = B.NUMBER
AND C.CORRECTION = B.CORRECTION
AND C.TYPE = 'C'

WHERE B.CUST = '11111'
AND (MONTH(CURRENT DATE)-1) = MONTH(B.TSTAMP)
AND B.CORR = ''
Robblob
  • 77
  • 1
  • 3
  • 13

1 Answers1

1

ListAgg Docs

SELECT 
DATE(B.TSTAMP) AS "SH DATE"
,TRIM(B.NUMBER) AS "NUMBER"
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END AS "DATE"
,ListAgg(F.CLASS, ', ') WITHIN GROUP (Order by F.Class) AS "CLASS"
,S.CITY AS "ORIGIN CITY"
,S.STATE AS "ORIGIN STATE"
,S.ZIPCODE AS "ORIGIN ZIP"
,C.CITY AS "DESTINATION CITY"
,C.STATE AS "DESTINATION STATE"
,C.ZIPCODE AS "DESTINATION ZIP"
,B.WEIGHT

FROM AF.BLUE B

INNER JOIN AF.BILL F
ON F.NUMBER = B.NUMBER
AND F.CORRECTION = B.CORRECTION
AND F.CLASS <> ''

INNER JOIN AF.NAME S
ON S.NUMBER = B.NUMBER
AND S.CORRECTION = B.CORRECTION
AND S.TYPE = 'S'

INNER JOIN AF.NAME C
ON C.NUMBER = B.NUMBER
AND C.CORRECTION = B.CORRECTION
AND C.TYPE = 'C'

WHERE B.CUST = '11111'
AND (MONTH(CURRENT DATE)-1) = MONTH(B.TSTAMP)
AND B.CORR = ''

GROUP BY 
DATE(B.TSTAMP) 
,TRIM(B.NUMBER) 
,CASE WHEN B.REPORT_TS IS NULL THEN '' ELSE CHAR(DATE(B.REPORT_TS)) END 
,S.CITY 
,S.STATE 
,S.ZIPCODE
,C.CITY 
,C.STATE 
,C.ZIPCODE
,B.WEIGHT
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Getting the following error when running your query ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "GROUP" following "" is not valid. Expected tokens may include: ", FROM INTO". – Robblob Aug 20 '14 at 22:08
  • Sorry group by statements can't be aliased or I need to use alias names take your pick. I removed the "as ColName" from the group by statement. – xQbert Aug 20 '14 at 22:28
  • So I can't use the "S, B, C" prefix in the group by? Is that what you're saying? Those are definitely needed in my query to avoid any ambiguity. – Robblob Aug 20 '14 at 22:34
  • No it's the column aliases that I removed. Table aliases remain. And the aliases JUST needed to be removed in the group by. They are fine in the select. Additionally what version of DB2 is this... perhaps listagg isn't supported in your version. – xQbert Aug 20 '14 at 22:36
  • Still getting the same error. I even tried removing the CASE statement and DATE/TRIM functions. Hmmmm – Robblob Aug 20 '14 at 22:40
  • Version of DB2? ListAgg or perhaps the sub ordering just isn't working. try changing line FROM `ListAgg(F.CLASS, ', ') WITHIN GROUP (Order by F.Class) AS "CLASS"` TO `ListAgg(F.CLASS, ', ')` AS "CLASS"` if sub order doesn't matter. – xQbert Aug 20 '14 at 22:43
  • Ok, that got me another error. ERROR [42884] [IBM][DB2] SQL0440N No authorized routine named "LISTAGG" of type "" having compatible arguments was found. I'm using version 9.7.600.413 – Robblob Aug 21 '14 at 14:04
  • I wanted to add that I have two different databases I pull from and the LISTAGG function works perfectly fine in one but not the other. I'm working with my IT dept to see what the deal is. – Robblob Aug 21 '14 at 14:21
  • The new error seems to indicate there may be a data type issue between associated tables. Table definitions would now be helpful. Are you sure that the data types between the different joined columns are all of the same type, size, precision? can F.CLASS be NULL? maybe `ListAgg(coalesce(F.CLASS,''), ', ')` or use the where clause or join to exclude the nulls. – xQbert Aug 21 '14 at 16:27
  • Bahh I think It's numeric isn't it. and we're generating a character (appending a comma) output.... I think you need to cast the number to character first and then listagg it. http://stackoverflow.com/questions/1038670/datatype-conversion-in-ibm-db2 may help with he casting depending on data type of f.class. I think the DB sees the first record as number, then tries to stuff the 2nd one in with a comma and says wait... You're a numeric field I can't do this and you get the SQL0440N error. – xQbert Aug 21 '14 at 16:32
  • Just confirmed that the mainframe database I'm trying to pull from is an older version of another database I use. Of course the one I need the function for is older and doesn't recognize the command yet.... go figure. You still answered the question though and I have added this new function to my arsenal. Thank you very much for your time! – Robblob Aug 21 '14 at 16:42
  • Cheers... better luck. You may want to look at how DB2 did "LISTAGG" functions for that DB version. XMLAgg may have been available and there are ways to achieve this through other means as well, just not built in functions :D – xQbert Aug 21 '14 at 16:55