AnsweredAssumed Answered

SQL GETDATE on Gel Script

Question asked by juan.segovia on May 7, 2013
Latest reply on May 14, 2013 by Owen_R
Hi, I'm currently working on a custom process. The situation I'm facing is this:

I have one custom resprt that involves this query:

select
((alineamiento*criticidad*antiguedad)+( beneficio_cualitativo* beneficio_cuantitativo)) as valor
from (

cast((case when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >= 541 then 2
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >=361 and DATEDIFF("d",inv.CREATED_DATE,GETDATE())<=540 then 1.60
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >=181 and DATEDIFF("d",inv.CREATED_DATE,GETDATE())<=360 then 1.30
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) <= 180 then 1
end) as real)as antiguedad,
cast((select NAME from niku.CMN_LOOKUPS_V where LOOKUP_CODE=osde_valor_criticida and LOOKUP_TYPE= 'OSDE_LOOKUP_VALOR_CRITIC2' and LANGUAGE_CODE='es' ) as real)as criticidad,
cast(ca.osde_alineamien_matr as real)as alineamiento,
cast(ca.osde_calif_ben_cual as real) as beneficio_cualitativo,
cast((select NAME from niku.CMN_LOOKUPS_V where LOOKUP_CODE= osde_calif_bencuan and LOOKUP_TYPE= 'OSDE_CALIF_BENCUANTI' and LANGUAGE_CODE='es' ) as real ) as beneficio_cuantitativo

from niku.INV_INVESTMENTS as inv
inner join niku.ODF_CA_INV as ca on inv.ID=ca.ID
inner join niku.ODF_CA_PROJECT as proj on inv.ID=proj.id
where inv.IS_ACTIVE=1

This gives me a number as a result that I use on that report.

Now my customer is asking to get that same value on an actual Clarity attribute of the Invesment Object, so my first idea was to make a process with Gel Script to update the value of my field with this same query. That script looks like this:

<gel:script
xmlns:sql="jelly:sql"
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary">
<gel:setDataSource dbId="niku" var="dataSource"/>
<sql:update dataSource="${dataSource}">
UPDATE niku.ODF_CA_INV
set osde_valor = (select
((alineamiento*criticidad*antiguedad)+( beneficio_cualitativo* beneficio_cuantitativo)) as valor
from (

cast((case when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >= 541 then 2
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >=361 and DATEDIFF("d",inv.CREATED_DATE,GETDATE())<=540 then 1.60
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) >=181 and DATEDIFF("d",inv.CREATED_DATE,GETDATE())<=360 then 1.30
when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) <= 180 then 1
end) as real)as antiguedad,
cast((select NAME from niku.CMN_LOOKUPS_V where LOOKUP_CODE=osde_valor_criticida and LOOKUP_TYPE= 'OSDE_LOOKUP_VALOR_CRITIC2' and LANGUAGE_CODE='es' ) as real)as criticidad,
cast(ca.osde_alineamien_matr as real)as alineamiento,
cast(ca.osde_calif_ben_cual as real) as beneficio_cualitativo,
cast((select NAME from niku.CMN_LOOKUPS_V where LOOKUP_CODE= osde_calif_bencuan and LOOKUP_TYPE= 'OSDE_CALIF_BENCUANTI' and LANGUAGE_CODE='es' ) as real ) as beneficio_cuantitativo

from niku.INV_INVESTMENTS as inv
inner join niku.ODF_CA_INV as ca on inv.ID=ca.ID
inner join niku.ODF_CA_PROJECT as proj on inv.ID=proj.id
where inv.IS_ACTIVE=1
))
</sql:update>
</gel:script>

The problem I'm having is that the GEL is not validating due to errors on the when DATEDIFF("d",inv.CREATED_DATE,GETDATE()) .... Lines.

Is there any other way recommended to do this or any sintx change I should perform to be able to validate this?

Thanks in advance,

Juan.

Outcomes