USP_QUERYVIEW_GETQUERYNODE

CREATE procedure dbo.[USP_QUERYVIEW_GETQUERYNODE]
(
  @ROOTOBJECTNAME nvarchar(128),
  @PATHALIAS nvarchar(128),
  @APPUSERID uniqueidentifier,
  @USEINREPORTMODELGENERATOR bit = null
)
as begin
  -- Returns related nodes to the root query view

  set nocount on;

  /*
  Do not filter out based on user rights.  Security will be checked outside this SP.
  declare @IS_SYS_ADMIN bit;
  select @IS_SYS_ADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @APPUSERID;
  */

  select
    coalesce([Q].[PATHALIAS], [RELATEDQUERYVIEW].[DISPLAYNAME]) as [DISPLAYNAME],
    [Q].[RELATEDQUERYVIEWID],
    [Q].[ROOTCOLUMN] as [ROOTCOLUMN],
    [Q].[RELATEDCOLUMN] as [RELATEDCOLUMN],
    [RELATEDQUERYVIEW].[OBJECTNAME] as [OBJECTNAME],
    [RELATEDQUERYVIEW].[OBJECTTYPE],
    [RELATEDQUERYVIEW].[SUBGROUP],
    [RELATEDQUERYVIEW].[PRIMARYKEYFIELD],
    [Q].[JOINTYPE],
    [Q].[JOINPARAMETERS],
    [RELATEDQUERYVIEW].[OUTPUTDEFINITIONXML],
    [RELATEDQUERYVIEW].[QUERYVIEWSPEC],
    [RELATEDQUERYVIEW].[ENFORCERECORDACCESSSECURITY],
    (select count(*) from dbo.[QUERYVIEWRELATIONSHIP] where [ROOTQUERYVIEWID] = [Q].[RELATEDQUERYVIEWID]) as [CHILDNODECOUNT],
    [RECORDTYPE].[ID] as [RECORDTYPEID],
    [RELATEDQUERYVIEW].[USEINREPORTMODELGENERATOR],
    [Q].[CARDINALITYTYPE],
    [RECORDTYPE].[NAME] as [RECORDTYPE],
    [Q].[DISPLAYALIAS],
    [Q].[OBSOLETE],
    cast(isnull([OLAPDATASOURCE].[SUPPORTSROLLINGDATES], 0) as bit) as [SUPPORTSROLLINGDATES],
    dbo.UFN_OLAP_GETDATAMARTLASTREFRESH([OLAPDATASOURCE].[MARTKEY]) as [DATAMARTLASTREFRESH]
  from dbo.[QUERYVIEWRELATIONSHIP] as [Q]
  inner join dbo.[QUERYVIEWCATALOG] as ROOTQUERYVIEW on [Q].[ROOTQUERYVIEWID] = [ROOTQUERYVIEW].[ID]
  inner join dbo.[QUERYVIEWCATALOG] as [RELATEDQUERYVIEW] on [Q].[RELATEDQUERYVIEWID] = [RELATEDQUERYVIEW].[ID]
  left outer join dbo.[RECORDTYPE] on [RELATEDQUERYVIEW].RECORDTYPEID = [RECORDTYPE].[ID]
  left outer join dbo.[OLAPDATASOURCE] on [OLAPDATASOURCE].[MARTKEY] = [ROOTQUERYVIEW].[MARTKEY]
  where [ROOTQUERYVIEW].[OBJECTNAME] = @ROOTOBJECTNAME
  and [Q].[PATHALIAS] = @PATHALIAS
  and (@USEINREPORTMODELGENERATOR is null or [RELATEDQUERYVIEW].[USEINREPORTMODELGENERATOR] = @USEINREPORTMODELGENERATOR);

  /* Always return this info. Do not filter out based on user rights.  Security will be checked outside this SP.
  and (
      @IS_SYS_ADMIN  = 1
      or (dbo.[UFN_SECURITY_APPUSER_CANACCESS_QUERYVIEW](@APPUSERID, [Q].[ROOTQUERYVIEWID]) = 1
          and
          dbo.[UFN_SECURITY_APPUSER_CANACCESS_QUERYVIEW](@APPUSERID, [Q].[RELATEDQUERYVIEWID]) = 1)
  );
  */

  return 0;
end