AnsweredAssumed Answered

Basic NSQL Query Question on -  Select:Param, Order By,Top (N) SQL Function

Question asked by Jboyd on Feb 8, 2010
Latest reply on Feb 8, 2010 by another_martink
I am new to SQL and NSQL.[left]  [left] I am trying to obtain the top N largest active projects by hours for a user supplied date range and user supplied N.  [left]  [left] I am having two (and possibly more) issues with the NSQL described below.  I have underlined the problem lines.  Removing the two lines will allow the the query to run, albeit incorrectly.  [left]  [left] SELECT  [left]  [left] @SELECT:PARAM:USER_DEF:INTEGER:TOPN@     [left] ,@SELECT:METRIC:USER_DEF:IMPLIED:Top (@TOPN) sum(prTimeEntry.prActSum/3600):TOT_HOURS@[left] ,@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:[niku].SRM_Projects.name:PROJ_NAME:agg@[left]
[left]
[left] From [niku].SRM_PROJECTS  [left] JOIN [niku].PRJ_PROJECTS on [niku].SRM_PROJECTS.ID = [niku].PRJ_PROJECTS.prID[left] RIGHT JOIN [niku].PRTASK on [niku].PRJ_PROJECTS.prID = [niku].PRTASK.prProjectID[left] RIGHT JOIN [niku].prAssignment on [niku].PRTASK.prID = [niku].prAssignment.prTaskID[left] JOIN [niku].PRJ_RESOURCES on [niku].prAssignment.prResourceID = [niku].PRJ_Resources.prID[left] JOIN [niku].SRM_Resources on [niku].PRJ_Resources.prPrimaryRoleID = [niku].SRM_Resources.ID  [left] LEFT JOIN [niku].prTimeEntry on [niku].prAssignment.prID = [niku].prTimeEntry.prAssignmentID[left] LEFT JOIN [niku].prTimeSheet on [niku].prTimeEntry.prTimeSheetID = [niku].prTimeSheet.prID[left] LEFT JOIN [niku].prTimePeriod on [niku].prTimeSheet.prTimePeriodID = [niku].prTimePeriod.prID[left]
[left] Where @FILTER@[left]          AND [niku].prTimeSheet.prStatus = '4'[left]          AND [niku].SRM_Projects.is_Active = '1'[left]          AND @WHERE:PARAM:USER_DEF:DATE:STARTDATE@ is Null[left]              OR @Where:PARAM:USER_DEF:DATE:STARTDATE@ prTimePeriod.prFinish[left]
[left] GROUP BY [niku].SRM_Projects.Name[left]  [left] ORDER BY TOT_HOURS  [left]  [left] Having @Having_FILTER@[left]  [left] (1)  The first issues -   @SELECT:PARAM:USER_DEF:INTEGER:TOPN@.    It seems that NSQL does not like it if this line comes before the TOP() statement.  How else can I set the parameter supplied by the user?  Should I be doing this through a correlated query with the PARAM in a @WHERE@ statement?[left]  [left] (2) The second issue -   ORDER BY TOT_HOURS.    It seems that NSQL won't let me use only the field alias.  It requires a table alias too.  However, this ORDER BY uses the metric so that I can select the TOP (N) largest from the result set.[left]  [left] (3)  I am missing the boat on this one?  Are there some default dims already available to make answering this question easier?    [left]  [left] Thank you in advance for your time and attention.[left]  [left] Best regards.  [left]

Outcomes