Error when trying to combine field in DB2 SQL

Question

I have a query that gets current inventory levels from our database. The base query works without issue, but I need to add in the Ship_From field. The issue is, if I just add it to the select query, it will cause duplicated lines for each location (location is the only thing that's different between them). I tried to figure it out using this reference and this SO question as a guide. Unfortunately I got stuck with the query (below) giving me the error:

"An error occurred while running the query.

Illegal use of keyword WITH, token QUERYNO HAVING WHERE GROUP ORDER INTERSECT was expected.

(SQL code = -199, SQL state = 42601)"

I'm using QMF for Windows Version 8.1 Fix Pack 15 (Unicode) to execute the query. The only field that has duplicates is Ship_from and I want to combine the values into one line to remove duplicated data like this example (removing some columns since they all are the same except for the Ship_From field):

Current:

SUPPLIER_NAME|CONTAINER_CODE|ALLOC_QTY|   SOH   | %_Fill|Ship_from|
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IOSL1  |
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IHMI1  |
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IHMI2  |

Desired:

SUPPLIER_NAME|CONTAINER_CODE|ALLOC_QTY|   SOH   | %_Fill|     Ship_from     |
Supplier A   |   Pallet     |   100   |   96    | 96%   |IOSL1, IHMI1, IHMI2|

SQL Code:

with X(Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", Ship_from) as (

select distinct
    ProcFlowPlus.Sup_No,
    ProcFlowPlus.Supplier_name,
    ProcFlowPlus.CONTAINER_CODE,
    int(Allocation.ALLOC_QTY) as Alloc_Qty,
    Allocation.SAFE_STOCK_QTY,
    int(Allocation.TOT_ALLOC_REQ_QTY) as TOT_ALLOC_REQ_QTY,
    BuckStat.CONTAINER_TYPE_QTY as SOH,
    SIE_Bucket.SIE,
    int((BuckStat.CONTAINER_TYPE_QTY + SIE_Bucket.SIE) - Allocation.TOT_ALLOC_REQ_QTY) as Disc_Qty,
    concat (int((CONTAINER_TYPE_QTY + SIE_Bucket.SIE)/ Allocation.TOT_ALLOC_REQ_QTY * 100), '%') as "%_Fill",
    ProcFlowPlus.PROC_FLO_DESC as Ship_from

from
    (select distinct
        ProcFlow.PROC_FLO_ID,
        ContTrans.CONTAINER_TYPE_ID,
        ContTrans.CONTAINER_CODE,
        SupLookup.Sup_No,
        SupName.Supplier_name,
        ProcFlow.PROC_FLO_DESC
    from 
        (select distinct
            ProcFlow.SUPPLIER_ID,
            ProcFlow.PROC_FLO_ID,
            ProcFlow.PROC_FLO_DESC,
            ProcFlow.PROC_FLO_NAME
        from rcx.RXPRF1 ProcFlow
        where PLANT_ID = '50000036') as ProcFlow
        left join (select distinct
                       SUPPLIER_ID,
                       SUPPLIER_NO concat SUPPLIER_LOCATION as Sup_No
                   from rcx.RXPIR1 Suplookup
                   where PLANT_ID = '50000036'
                  ) as SupLookup on ProcFlow.SUPPLIER_ID = SupLookup.SUPPLIER_ID
        left join (select distinct
                       SupInfo.Supplier,
                       SupInfo.Supplier_name,
                       suplookup.SUPPLIER_ID
                   from 
                       (select distinct
                           SUPPLIER_ID,
                           concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num
                        from rcx.RXPIR1
                        where COMPANY_CODE like('HMI%')
                            and not SUPPLIER_NO in ('JN9999','272180','506776','081583','504880')
                        ) as SupLookup
                   left join rcx.RXSIV1 SupInfo on SupLookup.Sup_Num = SupInfo.SUPPLIER
                   where COMPANY_CODE like('HMI%')
                   ) as SupName on SupLookup.SUPPLIER_ID = SupName.SUPPLIER_ID

        left join rcx.RXPFC1 flowcont on ProcFlow.PROC_FLO_ID = flowcont.PROC_FLO_ID 
        left join rcx.RXCTY1 ContTrans on flowcont.CONTAINER_TYPE_ID = ContTrans.CONTAINER_TYPE_ID
        where not ProcFlow.PROC_FLO_NAME like '%RPR%'
            and (right(ProcFlow.PROC_FLO_DESC, 5) like ('IHMI%') 
            or right(ProcFlow.PROC_FLO_DESC, 5) like ('%IOSL%'))
        ) as ProcFlowPlus

    left join (select distinct
                  SupLookup.Sup_Num,
                  Suplookup.CONTAINER_CODE,
                  AllocationType.ALLOC_TYPE_DESC,
                  Allocation.ALLOC_QTY,
                  Allocation.SAFE_STOCK_QTY,
                  Allocation.ALLOC_QTY + Allocation.SAFE_STOCK_QTY as TOT_ALLOC_REQ_QTY

               from RCX.RXSAL1 Allocation
               inner join rcx.RXALT1 AllocationType on Allocation.ALLOC_TYPE_ID = AllocationType.ALLOC_TYPE_ID
               left join (select distinct
                              SUPPLIER_ID,
                              concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num,
                              CONTAINER_CODE,
                              CONTAINER_TYPE_ID
                         from rcx.RXPIR1    
                         where PLANT_ID= '50000036'
                         ) as SupLookup on Allocation.SUPPLIER_ID = suplookup.SUPPLIER_ID and allocation.CONTAINER_TYPE_ID = suplookup.CONTAINER_TYPE_ID
               where Allocation.PLANT_ID= '50000036'
               ) as Allocation on ProcFlowPlus.Sup_No = Allocation.Sup_Num and ProcFlowPlus.CONTAINER_CODE = Allocation.CONTAINER_CODE

    left join (select distinct
                   ProcFlowPlus.Sup_No,
                   ProcFlowPlus.CONTAINER_CODE,
                   BuckStat.CONTAINER_TYPE_QTY as SIE
               from (select distinct
                        ProcFlow.PROC_FLO_ID,
                        ContTrans.CONTAINER_TYPE_ID,
                        ContTrans.CONTAINER_CODE,
                        SupLookup.Sup_No,
                        ProcFlow.PROC_FLO_DESC
                     from (select distinct
                              ProcFlow.SUPPLIER_ID,
                              ProcFlow.PROC_FLO_ID,
                              ProcFlow.PROC_FLO_DESC,
                              ProcFlow.PROC_FLO_NAME
                          from rcx.RXPRF1 ProcFlow
                          where PLANT_ID = '50000036') as ProcFlow
                     left join (select distinct
                                   SUPPLIER_ID,
                                   SUPPLIER_NO concat SUPPLIER_LOCATION as Sup_No
                                from rcx.RXPIR1 Suplookup
                                where PLANT_ID = '50000036'
                                ) as SupLookup on ProcFlow.SUPPLIER_ID = SupLookup.SUPPLIER_ID
                     left join (select distinct
                                   SupInfo.Supplier,
                                   suplookup.SUPPLIER_ID
                                from (select distinct
                                         SUPPLIER_ID,
                                        concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num
                                      from rcx.RXPIR1
                                      where COMPANY_CODE like('HMI%')
                                          and not SUPPLIER_NO in ('JN9999','272180','506776','081583','504880')
                                       ) as SupLookup
                                left join rcx.RXSIV1 SupInfo on SupLookup.Sup_Num = SupInfo.SUPPLIER
                                where COMPANY_CODE like('HMI%')
                                ) as SupName on SupLookup.SUPPLIER_ID = SupName.SUPPLIER_ID
                     left join rcx.RXPFC1 flowcont on ProcFlow.PROC_FLO_ID = flowcont.PROC_FLO_ID 
                     left join rcx.RXCTY1 ContTrans on flowcont.CONTAINER_TYPE_ID = ContTrans.CONTAINER_TYPE_ID
                     where not ProcFlow.PROC_FLO_NAME like '%RPR%'
                         and (right(ProcFlow.PROC_FLO_DESC, 5) like ('IHMI%') or right(ProcFlow.PROC_FLO_DESC, 5) like ('%IOSL%'))) as ProcFlowPlus

                     left join rcx.RXRLN1 buckets on ProcFlowPlus.PROC_FLO_ID = buckets.PROC_FLO_ID
                     left join rcx.RXBKT1 BucketNames on buckets.TO_BUCKET_ID = BucketNames.BUCKET_ID
                     left join rcx.RXBTC1 BuckStat on buckets.TO_BUCKET_ID = BuckStat.BUCKET_ID

                     where ProcFlowPlus.Sup_No is not null
                        and BuckStat.CONTAINER_TYPE_ID = ProcFlowPlus.CONTAINER_TYPE_ID
                        and BucketNames.BUCKET_NAME like '%SIE%'
                     ) as SIE_Bucket on ProcFlowPlus.Sup_No = SIE_Bucket.Sup_No and ProcFlowPlus.CONTAINER_CODE = SIE_Bucket.CONTAINER_CODE

    left join rcx.RXRLN1 buckets on ProcFlowPlus.PROC_FLO_ID = buckets.PROC_FLO_ID
    left join rcx.RXBKT1 BucketNames on buckets.TO_BUCKET_ID = BucketNames.BUCKET_ID
    left join rcx.RXBTC1 BuckStat on buckets.TO_BUCKET_ID = BuckStat.BUCKET_ID

    where ProcFlowPlus.Supplier_name is not null
        and BuckStat.CONTAINER_TYPE_ID = ProcFlowPlus.CONTAINER_TYPE_ID
        and BucketNames.BUCKET_NAME like '%SOH%'
        and Allocation.ALLOC_QTY is not null

    order by 
        ProcFlowPlus.Sup_No asC,
        ProcFlowPlus.CONTAINER_CODE asc
) 

select Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", 
       LISTAGG(Ship_from, ', ') within group (order by Ship_from) as Ship_from
from X
group by Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"

-EDIT-

Updated code to use LISTAGG function as @P.Vernon suggested. This results in error on the keyword Group (illegal use of keyword) in the LISTAGG's Within Group clause. If I remove the LISTAGG function and replace it with just Ship_from or remove Ship_from entirely, the query runs as expected.

Add some sample table data and the expected result - as formatted text, not images. (Take a look at https://stackoverflow.com/help/mcve before you start.) — jarlh, Sep 19 '18 at 14:24
You can use the DB2 LISTAGG Function... https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_bif_listagg.html — Nifriz, Sep 19 '18 at 14:43
@Nifriz When I try to use the LISTAGG function, it gets hung up on the `Group` keyword in `LISTAGG(distinct ProcFlowPlus.PROC_FLO_DESC, ', ') within group (order by ProcFlowPlus.PROC_FLO_DESC) as Ship_From` saying that it was expecting `From` or `Into` — 110SidedHexagon, Sep 19 '18 at 15:16
What DB2 version are you using. Use `LISTAGG` if it is available. Do you really need to use `distinct` on every sub-select - don't any of your tables have PKs or Uniuqe indexes? — Paul Vernon, Sep 19 '18 at 16:10
@P.Vernon I'm using version 9.7.5, so it should work, but I'm having the issue I stated before. As for distinct, it is needed for most of our tables as QMF will time out without it. — 110SidedHexagon, Sep 19 '18 at 19:00
`DISTINCT` as a keyword within `LISTAGG` only came in with Db2 11.1, so you would have to remove it in 9.7.5 — Paul Vernon, Sep 19 '18 at 19:59
also `ProcFlowPlus.PROC_FLO_DESC` is not a column of the `X` CTE. Maybe post your full query using LISTAGG as an edit to your question if you are still having trouble. My answer below would be the way I would think you need to use LISTAGG .. i.e. against your `X` CTE. It should work in 9.7 if you use your CTE and not my `values` test data — Paul Vernon, Sep 19 '18 at 20:03

score 0 · Answer 1 · answered Sep 19 '18 at 16:16

Use LISTAGG not recursion to aggregate strings. E.g.

with X(Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", Ship_from, curr, prev) as 
( values (1,1,1,1,1,1,1,1,1,1,'IOSL1',1,1)
        ,(1,1,1,1,1,1,1,1,1,1,'IHMI1',1,1)
        ,(1,1,1,1,1,1,1,1,1,1,'IHMI2',1,1))
select Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"
,      LISTAGG(Ship_from, ', ') within group (order by Ship_from DESC) as Ship_from
from X
group by Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"

will return

 SUP_NO    SUPPLIER_NAME   CONTAINER_CODE  ALLOC_QTY   SAFE_STOCK_QTY  TOT_ALLOC_REQ_QTY   SOH     SIE     DISC_QTY    %_Fill  SHIP_FROM
 ------     -------------   --------------  ---------   --------------  -----------------   ---     ---     --------    ------  -------------------
      1                 1                1          1                1                  1     1       1            1         1  IOSL1, IHMI2, IHMI1

Error when trying to combine field in DB2 SQL

1 Answers1