DB2 using SUM function causes SQL Error [S1000]: General error-CodePudding

I am trying to gather sales data for a project and I'm having an issue with a query that I'm using as a cte in my main query.

SELECT
    CONCAT(
      CONCAT(
       VARCHAR_FORMAT(INFO.DATE, 'YYYYMMDD'),
       CAST(INFO.INVOICE AS VARCHAR)
      ), 
      CAST(INFO.STORE AS VARCHAR)
    ) AS HEADER,
    CASE WHEN INFO.CUSTOMER_NUMBER IN (999990, 999991) 
      THEN 'CASH' ELSE 'CHARGE' END AS CUST_TYPE,
    INFO.STORE,
    INFO.INVOICE,
    INFO.DATE,
    INFO.SALES_UNITS,
    INFO.SALES
FROM
    DATALAKE.SLSINFO INFO
WHERE 
    INFO.DATE >= '2019-01-01'
    AND INFO.STORE = 163
    AND INFO.INVOICE = 100349
ORDER BY 
    HEADER ASC

This query is supposed to create a unique header then use the customer number to classify the invoice as a cash customer or a charge customer and return some associated values.

In this case, I'm looking at a specific date/store/invoice for testing, and the results are:

HEADER	CUST_TYPE	STORE	INVOICE	DATE	SALES_UNITS	SALES
20190101100349163	CASH	163	100349	2019-01-01	4	9.96
20190101100349163	CASH	163	100349	2019-01-01	1	10.99
20190101100349163	CASH	163	100349	2019-01-01	1	2.99

I'm trying to create one row per unique header by adding the SALES_UNITS and SALES columns up. The query I'm trying to use is this:

SELECT
    CONCAT(CONCAT(VARCHAR_FORMAT(INFO.DATE, 'YYYYMMDD'), CAST(INFO.INVOICE AS VARCHAR)), CAST(INFO.STORE AS VARCHAR)) AS HEADER,
    CASE WHEN INFO.CUSTOMER_NUMBER IN (999990, 999991) THEN 'CASH' ELSE 'CHARGE' END AS CUST_TYPE,
    INFO.STORE,
    INFO.INVOICE,
    INFO.DATE,
    SUM(INFO.SALES_UNITS) AS UNITS,
    SUM(INFO.SALES) AS SALES
FROM
    DATALAKE.SLSINFO INFO
WHERE 
    INFO.DATE >= '2019-01-01'
    AND INFO.STORE = 163
    AND INFO.INVOICE = 100349
ORDER BY 
    HEADER ASC

The ideal output would look like this

HEADER	CUST_TYPE	STORE	INVOICE	DATE	UNITS	SALES
20190101100349163	CASH	163	100349	2019-01-01	6	23.94

However this second query doesn't run and I receive SQL Error [S1000]: General Error with no additional information.

I've tried grouping by header in both the original and second query and it doesn't let me group by anything, I haven't figured out why that is yet.

Any insight would be greatly appreciated!

CodePudding user response：

You could use the with clause to give your original query a name (I called it T, then just get your sums and group by the other columns.

      With T as (
  SELECT 
    CONCAT(
      CONCAT(
        VARCHAR_FORMAT(INFO.DATE, 'YYYYMMDD'), 
        CAST(INFO.INVOICE AS VARCHAR)
      ), 
      CAST(INFO.STORE AS VARCHAR)
    ) AS HEADER, 
    CASE WHEN INFO.CUSTOMER_NUMBER IN (999990, 999991) THEN 'CASH' ELSE 'CHARGE' END AS CUST_TYPE, 
    INFO.STORE, 
    INFO.INVOICE, 
    INFO.DATE, 
    INFO.SALES_UNITS, 
    INFO.SALES 
  FROM 
    DATALAKE.SLSINFO INFO 
  WHERE 
    INFO.DATE >= '2019-01-01' 
    AND INFO.STORE = 163 
    AND INFO.INVOICE = 100349
) 
select 
  Header, 
  cust_type, 
  store, 
  invoice, 
  date, 
  SUM(SALES_UNITS) AS UNITS, 
  SUM(SALES) AS SALES 
from 
  t 
group by 
  Header, 
  cust_type, 
  store, 
  invoice, 
  date 
order by 
  Header