Why is my query failing with SQLCODE = -420?

Question

Can I get some help with this? I'll copy the code as well as the error. I looked up the error code

(DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=BOOLEAN)

but I'm not understanding what doesn't meet the requirements of the function? I'm just trying to pull these tables, rename ACH_ORIGINATION.DESCRIPTION when it meets certain requirements, and then filter out the ones that don't meet the requirements. I also want to group by DESCRIPTION but I'd like to get this portion working first.

SELECT
    ACH_ORIGINATION.COMPANY_SERIAL,
    ACH_ORIGINATION.NAME,
    ACH_ORIGINATION.AMOUNT,
    ACH_ORIGINATION.NEXT_POSTING_DATE,
    CASE ACH_ORIGINATION.DESCRIPTION
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%J & J%' THEN 'J & J Electric'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%DeCleene%' THEN 'DeCleene'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Arab%' THEN 'Arab'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Clifford%' THEN 'Clifford Signs'
        ELSE 'Nothing'
    END AS DESCRIPTION
FROM
    CORE.ACH_ORIGINATION AS ACH_ORIGINATION
WHERE
    ACH_ORIGINATION.NEXT_POSTING_DATE IS NOT NULL AND
    DESCRIPTION <> 'Nothing'

Error:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ACH32Origination32Report_TableDataset_1579792666108_134237 at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:466) at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:441) at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:333) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54) Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ACH32Origination32Report_TableDataset_1579792666108_134237 at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:809) at net.sf.jasperreports.components.table.fill.FillTableSubreport.prepareSubreport(FillTableSubreport.java:156) at net.sf.jasperreports.components.table.fill.FillTable.prepare(FillTable.java:400) at net.sf.jasperreports.engine.fill.JRFillComponentElement.prepare(JRFillComponentElement.java:151) at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:332) at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:384) at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:358) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1102) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1065) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:329) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:159) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:963) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120) at java.lang.Thread.run(Unknown Source) Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ACH32Origination32Report_TableDataset_1579792666108_134237 at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1114) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691) at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1314) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:931) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:873) at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:665) at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59) at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:203) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ... 1 more Caused by: com.ibm.db2.jcc.am.SqlDataException: DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=BOOLEAN, DRIVER=4.13.127 at com.ibm.db2.jcc.am.id.a(id.java:669) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:127) at com.ibm.db2.jcc.am.bo.b(bo.java:4101) at com.ibm.db2.jcc.am.bo.a(bo.java:4083) at com.ibm.db2.jcc.t4.cb.a(cb.java:835) at com.ibm.db2.jcc.t4.cb.n(cb.java:801) at com.ibm.db2.jcc.t4.cb.j(cb.java:253) at com.ibm.db2.jcc.t4.cb.d(cb.java:55) at com.ibm.db2.jcc.t4.q.c(q.java:44) at com.ibm.db2.jcc.t4.sb.j(sb.java:147) at com.ibm.db2.jcc.am.no.kb(no.java:2112) at com.ibm.db2.jcc.am.oo.b(oo.java:4407) at com.ibm.db2.jcc.am.oo.fc(oo.java:743) at com.ibm.db2.jcc.am.oo.executeQuery(oo.java:713) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233) ... 11 more

You are mixing syntaxes, either `case colum when value` or `case when condition` — nfgl, Jan 23 '20 at 15:35
Thanks for the comments, I understand where the error was now. — cmurphy, Jan 23 '20 at 18:24

score 2 · Accepted Answer · answered Jan 23 '20 at 15:38

2

Per comments, fix the CASE expression like this:

CASE    WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%J & J%' THEN 'J & J Electric'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%DeCleene%' THEN 'DeCleene'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Arab%' THEN 'Arab'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Clifford%' THEN 'Clifford Signs'
        ELSE 'Nothing'
END AS DESCRIPTION

answered Jan 23 '20 at 15:38

mao

11,321
2
13
29

Scratch my previous comment about them all being labeled as 'Nothing' - it seems to be working properly now, however, I am getting an error now that I'm trying to group by the DESCRIPTION. All I did was add GROUP BY DESCRIPTION after the WHERE clause and now I'm getting DB2 SQL Error: SQLCODE= -119, SQLSTATE=42803, SQLERRMC=NEXT_POSTING_DATE, DRIVER=3.58.82. – cmurphy Jan 23 '20 at 18:35

score 0 · Answer 2 · answered Jan 24 '20 at 05:56

Actually, it's better to post a new question, if your original problem is resolved, and now you have a new problem...
Multiple problems with GROUP BY. You may run the query below to play with expressions.

1) You must use some aggregate function on a column which is not presented in the GROUP BY list, but used in the SELECT list. If you uncomment the 1-st line commented out, and comment out the next line (with MAX function), you get SQLCODE=-119.

2) Unanticipated / wrong expression in the GROUP BY clause.
You should use the same case expression in the group by clause, and not the original DESCRIPTION column. If you uncomment commented out --DESCRIPTION line and comment out the CASE expression in the GROUP BY list, you may "suddenly" realize, that you get 2 groups with the same value in the DESCRIPTION column. This is because of you group by the original value in DESCRIPTION first and process the group value in the CASE expression afterwards.

SELECT
    --ACH_ORIGINATION.NEXT_POSTING_DATE,
    MAX(ACH_ORIGINATION.NEXT_POSTING_DATE) AS NEXT_POSTING_DATE,
    CASE 
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%J & J%' THEN 'J & J Electric'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%DeCleene%' THEN 'DeCleene'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Arab%' THEN 'Arab'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Clifford%' THEN 'Clifford Signs'
        ELSE 'Nothing'
    END AS DESCRIPTION
, COUNT(1) CNT    
FROM 
(
VALUES 
  (CURRENT DATE - 1, 'Arab1') 
, (CURRENT DATE - 2, 'Arab2')  
) ACH_ORIGINATION (NEXT_POSTING_DATE, DESCRIPTION)
WHERE
    ACH_ORIGINATION.NEXT_POSTING_DATE IS NOT NULL AND
    DESCRIPTION <> 'Nothing'
GROUP BY 
--DESCRIPTION
    CASE 
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%J & J%' THEN 'J & J Electric'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%DeCleene%' THEN 'DeCleene'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Arab%' THEN 'Arab'
        WHEN ACH_ORIGINATION.DESCRIPTION LIKE '%Clifford%' THEN 'Clifford Signs'
        ELSE 'Nothing'
    END
;

Why is my query failing with SQLCODE = -420?

2 Answers2