AnsweredAssumed Answered

Need Help Converting SQL to NSQL

Question asked by juan.segovia on May 7, 2012
Latest reply on May 8, 2012 by Murali_K
Hi, I'm having issues with a SQL Statement trying to make it work with NSQL.

My SQL reads as follows:

select emp.NAME, COUNT(*) as cantidad from niku.INV_INVESTMENTS as inv
inner join niku.ODF_CA_INV as ca on inv.ID=ca.ID
inner join niku.CMN_LOOKUPS_V as emp on o_empresa=emp.LOOKUP_CODE
where inv.IS_ACTIVE=1 and o_empresa is not NULL and ca.o_procesos='o_gdm' and ca.o_procesos is not null
and emp.LOOKUP_TYPE= 'o_lookup_empresa' and emp.LANGUAGE_CODE='es'
group by o_empresa,emp.name;

This SQL works fine and gives me the results I need. But when I convert this to NSQL (not my area at all) I can make the syntax pass the validation but no results are coming back.

This is my NSQL:

select
@SELECT:DIM:USER_DEF:IMPLIED:REQ_EMP:emp.name:emp@,
@SELECT:METRIC:USER_DEF:IMPLIED:count(*):Cantidad:agg@
from niku.INV_INVESTMENTS inv
inner join niku.ODF_CA_INV ca on inv.ID=ca.ID
inner join niku.CMN_LOOKUPS_V emp on o_empresa=emp.LOOKUP_CODE
where inv.IS_ACTIVE=1 and o_empresa is not NULL and ca.o_procesos='o_gdm' and ca.o_procesos is not null
and emp.LOOKUP_TYPE= 'o_lookup_empresa' and emp.LANGUAGE_CODE='es'
and (@WHERE:PARAM:USER_DEF:STRING:TYPE_CONSTRAIN@ = o_plan_semestral)
and @FILTER@
group by o_empresa,emp.name
HAVING @HAVING_FILTER@

I did this because I need to be able to filter by the attribute o_plan_semestral.

Again, I'm not good with NSQL so I will really appreciate any suggestion/modification you can make to it so it works as my SQL.

Thanks,

Juan

Outcomes