AnsweredAssumed Answered

SQL query works in SQL Server, fails in Clarity. Why?

Question asked by Alex_Feldstein on Jan 24, 2011
Latest reply on Jan 24, 2011 by Alex_Feldstein
I am having process step failure on a query. The query works fine in SQL Server Management Console.

This is part of a new step I'm creating in an existing Audit process. It is suppossed to look for data inconsistencies and generate an email to the resource alerting them to the issue and giving them advise and on how to fix it.

The rule for this one is:
If you create an expense voucher against a project task, it has to be for one-and-only one resource, not multiples. This could happen rarely when they select a task assigned to a real person instead of a non-labor-resource (NLR) which are resource categories we set up for financial purposes (e.g. NLR-TRAVEL, NLR-PROFESSIONAL-FEES, etc.)

This is a simplified version of the query. I took out some of the extra columns we need for emailing purposes.

[font=Courier New]SELECT ppat.TRANSNO, COUNT(*) as knt
FROM PPA_TRANSCONTROL ppat
INNER JOIN prtask tsk ON tsk.prid = ppat.task_id
INNER JOIN srm_projects sp ON sp.id = tsk.prprojectid
INNER JOIN prassignment pra ON pra.prtaskid=tsk.prid
INNER JOIN srm_resources nlr_res ON nlr_res.id=pra.prresourceid
WHERE ppat.resource_code != nlr_res.unique_name
GROUP BY ppat.TRANSNO
HAVING COUNT(*) > 1[font]

It works fine in SQL Server. Inserted into a GEL script it validates, but crashes at runtime (excerpt):

[font=Courier New]BPM-0704: An error occurred while executing custom script: org.apache.commons.jelly.JellyTagException: null:46:30: <sql:query> SELECT ppat.TRANSNO, COUNT(*) as knt FROM PPA_TRANSCONTROL ppat INNER JOIN prtask tsk ON tsk.prid = ppat.task_id INNER JOIN srm_projects sp ON sp.id = tsk.prprojectid INNER JOIN prassignment pra ON pra.prtaskid=tsk.prid INNER JOIN srm_resources nlr_res ON nlr_res.id=pra.prresourceid WHERE ppat.resource_code != nlr_res.unique_name GROUP BY ppat.TRANSNO HAVING COUNT(*) &gt; 1: [CA Clarity][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ';'. at org.apache.commons.jelly.tags.sql.QueryTag.doTag(QueryTag.java:194)...[font]

The error says it fails syntax checking near ";". Where? There is no semi-colon. SQL Serrver processes the query just fine.

Any ideas?

Alex

Outcomes