USP_DATALIST_QUERY

CREATE procedure dbo.USP_DATALIST_QUERY
(
    @CURRENTAPPUSERID uniqueidentifier, 
    @NAME nvarchar(100) = null, 
    @ONLYSHOWMYQUERIES bit = null,
    @RECORDTYPEID uniqueidentifier = null, 
    @QUERYCATEGORYCODEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @INCLUDEFOLDERS bit = 0,
    @ONLYSHOWMOBILIZED bit = 0
)
as
    set nocount on;

    declare @QUERYCATEGORYCODEIDNOTNULL as uniqueidentifier;
    declare @RECORDTYPEIDNOTNULL as uniqueidentifier;

    if @RECORDTYPEID = '00000000-0000-0000-0000-000000000001'
        set @RECORDTYPEIDNOTNULL = null;
    else
        set @RECORDTYPEIDNOTNULL = @RECORDTYPEID;

    if @QUERYCATEGORYCODEID = '00000000-0000-0000-0000-000000000001'
        set @QUERYCATEGORYCODEIDNOTNULL = null;
    else
        set @QUERYCATEGORYCODEIDNOTNULL = @QUERYCATEGORYCODEID;

    declare @ISSYSADMIN bit;
    declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;
    select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;


    /* Filter out any ad-hoc/smart queries involving query views to which the current user does not have access */
    if (@ISSYSADMIN = 0)
    begin
        select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f')

        if (@HASQUERYVIEWSYSTEMPRIVILEGE = 0) 
        begin
            declare @ADHOCCATALOGITEMS table (OBJECTNAME nvarchar(128));

            insert into @ADHOCCATALOGITEMS (OBJECTNAME)
            select 
                QUERYVIEWCATALOG.OBJECTNAME 
            from 
                dbo.QUERYVIEWCATALOG
            where 
                exists 
                (
                    select [ADHOCCATALOGITEMS].QUERYVIEWCATALOGID 
                    from dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@CURRENTAPPUSERID) as [ADHOCCATALOGITEMS] where [ADHOCCATALOGITEMS].QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
                );
        end

        declare @SMARTCATALOGITEMS table (SMARTQUERYCATALOGID uniqueidentifier);

        insert into @SMARTCATALOGITEMS (SMARTQUERYCATALOGID)
        select 
            SMARTQUERYCATALOG.ID 
        from dbo.SMARTQUERYCATALOG
        where 
            exists 
            (
                select [SMARTCATALOGITEMS].SMARTQUERYCATALOGID
                from dbo.UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER(@CURRENTAPPUSERID) as [SMARTCATALOGITEMS] where [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
            );
    end;

    declare @PERMISSIONEDFOLDERS table
    (
        FOLDERID uniqueidentifier
    );

    insert into @PERMISSIONEDFOLDERS (FOLDERID)
    select ID
    from dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS(@CURRENTAPPUSERID);

    declare @QUERIESTOINCLUDE table
    (
        QUERYID uniqueidentifier,
        FOLDERID uniqueidentifier,
        QUERY_TYPE bit,
        ISFAVORITE bit
    );

    declare @HASCRITERIA bit = 0;

    if rtrim(ltrim(coalesce(@NAME, ''))) <> '' or coalesce(@SITEFILTERMODE, 0) <> 0 or @RECORDTYPEID is not null or @QUERYCATEGORYCODEID is not null or coalesce(@ONLYSHOWMYQUERIES, 0) <> 0 or @ONLYSHOWMOBILIZED <> 0
        set @HASCRITERIA = 1;

    insert into @QUERIESTOINCLUDE (QUERYID, FOLDERID, QUERY_TYPE, ISFAVORITE)
    select
        QUERY.ID,
        QUERY.FOLDERID,
        0,
        case when exists(select ID from dbo.APPUSERADHOCQUERYFAVORITE where ADHOCQUERYID = QUERY.ID and APPUSERID = @CURRENTAPPUSERID)
        then convert(bit, 1) else convert(bit, 0)
        end as ISFAVORITE
    from dbo.ADHOCQUERY QUERY
    inner join dbo.QUERYVIEWCATALOG on QUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
    where 
        /* Filter out restricted query views */
        ((@ISSYSADMIN = 1 or 
      (@HASQUERYVIEWSYSTEMPRIVILEGE = 1 and (dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, QUERY.ID) = 1)))
        /* This will return a list of query views in use by the ad-hoc query but not in the ADHOCCATALOGITEMS variable; 
        These ad-hoc queries should not be returned to the client. */
        or 
            (not exists(select [QUERYVIEWSINUSE].OBJECTNAME 
            from dbo.UFN_ADHOCQUERY_QUERYVIEWSINUSE(QUERY.QUERYDEFINITIONXML) as [QUERYVIEWSINUSE]
            where not exists (
                select [ADHOCCATALOGITEMS].OBJECTNAME
                from @ADHOCCATALOGITEMS as [ADHOCCATALOGITEMS] where [ADHOCCATALOGITEMS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME))
            )
            and
            (dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, QUERY.ID) = 1)
        )    
        -- Ensure the folder the query belongs to is visible

        and 
        (
        QUERY.FOLDERID is null
        or QUERY.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS)
        )
        /* End restricted query views filter */
        and (@ONLYSHOWMOBILIZED = 0 or QUERY.MOBILIZE = 1)
        and ((@NAME is null or @NAME = '') or QUERY.NAME like '%' + @NAME + '%')
        and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and QUERY.OWNERID = @CURRENTAPPUSERID))
        and (@RECORDTYPEID is null or ((@RECORDTYPEIDNOTNULL is null and QUERYVIEWCATALOG.RECORDTYPEID is null) or (QUERYVIEWCATALOG.RECORDTYPEID = @RECORDTYPEIDNOTNULL))) 
        and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and QUERY.QUERYCATEGORYCODEID is null) or (QUERY.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEIDNOTNULL)))
        and QUERYVIEWCATALOG.ROOTOBJECT = 1
        and
        (
        QUERY.SITEID is null 
            or
        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[QUERY].[SITEID] or (SITEID is null and [QUERY].[SITEID] is null)))
        )
        and 
        (
        @SITEFILTERMODE = 0
        or 
        QUERY.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))        
        );


    insert into @QUERIESTOINCLUDE (QUERYID, FOLDERID, QUERY_TYPE, ISFAVORITE)
    select
        QUERY.ID,
        QUERY.FOLDERID,
        1,
        case when exists(select ID from dbo.APPUSERSMARTQUERYINSTANCEFAVORITE where SMARTQUERYINSTANCEID = QUERY.ID and APPUSERID = @CURRENTAPPUSERID)
        then convert(bit, 1) else convert(bit, 0)
        end as ISFAVORITE
    from dbo.SMARTQUERYINSTANCE QUERY
    inner join dbo.SMARTQUERYCATALOG on QUERY.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    where 
        /* Filter out restricted smart queries */
        (@ISSYSADMIN = 1
        /*This will return a list of smart queries in use by the smart query but not in the SMARTCATALOGITEMS variable;
        These smart queries should not be returned to the client.*/
        or 
        exists (select [SMARTCATALOGITEMS].SMARTQUERYCATALOGID 
            from @SMARTCATALOGITEMS as [SMARTCATALOGITEMS] 
            where [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID)
        and
        (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, QUERY.ID) = 1)
        ) 
        -- Ensure the folder the query belongs to is visible

        and 
        (
        QUERY.FOLDERID is null
        or QUERY.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS)
        )
        and (@ONLYSHOWMOBILIZED = 0 or QUERY.MOBILIZE = 1)
        and ((@NAME is null or @NAME = '') or QUERY.NAME like '%' + @NAME + '%')
        and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and QUERY.OWNERID = @CURRENTAPPUSERID))
        and (@RECORDTYPEID is null or ((@RECORDTYPEIDNOTNULL is null and SMARTQUERYCATALOG.RECORDTYPEID is null) or (SMARTQUERYCATALOG.RECORDTYPEID = @RECORDTYPEIDNOTNULL)))
        and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and QUERY.QUERYCATEGORYCODEID is null) or (QUERY.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEIDNOTNULL)))
        and
        (
        QUERY.SITEID is null 
            or
        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[QUERY].[SITEID] or (SITEID is null and [QUERY].[SITEID] is null)))
        )
        and 
        (
        @SITEFILTERMODE = 0
        or 
        QUERY.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        );

    declare @QUERYANCESTORS table
    (
        QUERYID uniqueidentifier,
        ANCESTORFOLDERID uniqueidentifier
    );

    insert into @QUERYANCESTORS (QUERYID, ANCESTORFOLDERID)
    select
        QUERIESTOINCLUDE.QUERYID,
        ANCESTORS.ID
    from @QUERIESTOINCLUDE QUERIESTOINCLUDE
    cross apply dbo.UFN_ADHOCQUERY_GETANCESTORS(QUERIESTOINCLUDE.FOLDERID) as ANCESTORS;

    declare @QUERY_DETAIL table 
    (
        QUERYID uniqueidentifier,
        NAME nvarchar(255),
        [DESCRIPTION] nvarchar(1024),
        RECORDTYPEID uniqueidentifier,
        PRIMARYKEYFIELD nvarchar(100), 
        [RECORDTYPE_NAME] nvarchar(50),
        QUERYCATEGORYCODEID uniqueidentifier,
        [QUERYCATEGORYCODE_DESCRIPTION] nvarchar(100),
        CATALOG_NAME nvarchar(255),
        [OWNER] nvarchar(128),
        OWNERID uniqueidentifier,
        OTHERSCANMODIFY bit,
        DATEADDED datetime,
        [ADDEDBY_USERNAME] nvarchar(128),
        ISBROWSABLE bit,
        [SITE] nvarchar(250),
        ISQUERY bit,
        PARENTFOLDERID uniqueidentifier, 
        IMAGEKEY nvarchar(30),
        DISPLAYORDER int,
        DISPLAYORDERNOTSET bit,
        MOBILIZE bit,
        USERCANEDIT bit,
        QUERY_TYPE int,
        QUERY_TYPE_NAME varchar(10),
        ISFAVORITE bit
    );

    with xmlnamespaces ('bb_appfx_smartquery' as [ns])
    insert into @QUERY_DETAIL (QUERYID,NAME,[DESCRIPTION],RECORDTYPEID,PRIMARYKEYFIELD, [RECORDTYPE_NAME],QUERYCATEGORYCODEID,[QUERYCATEGORYCODE_DESCRIPTION],CATALOG_NAME,[OWNER],OWNERID,OTHERSCANMODIFY,DATEADDED,[ADDEDBY_USERNAME],ISBROWSABLE,[SITE],ISQUERY,PARENTFOLDERID, IMAGEKEY,DISPLAYORDER,DISPLAYORDERNOTSET,MOBILIZE,USERCANEDIT,QUERY_TYPE,QUERY_TYPE_NAME,ISFAVORITE)
    select 
        QUERY.ID as QUERYID,
        QUERY.NAME,
        QUERY.[DESCRIPTION],
        QUERY.RECORDTYPEID,
        QUERY.PRIMARYKEYFIELD, 
        RECORDTYPE.NAME [RECORDTYPE_NAME],
        QUERY.QUERYCATEGORYCODEID,
        QUERYCATEGORYCODE.[DESCRIPTION] [QUERYCATEGORYCODE_DESCRIPTION],
        QUERY.CATALOG_NAME,
        case when APPUSER.DISPLAYNAME = ''
        then APPUSER.USERNAME
        else APPUSER.DISPLAYNAME
        end [OWNER],
        QUERY.OWNERID,
        QUERY.OTHERSCANMODIFY,
        QUERY.DATEADDED,
        CHANGEAGENT.USERNAME [ADDEDBY_USERNAME],
        QUERY.ISBROWSABLE,
        coalesce(SITE.NAME, 'All sites') [SITE],
        cast(1 as bit) as ISQUERY,
        QUERY.FOLDERID as PARENTFOLDERID,
        null,
        QUERY.DISPLAYORDER,
        case 
        when DISPLAYORDER = 0 then 1 
        else 0 
        end as DISPLAYORDERNOTSET,
        QUERY.MOBILIZE,
        QUERY.USERCANEDIT,
        QUERY.QUERY_TYPE,
        case
        when QUERY.QUERY_TYPE = 0 then 'Ad-hoc'
        else 'Smart' 
        end as QUERY_TYPE_NAME,
        QUERIESTOINCLUDE.ISFAVORITE 
    from @QUERIESTOINCLUDE as QUERIESTOINCLUDE
    inner join 
    (
        select 
        ADHOCQUERY.ID,
        ADHOCQUERY.NAME,
        ADHOCQUERY.[DESCRIPTION],
        QUERYVIEWCATALOG.RECORDTYPEID,
        '' as [PRIMARYKEYFIELD],
        ADHOCQUERY.QUERYCATEGORYCODEID,
        QUERYVIEWCATALOG.DISPLAYNAME CATALOG_NAME,
        ADHOCQUERY.OWNERID,
        ADHOCQUERY.OTHERSCANMODIFY,
        ADHOCQUERY.DATEADDED,
        ADHOCQUERY.ISBROWSABLE,
        ADHOCQUERY.SITEID,
        ADHOCQUERY.FOLDERID,
        ADHOCQUERY.DISPLAYORDER,
        ADHOCQUERY.MOBILIZE,
        case 
            when @ISSYSADMIN = 1 then cast(1 as bit)
            when dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERY.ID) = 1 then cast(1 as bit)
            else cast(0 as bit)
        end as USERCANEDIT,
        ADHOCQUERY.ADDEDBYID,
        0 as QUERY_TYPE
        from dbo.ADHOCQUERY 
        inner join dbo.QUERYVIEWCATALOG on ADHOCQUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID

        union all

        select 
        SMARTQUERYINSTANCE.ID,
        SMARTQUERYINSTANCE.NAME,
        SMARTQUERYINSTANCE.[DESCRIPTION],
        SMARTQUERYCATALOG.RECORDTYPEID,
        (select T.c.value('@PrimaryKeyField', 'nvarchar(100)') from SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec') as T(c)) as [PRIMARYKEYFIELD],
        SMARTQUERYINSTANCE.QUERYCATEGORYCODEID,
        SMARTQUERYCATALOG.[NAME] CATALOG_NAME,
        SMARTQUERYINSTANCE.OWNERID,
        SMARTQUERYINSTANCE.OTHERSCANMODIFY,
        SMARTQUERYINSTANCE.DATEADDED,
        case (select coalesce(T.c.value('@PrimaryKeyField', 'nvarchar(100)'), '') from SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec') as T(c))
            when '' then convert(bit, 0)
            else convert(bit, 1) end as [ISBROWSABLE],
        SMARTQUERYINSTANCE.SITEID,
        SMARTQUERYINSTANCE.FOLDERID,
        SMARTQUERYINSTANCE.DISPLAYORDER,
        SMARTQUERYINSTANCE.MOBILIZE,
        cast(1 as bit) as USERCANEDIT,
        SMARTQUERYINSTANCE.ADDEDBYID,
        1 as QUERY_TYPE
        from dbo.SMARTQUERYINSTANCE
        inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    ) as QUERY on QUERY.ID = QUERIESTOINCLUDE.QUERYID
    left join dbo.RECORDTYPE on QUERY.RECORDTYPEID = RECORDTYPE.ID
    left join dbo.APPUSER on QUERY.OWNERID = dbo.APPUSER.ID
    left join dbo.QUERYCATEGORYCODE on QUERY.QUERYCATEGORYCODEID = QUERYCATEGORYCODE.ID
    left join dbo.CHANGEAGENT on QUERY.ADDEDBYID = CHANGEAGENT.ID
    left join dbo.[SITE] on QUERY.SITEID = [SITE].ID;

    declare @FAVORITESFOLDERID uniqueidentifier = '00000000-0000-0000-0000-000000000001';

    select 
        QUERY.QUERYID,
        QUERY.NAME,
        QUERY.[DESCRIPTION],
        QUERY.RECORDTYPEID,
        QUERY.PRIMARYKEYFIELD, 
        QUERY.[RECORDTYPE_NAME],
        QUERY.QUERYCATEGORYCODEID,
        QUERY.[QUERYCATEGORYCODE_DESCRIPTION],
        QUERY.CATALOG_NAME,
        QUERY.[OWNER],
        QUERY.OWNERID,
        QUERY.OTHERSCANMODIFY,
        QUERY.DATEADDED,
        QUERY.[ADDEDBY_USERNAME],
        QUERY.ISBROWSABLE,
        QUERY.[SITE],
        QUERY.ISQUERY,
        QUERY.PARENTFOLDERID,
        QUERY.IMAGEKEY,
        QUERY.DISPLAYORDER,
        QUERY.DISPLAYORDERNOTSET,
        QUERY.MOBILIZE,
        QUERY.USERCANEDIT,
        QUERY.QUERY_TYPE,
        QUERY.QUERY_TYPE_NAME
    from @QUERY_DETAIL as QUERY 

    union all

    select 
        ADHOCQUERYFOLDER.ID,
        ADHOCQUERYFOLDER.NAME + ' (' + cast((select count(*) from @QUERYANCESTORS as QUERYANCESTORS where QUERYANCESTORS.ANCESTORFOLDERID = ADHOCQUERYFOLDER.ID) as nvarchar(10)) + ')',
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        cast(1 as bit) as OTHERSCANMODIFY,
        null,
        null,
        null,
        null,
        cast(0 as bit) as ISQUERY,
        ADHOCQUERYFOLDER.PARENTFOLDERID,
        'RES:folder_closed',
        ADHOCQUERYFOLDER.DISPLAYORDER,
        case 
            when DISPLAYORDER = 0 then 1 
            else 0 
        end as DISPLAYORDERNOTSET,
        null,
        cast(0 as bit) as USERCANEDIT,
        null,
        null
    from @PERMISSIONEDFOLDERS PERMISSIONEDFOLDERS
    inner join dbo.ADHOCQUERYFOLDER on PERMISSIONEDFOLDERS.FOLDERID = ADHOCQUERYFOLDER.ID
    where
    @INCLUDEFOLDERS = 1 and
    (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYFOLDER_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERYFOLDER.ID) = 1) and
    (
        @HASCRITERIA = 0 or
        -- Ensure that there are visible queries under this folder

        exists
        (
        select 1 from 
        @QUERYANCESTORS as QUERYANCESTORS 
        where 
            QUERYANCESTORS.ANCESTORFOLDERID = ADHOCQUERYFOLDER.ID
        )
    )

    union all

    select 
        @FAVORITESFOLDERID,
        'Favorites' + ' (' + cast((select count(*) from @QUERIESTOINCLUDE as QUERIESTOINCLUDE where QUERIESTOINCLUDE.ISFAVORITE = 1) as nvarchar(10)) + ')',
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        cast(1 as bit) as OTHERSCANMODIFY,
        null,
        null,
        null,
        null,
        cast(0 as bit) as ISQUERY,
        null,
        'RES:favorites_16',
        0,
        0 as DISPLAYORDERNOTSET,
        null,
        cast(0 as bit) as USERCANEDIT,
        null,
        null
    where @INCLUDEFOLDERS = 1 

    union all

    select 
        QUERY.QUERYID,
        QUERY.NAME,
        QUERY.[DESCRIPTION],
        QUERY.RECORDTYPEID,
        QUERY.PRIMARYKEYFIELD, 
        QUERY.[RECORDTYPE_NAME],
        QUERY.QUERYCATEGORYCODEID,
        QUERY.[QUERYCATEGORYCODE_DESCRIPTION],
        QUERY.CATALOG_NAME,
        QUERY.[OWNER],
        QUERY.OWNERID,
        QUERY.OTHERSCANMODIFY,
        QUERY.DATEADDED,
        QUERY.[ADDEDBY_USERNAME],
        QUERY.ISBROWSABLE,
        QUERY.[SITE],
        QUERY.ISQUERY,
        @FAVORITESFOLDERID as PARENTFOLDERID,
        QUERY.IMAGEKEY,
        QUERY.DISPLAYORDER,
        QUERY.DISPLAYORDERNOTSET,
        QUERY.MOBILIZE,
        QUERY.USERCANEDIT,
        QUERY.QUERY_TYPE,
        QUERY.QUERY_TYPE_NAME
    from @QUERY_DETAIL as QUERY 
    where QUERY.ISFAVORITE = 1 and @INCLUDEFOLDERS = 1 
    order by 
        -- This order statement is to ensure that rows with a blank DISPLAYORDER appear

        -- after rows that have a set DISPLAYORDER

        DISPLAYORDERNOTSET,
        DISPLAYORDER, 
        NAME;