Dear Friends
In the oracle apex 23.1, Created a Classic form as a dynamic field where I am using the following query . in which there are first 2 columns are company and bank_name , but the other 12 columns are dynamic . it's not necessary that all the 12 columns will have the data . these all 12 columns are number columns. I need to sum for that I used the following query which is not working , if it's having a static column then it's working
Currently using this query
RETURN q'~
SELECT *
FROM VIEW_BA_FF_PIVOTED
WHERE :P163_COMPANY IS NULL OR INSTR(':' || :P163_COMPANY || ':', ':' || company || ':') > 0
order by bank_name,company
~';
need the query something like this but this query is giving an error .
SELECT
company,
SUM(CASE WHEN col3 IS NOT NULL THEN col3 ELSE 0 END) AS sum_col3,
SUM(CASE WHEN col4 IS NOT NULL THEN col4 ELSE 0 END) AS sum_col4,
SUM(CASE WHEN col5 IS NOT NULL THEN col5 ELSE 0 END) AS sum_col5,
SUM(CASE WHEN col6 IS NOT NULL THEN col6 ELSE 0 END) AS sum_col6,
SUM(CASE WHEN col7 IS NOT NULL THEN col7 ELSE 0 END) AS sum_col7,
SUM(CASE WHEN col8 IS NOT NULL THEN col8 ELSE 0 END) AS sum_col8,
SUM(CASE WHEN col9 IS NOT NULL THEN col9 ELSE 0 END) AS sum_col9,
SUM(CASE WHEN col10 IS NOT NULL THEN col10 ELSE 0 END) AS sum_col10,
SUM(CASE WHEN col11 IS NOT NULL THEN col11 ELSE 0 END) AS sum_col11,
SUM(CASE WHEN col12 IS NOT NULL THEN col12 ELSE 0 END) AS sum_col12,
SUM(CASE WHEN col13 IS NOT NULL THEN col13 ELSE 0 END) AS sum_col13,
SUM(CASE WHEN col14 IS NOT NULL THEN col14 ELSE 0 END) AS sum_col14
FROM
(
SELECT
bank_name AS bank,
company,
SUM(CASE WHEN col3 IS NOT NULL THEN col3 ELSE 0 END) AS sum_col3,
SUM(CASE WHEN col4 IS NOT NULL THEN col4 ELSE 0 END) AS sum_col4,
SUM(CASE WHEN col5 IS NOT NULL THEN col5 ELSE 0 END) AS sum_col5,
SUM(CASE WHEN col6 IS NOT NULL THEN col6 ELSE 0 END) AS sum_col6,
SUM(CASE WHEN col7 IS NOT NULL THEN col7 ELSE 0 END) AS sum_col7,
SUM(CASE WHEN col8 IS NOT NULL THEN col8 ELSE 0 END) AS sum_col8,
SUM(CASE WHEN col9 IS NOT NULL THEN col9 ELSE 0 END) AS sum_col9,
SUM(CASE WHEN col10 IS NOT NULL THEN col10 ELSE 0 END) AS sum_col10,
SUM(CASE WHEN col11 IS NOT NULL THEN col11 ELSE 0 END) AS sum_col11,
SUM(CASE WHEN col12 IS NOT NULL THEN col12 ELSE 0 END) AS sum_col12,
SUM(CASE WHEN col13 IS NOT NULL THEN col13 ELSE 0 END) AS sum_col13,
SUM(CASE WHEN col14 IS NOT NULL THEN col14 ELSE 0 END) AS sum_col14
FROM
VIEW\_BA\_FF\_PIVOTED
WHERE :P163\_COMPANY IS NULL OR INSTR(':' || :P163\_COMPANY || ':', ':' || company || ':') > 0
GROUP BY
bank\_name, company
) t
GROUP BY
company
sandy