Summary Row for a SQL HANA Query Error

[juzersm1990]

Dear Experts,

I am trying to update an SQL HANA query to include the summary row for a couple of columns.  I am using the UNION ALL method to get the summary, individually the two queries are working fine, but when executed together I get an below error.

Below query::

SELECT T2."Indicator",T0."DocNum",T0."TaxDate" "Doc Date",DAYS_BETWEEN(T0."TaxDate",CURRENT_DATE) "No. Of Days",T0."CardName", T0."NumAtCard",
cast(T3."InsTotal" as integer) AS "Total Amount",cast(T3."InsTotal"-T3."PaidToDate" as integer) AS "Open Amount", T1."SlpName" 
FROM ORDR T0 
INNER JOIN RDR6 T3 ON T0."DocEntry"=T3."DocEntry" 
INNER JOIN OSLP T1 ON T0."SlpCode" = T1."SlpCode" 
INNER JOIN OPID T2 ON T0."PIndicator" = T2."Indicator" 
WHERE DAYS_BETWEEN(T0."TaxDate",CURRENT_DATE)>365 and T0."CANCELED"='N' and T0."DocStatus"='O' 
UNION ALL
SELECT '','','','','','Total',
sum(T3."InsTotal"),sum(T3."InsTotal"-T3."PaidToDate"),''
FROM ORDR T0 
INNER JOIN RDR6 T3 ON T0."DocEntry"=T3."DocEntry" 
INNER JOIN OSLP T1 ON T0."SlpCode" = T1."SlpCode" 
INNER JOIN OPID T2 ON T0."PIndicator" = T2."Indicator" 
WHERE DAYS_BETWEEN(T0."TaxDate",CURRENT_DATE)>365 and T0."CANCELED"='N' and T0."DocStatus"='O'

on executing i am getting the below error :

SAP DBTech JDBC: [339]: invalid number: Failed to parse constant

[MD1 · Accepted solution]

Hi,

Try this

SELECT
T2."Indicator",
T0."DocNum",
T0."TaxDate" AS "Doc Date",
DAYS_BETWEEN(T0."TaxDate", CURRENT_DATE) AS "No. Of Days",
T0."CardName",
T0."NumAtCard",
CAST(COALESCE(T3."InsTotal", 0) AS DECIMAL(15, 2)) AS "Total Amount",
CAST(COALESCE(T3."InsTotal", 0) - COALESCE(T3."PaidToDate", 0) AS DECIMAL(15, 2)) AS "Open Amount",
T1."SlpName"
FROM
ORDR T0
INNER JOIN RDR6 T3 ON T0."DocEntry" = T3."DocEntry"
INNER JOIN OSLP T1 ON T0."SlpCode" = T1."SlpCode"
INNER JOIN OPID T2 ON T0."PIndicator" = T2."Indicator"
WHERE
DAYS_BETWEEN(T0."TaxDate", CURRENT_DATE) > 365
AND T0."CANCELED" = 'N'
AND T0."DocStatus" = 'O'

UNION ALL

SELECT
'0',
'0',
'0',
'0',
'0',
'Total',
CAST(SUM(COALESCE(T3."InsTotal", 0)) AS DECIMAL(15, 2)),
CAST(SUM(COALESCE(T3."InsTotal", 0) - COALESCE(T3."PaidToDate", 0)) AS DECIMAL(15, 2)),
''
FROM
ORDR T0
INNER JOIN RDR6 T3 ON T0."DocEntry" = T3."DocEntry"
INNER JOIN OSLP T1 ON T0."SlpCode" = T1."SlpCode"
INNER JOIN OPID T2 ON T0."PIndicator" = T2."Indicator"
WHERE
DAYS_BETWEEN(T0."TaxDate", CURRENT_DATE) > 365
AND T0."CANCELED" = 'N'
AND T0."DocStatus" = 'O';

[juzersm1990 · Accepted solution]

Thanks for this, worked perfectly