SQL Validation Issue

[alanm]

Hi

validation.properties I have set: Validator.ValidSQL=(?is)^\s*(select|call|with|With|exec)\b((?!\binto\b)[^;])*;?\s*$

But the following still returns an error and won't run:

Invalid SQL:An error has occurred. Please contact your system administrator. (6632), SQL: With LogRecords as (select  PROTOCOL.PROTID,PROTMESSAGEID,PROTUSERNAME,PROTMESSAGEDATE,PROTMESSAGETEXT,PROTFCTDATE
    from protocol inner join prot_message on protocol.protid = prot_message.protid
    where protmessagedate between trunc(current_date - ?) and trunc(current_date) and protactionid = 21500
    )
Select PROTMESSAGEID, PROTUSERNAME, PROTMESSAGEDATE, PROTMESSAGETEXT, EVENTGROUP, ERROR, TOTALERRORS, LOCATION,DEVICE
    ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%'
        AND (LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%SIZE ADJUSTED%'
         OR LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%FILE TRUNCATED%'
         OR LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%ENTRY WILL BE ERASED%')
    THEN 1 ELSE 0 END AS FILESIZEADJUSTED
   ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%' AND UPPER(PROTMESSAGETEXT) NOT LIKE '%.TMP'
        AND LEAD(UPPER(PROTMESSAGETEXT),1) OVER (PARTITION BY DEVICE ORDER BY PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID) LIKE '%CROSSLINKED%'
       THEN 1 ELSE 0 END AS FILESCROSSLINKED
 ,CASE WHEN TOTALERRORS >=  ?  THEN 1 ELSE 0 END ERRORTRIGGERED
From (
    select PROTMESSAGEID, PROTUSERNAME,PROTMESSAGEDATE,PROTMESSAGETEXT
    ,ProtFctDate as EventGroup
    ,CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA/%' THEN 1 ELSE 0 END AS ERROR
    ,SUM(CASE WHEN UPPER(PROTMESSAGETEXT) LIKE '/RFA%' THEN 1 ELSE 0 END) OVER (Partition by ProtUserName) as TOTALERRORS
    ,SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-V') + 2,INSTR(PROTUSERNAME,'-D') - INSTR(PROTUSERNAME,'-V')-2)  AS LOCATION
    ,SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-D') + 2,INSTR(PROTUSERNAME,'-M') - INSTR(PROTUSERNAME,'-D')-2)  AS DEVICE
    from LogRecords
    where SUBSTR(PROTUSERNAME,INSTR(PROTUSERNAME,'-D') + 2,INSTR(PROTUSERNAME,'-M') - INSTR(PROTUSERNAME,'-D')-2) like '%' || ?
      and PROTID in (Select Max(PROTID) over (Partition By ProtUserName,ProtFCTDate) From LogRecords))
    order by PROTUSERNAME,PROTMESSAGEDATE, PROTMESSAGEID

Any thoughts

Thanks

Al.

[alanm]

This is for Jasper Server 7.5. The report runs fine in Jaspersoft Studio 6.11

[alanm]

Fixed. I had forgotten to uncomment the line: sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context

in the security.properties file

[djohnson53]

Fixed. I had forgotten to uncomment the line: sqlQueryExecutor=Alpha,ValidSQL,500000,true,SQL_Query_Executor_context

in the security.properties file

alanm - 13 hours 39 min ago