Jasperserver does not recognize the query

[erikmarbad]

I am using jasperserver 8.0.0 and in my report I have the following query that works for me in jasperstudio but not in jasperserver, I already verified that the problem is in the query but I do not understand the reason why it does not recognize it

WITH FACTURA AS (SELECT   DISTINCT SIH.IHDOCN FACTURA, RCM.CNME NOMCLI, SIH.SIINVD FECHA  FROM       $P!{AMB}.SIH SIH JOIN  $P!{AMB}.RCM RCM ON RCM.CCUST = SIH.SICUST  WHERE SUBSTR(SIH.SIINVD,1,6) >= CASE WHEN ('$P!{FECHA_ANO_MES}' = '' OR '$P!{FECHA_ANO_MES}' = 'null')                                       THEN 0                                       ELSE CASE WHEN LENGTH('$P!{FECHA_ANO_MES}') = 10                                                 THEN REPLACE(SUBSTR('$P!{FECHA_ANO_MES}', 1, 7), '-', '')                                                 ELSE '$P!{FECHA_ANO_MES}' END END  AND SIH.IHDOCN LIKE CASE WHEN ('$P!{FAC}' = '' OR '$P!{FAC}' = 'null')                           THEN ('%')                           ELSE '$P!{FAC}%' END  AND SIH.IHDPFX = 'EX'  AND  IHDTYP = '1'  )SELECT FAC.FACTURA, FAC.NOMCLI NOMCLI,SUM(CASE WHEN OG.GRUPO = 'OLMALM' THEN LES.ESAMT ELSE 0 END) ALMACENAJE,SUM(CASE WHEN OG.GRUPO = 'OLMCARGUE' THEN LES.ESAMT ELSE 0 END) CARGUE,SUM(CASE WHEN OG.GRUPO = 'OLMCARTAGE' THEN LES.ESAMT ELSE 0 END) OLMCARTAGE,SUM(CASE WHEN OG.GRUPO = 'OLMCOTECNA' THEN LES.ESAMT ELSE 0 END) COTECNA,SUM(CASE WHEN OG.GRUPO = 'OLMCUTTOFF' THEN LES.ESAMT ELSE 0 END) CUTTOFF,SUM(CASE WHEN OG.GRUPO = 'OLMDOCTRA' THEN LES.ESAMT ELSE 0 END) OLMDOCFEE,SUM(CASE WHEN OG.GRUPO  = 'OLMICA' THEN LES.ESAMT ELSE 0 END) ICA,SUM(CASE WHEN OG.GRUPO = 'OLMLLENA' THEN LES.ESAMT ELSE 0 END) LLENACO,SUM(CASE WHEN OG.GRUPO  = 'OLMMOVINSP' THEN LES.ESAMT ELSE 0 END) MOVINSP,SUM(CASE WHEN OG.GRUPO = 'OLMOPEPOR' THEN LES.ESAMT ELSE 0 END) OPEPORT,SUM(CASE WHEN OG.GRUPO = 'OLMREFR' THEN LES.ESAMT ELSE 0 END) REFRI,SUM(CASE WHEN OG.GRUPO = 'SELLO' THEN LES.ESAMT ELSE 0 END) SELLO,SUM(CASE WHEN OG.GRUPO = 'OLMSIA' THEN LES.ESAMT ELSE 0 END) SIA,SUM(CASE WHEN OG.GRUPO = 'OLMSORTING' THEN LES.ESAMT ELSE 0 END) SORTING,SUM(CASE WHEN OG.GRUPO = 'OLMSTANDBY' THEN LES.ESAMT ELSE 0 END) OLMSTANDBY,SUM(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESAMT ELSE 0 END) TRAMAR,MAX(CASE WHEN OG.GRUPO = 'OLMTRAMAR' THEN LES.ESCURR ELSE '' END) MNDTRAMAR,SUM(CASE WHEN OG.GRUPO = 'OLMTRAEREO' THEN LES.ESAMT ELSE 0 END) TRAEREO,SUM(CASE WHEN OG.GRUPO = 'OLMTR' THEN LES.ESAMT ELSE 0 END) TRASTER,SUM(CASE WHEN OG.GRUPO = 'OLMUSOINST' THEN LES.ESAMT ELSE 0 END) OLMUSOINST,SUM(CASE WHEN OG.GRUPO = 'OLMPESAJE' THEN LES.ESAMT ELSE 0 END) OLMPESAJE,SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) SERV_LOGISTICO,SUM(CASE WHEN OG.GRUPO = 'OLMCANCEL' THEN LES.ESAMT ELSE 0 END) OLMCANCEL,SUM(CASE WHEN OG.GRUPO = 'OLMCOMODAT' THEN LES.ESAMT ELSE 0 END) OLMCOMODAT,SUM(CASE WHEN OG.GRUPO = 'OLMCONTEN' THEN LES.ESAMT ELSE 0 END) OLMCONTEN,SUM(CASE WHEN OG.GRUPO = 'OLMNOINT' THEN LES.ESAMT ELSE 0 END) OLMNOINT,SUM(CASE WHEN OG.GRUPO = 'OLMSERD' THEN LES.ESAMT ELSE 0 END)  OLMSERD,SUM(CASE WHEN OG.GRUPO = 'OLMTTERDEV' THEN LES.ESAMT ELSE 0 END) OLMTTERDEV,SUM(CASE WHEN OG.GRUPO = 'OLMCOURIER' THEN LES.ESAMT ELSE 0 END) OLMCOURIER,SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN' THEN LES.ESAMT ELSE 0 END) OLMSELLEN1,SUM(CASE WHEN OG.GRUPO = 'OLMSELLEN2' THEN LES.ESAMT ELSE 0 END) OLMSELLEN2,FAC.FECHAFROM  FACTURA  FACLEFT  OUTER JOIN $P!{AMB}.LLH LLH ON FAC.FACTURA = LLH.LHCINVLEFT OUTER JOIN $P!{AMB}.LES LES ON LLH.LHLOAD = LES.ESLOAD AND LLH.LHORDN = LES.ESORDNLEFT OUTER JOIN ERPLXFU.VW_OLM_GRUPOS OG ON LES.ESSCCD = OG.COD_OLMGROUP BY FAC.FACTURA, FAC.NOMCLI, FAC.FECHAORDER BY FAC.FACTURA

[gustavofarias]

With default server settings you can't use a query starting with  "WITH FACTURA AS (".

You either change the query not to use WITH AS or you change jasper server settings (there is lot's of answers about it) to be less restrictive.

 

[rmeadows]

You are hitting sql validation in the product.   You have to either turn it off..or turn on and relax the regex for it.  

/WEB-INF/classes/esapi

 

Thanks.