AnsweredAssumed Answered

XOG - NSQL_SYNTAX_ERROR_UNKNOWN_CONSTRUCT

Question asked by hein.groenewald on May 4, 2012
Latest reply on May 27, 2013 by another_martink
Hi ...

As soon as I thought I have figured the whole XOG thing out, it turn around and bits me... again... :-)

We are trying to create a (relative easy) portlet, showing all the project dependencies.
The SQL statement works fine, and returns the values as expected.
The SQL statement is as follows:
select
PRJ_DEP.ID as Dependant_ID,
INV.ID as Prj_ID,
INV.NAME as Prj_Name,
CA_INV.ca_inv_projindicatio as Prj_Type_ID,
pt_Caps.name as Prj_Type,
CA_INV.ca_inv_projectoffice as Prj_Office,
INV.MANAGER_ID as Prj_Mngr_Ind,
(ProjManID.last_name + ', ' + ProjManID.first_name) as Prj_Mngr,
INV.SCHEDULE_FINISH as Prj_Fin_date,
INV_DEP.NAME as Dep_Prj_Name,
CA_INV_DEP.ca_inv_projectoffice as Dep_Prj_Office,
INV_DEP.MANAGER_ID as Dep_Prj_Mngr_Ind,
(ProjManIDDep.last_name + ', ' + ProjManIDDep.first_name) as Dep_Prj_Mngr,
INV_DEP.SCHEDULE_FINISH as Dep_Prj_Fin_date,
INV_DEP.PROGRESS as Dep_Prj_Progress_Ind,
Prgrs_Caps.name as Dep_Prj_Progress,
INV_DEP.Status as Dep_Prj_Status_Ind,
Stat_Caps.name as Dep_Prj_Status
from PRJ_PROJECT_DEPENDS PRJ_DEP
inner join INV_INVESTMENTS INV on PRJ_DEP.DEPENDENT_ID = INV.ID
inner join niku.odf_ca_project CA_PROJ on INV.id = CA_PROJ.id
inner join niku.odf_ca_inv CA_INV on CA_PROJ.id = CA_INV.id
inner join INV_INVESTMENTS INV_DEP on PRJ_DEP.PRINCIPAL_ID = INV_DEP.ID
inner join niku.odf_ca_project CA_PROJ_DEP on INV_DEP.id = CA_PROJ_DEP.id
inner join niku.odf_ca_inv CA_INV_DEP on CA_PROJ_DEP.id = CA_INV_DEP.id
--Project Type Lookup
left join niku.cmn_lookups pt_LU on CA_INV.ca_inv_projindicatio = pt_LU.lookup_code and pt_LU.lookup_type='CL_PROJINDICATION'
left join niku.cmn_captions_nls pt_Caps on pt_Caps.pk_id = pt_LU.id and pt_Caps.language_code='en' and pt_Caps.table_name = 'CMN_LOOKUPS'
--Progress Lookup
left join niku.cmn_lookups Prgrs_LU on inv.PROGRESS = Prgrs_LU.lookup_enum and Prgrs_LU.lookup_type='INVESTMENT_OBJ_PROGRESS'
left join niku.cmn_captions_nls Prgrs_Caps on Prgrs_Caps.pk_id = Prgrs_LU.id and Prgrs_Caps.language_code='en' and Prgrs_Caps.table_name = 'CMN_LOOKUPS'
--Status Lookup
left join niku.cmn_lookups Stat_LU on INV_DEP.Status = Stat_LU.lookup_enum and Stat_LU.lookup_type='INVESTMENT_OBJ_STATUS'
left join niku.cmn_captions_nls Stat_Caps on Stat_Caps.pk_id = Stat_LU.id and Stat_Caps.language_code='en' and Stat_Caps.table_name = 'CMN_LOOKUPS'
--User ID's Lookup
left join niku.cmn_sec_users ProjManID on INV.MANAGER_ID = ProjManID.id
left join niku.cmn_sec_users ProjManIDDep on INV_DEP.MANAGER_ID = ProjManIDDep.id

Embedded in NSQL it looks as follows:
[CDATA[select @select:dim:user_def:implied:project:Dependant_ID:Dependant_ID@,
@select:dim_prop:user_def:implied:project:Prj_Name,Prj_Name@,
@select:dim_prop:user_def:implied:project:Prj_Type_ID,Prj_Type_ID@,
@select:dim_prop:user_def:implied:project:Prj_Type,Prj_Type@,
@select:dim_prop:user_def:implied:project:Prj_Office,Prj_Office@,
@select:dim_prop:user_def:implied:project:Prj_Mngr_Ind,Prj_Mngr_Ind@,
@select:dim_prop:user_def:implied:project:Prj_Mngr,Prj_Mngr@,
@select:dim_prop:user_def:implied:project:Prj_Fin_date,Prj_Fin_date@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Name,Dep_Prj_Name@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Office,Dep_Prj_Office@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Mngr_Ind,Dep_Prj_Mngr_Ind@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Mngr,Dep_Prj_Mngr@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Fin_date,Dep_Prj_Fin_date@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Progress_Ind,Dep_Prj_Progress_Ind@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Progress,Dep_Prj_Progress@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Status_Ind,Dep_Prj_Status_Ind@,
@select:dim_prop:user_def:implied:project:Dep_Prj_Status,Dep_Prj_Status@
from (
select PRJ_DEP.ID as Dependant_ID,
INV.NAME as Prj_Name,
CA_INV.ca_inv_projindicatio as Prj_Type_ID,
pt_Caps.name as Prj_Type,
CA_INV.ca_inv_projectoffice as Prj_Office,
INV.MANAGER_ID as Prj_Mngr_Ind,
(ProjManID.last_name + ', ' + ProjManID.first_name) as Prj_Mngr,
INV.SCHEDULE_FINISH as Prj_Fin_date,
INV_DEP.NAME as Dep_Prj_Name,
CA_INV_DEP.ca_inv_projectoffice as Dep_Prj_Office,
INV_DEP.MANAGER_ID as Dep_Prj_Mngr_Ind,
(ProjManIDDep.last_name + ', ' + ProjManIDDep.first_name) as Dep_Prj_Mngr,
INV_DEP.SCHEDULE_FINISH as Dep_Prj_Fin_date,
INV_DEP.PROGRESS as Dep_Prj_Progress_Ind,
Prgrs_Caps.name as Dep_Prj_Progress,
INV_DEP.Status as Dep_Prj_Status_Ind,
Stat_Caps.name as Dep_Prj_Status
from PRJ_PROJECT_DEPENDS PRJ_DEP
inner join INV_INVESTMENTS INV on PRJ_DEP.DEPENDENT_ID = INV.ID
inner join niku.odf_ca_project CA_PROJ on INV.id = CA_PROJ.id
inner join niku.odf_ca_inv CA_INV on CA_PROJ.id = CA_INV.id
inner join INV_INVESTMENTS INV_DEP on PRJ_DEP.PRINCIPAL_ID = INV_DEP.ID
inner join niku.odf_ca_project CA_PROJ_DEP on INV_DEP.id = CA_PROJ_DEP.id
inner join niku.odf_ca_inv CA_INV_DEP on CA_PROJ_DEP.id = CA_INV_DEP.id
--Project Type Lookup
left join niku.cmn_lookups pt_LU on CA_INV.ca_inv_projindicatio = pt_LU.lookup_code and pt_LU.lookup_type='CL_PROJINDICATION'
left join niku.cmn_captions_nls pt_Caps on pt_Caps.pk_id = pt_LU.id and pt_Caps.language_code='en' and pt_Caps.table_name = 'CMN_LOOKUPS'
--Progress Lookup
left join niku.cmn_lookups Prgrs_LU on inv.PROGRESS = Prgrs_LU.lookup_enum and Prgrs_LU.lookup_type='INVESTMENT_OBJ_PROGRESS'
left join niku.cmn_captions_nls Prgrs_Caps on Prgrs_Caps.pk_id = Prgrs_LU.id and Prgrs_Caps.language_code='en' and Prgrs_Caps.table_name = 'CMN_LOOKUPS'
--Status Lookup
left join niku.cmn_lookups Stat_LU on INV_DEP.Status = Stat_LU.lookup_enum and Stat_LU.lookup_type='INVESTMENT_OBJ_STATUS'
left join niku.cmn_captions_nls Stat_Caps on Stat_Caps.pk_id = Stat_LU.id and Stat_Caps.language_code='en' and Stat_Caps.table_name = 'CMN_LOOKUPS'
--User ID's Lookup
left join niku.cmn_sec_users ProjManID on INV.MANAGER_ID = ProjManID.id
left join niku.cmn_sec_users ProjManIDDep on INV_DEP.MANAGER_ID = ProjManIDDep.id
)project_info
where @filter@]]

But as soon as I try to XOG this into Clarity I get the following error:
XOG-3052: Query record has not been inserted com.niku.union.persistence.nsql.NSQLSyntaxException: NSQL Syntax exception: NSQL_SYNTAX_ERROR_UNKNOWN_CONSTRUCT, Info: @select:dim_prop:user_def:implied:project:Prj_ID,Prj_ID@

It does not matter which value I use as dim, (Prj_ID or Dependent_ID) it gives same error, only with the other second line as the problem).
I have also tried by doing the XOG step vby step, by adding one value at a time. But as soon as I try the second value/line, it gives the error, no matter what it is.

Even the simple querry
[CDATA[select @select:dim:user_def:implied:project:Dependant_ID:Dependant_ID@,
@select:dim_prop:user_def:implied:project:Prj_Name,Prj_Name@
from (
select PRJ_DEP.ID as Dependant_ID,
INV.NAME as Prj_Name
from PRJ_PROJECT_DEPENDS PRJ_DEP
inner join INV_INVESTMENTS INV on PRJ_DEP.DEPENDENT_ID = INV.ID
)project_info
where @filter@]]
gives the same error.

Only the first line work...

What am I missing?
Any suggestions or help would be greatly appreciated...

Thanks

Outcomes