AnsweredAssumed Answered

NSQL Query assistance

Question asked by juan.segovia on Feb 18, 2013
Latest reply on Feb 21, 2013 by another_martink
Hi, I'm trying to convert this perfectly working SQL query:

[color=#57236d]Select
inv.id, prj.osde_id_gdm, inv.name, inv.MANAGER_ID, ocp.osde_asigna_gmp, ocp.osde_resp_sistemas, prior_status, current_status, update_date, how_long

from niku.inv_investments inv,
niku.ODF_CA_INV ocp,
niku.ODF_CA_PROJECT prj,

(select A1.object_id, A1.raw_value_after current_status, A1.raw_value_before prior_status, A1.last_updated_date update_date,
datediff( dd , isnull(( select max(created_date) from niku.cmn_audits A2
where A2.object_id = A1.object_id
and A2.object_code = A1.object_code
AND a2.attribute_code = a1.attribute_code
and A2. created_date < A1.created_date ),(select created_date from niku.odf_ca_inv where id=A1.object_id)),created_date) how_long

from niku.cmn_audits A1,
(select ID from niku.cmn_audits where table_name = 'odf_ca_inv' and attribute_code = 'osde_estado_req') lc where A1.id = lc.id) StatusDays

where inv.id = ocp.id
and inv.id = prj.id
and inv.id = StatusDays.object_id
and inv.is_active = 1
order by ID, update_date asc;
[color]

To Clarity NSQL. So far I tried with this query:

[color=#b90d2a][color]select
@SELECT:DIM:USER_DEF:IMPLIED:IDREQ:id_REQ:id_REQ@,
@SELECT:METRIC:USER_DEF:IMPLIED:sumario:sumario@,
@SELECT:METRIC:USER_DEF:IMPLIED:responsable:responsable@,
@SELECT:METRIC:USER_DEF:IMPLIED:asignadogmp:asignadogmp@,
@SELECT:METRIC:USER_DEF:IMPLIED:sistemas:sistemas@,
@SELECT:METRIC:USER_DEF:IMPLIED:prior_status:prior_status@,
@SELECT:METRIC:USER_DEF:IMPLIED:current_status:current_status@,
@SELECT:METRIC:USER_DEF:IMPLIED:update_date:update_date@,
@SELECT:METRIC:USER_DEF:IMPLIED:how_long:how_long@

from(
Select id_REQ, sumario, responsable, asignadogmp, sistemas, prior_status, current_status, update_date, how_long

from (
select prj.osde_id_gdm as id_REQ, inv.NAME as sumario,
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) as responsable,
(select FULL_NAME from niku.SRM_RESOURCES as sis where ocp.osde_asigna_gmp=sis.ID) as asignadogmp,
(select FULL_NAME from niku.SRM_RESOURCES as sis where ocp.osde_resp_sistemas=sis.ID) as sistemas,

from niku.inv_investments inv,
niku.ODF_CA_INV ocp,
niku.ODF_CA_PROJECT prj,

(select A1.object_id, A1.raw_value_after current_status, A1.raw_value_before prior_status, A1.last_updated_date update_date,
datediff( dd , isnull(( select max(created_date) from niku.cmn_audits A2
where A2.object_id = A1.object_id
and A2.object_code = A1.object_code
AND a2.attribute_code = a1.attribute_code
and A2. created_date < A1.created_date ),(select created_date from niku.odf_ca_inv where id=A1.object_id)),created_date) how_long
from niku.cmn_audits A1,
(select ID from niku.cmn_audits where table_name = 'odf_ca_inv' and attribute_code = 'osde_estado_req') lc where A1.id = lc.id) StatusDays

where inv.id = ocp.id
and inv.id = prj.id
and inv.id = StatusDays.object_id
and inv.is_active = 1
) as subq
)T
and @FILTER@
order by ID, update_date asc;
having @HAVING_FILTER@


But I'm getting this error: Incorrect syntax near the keyword 'from'. I tried my best but still I cannot get it to validate.

Can anyone please tell me where is my mistake here?

Thank you so much in advance.

Juan

Outcomes