V_QUERY_SMARTQUERYINSTANCE
Provides the ability to query smart query instances.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| RECORDTYPE | nvarchar(50) | yes | Type |
| NAME | nvarchar(255) | Name | |
| DESCRIPTION | nvarchar(1024) | Description | |
| QUERYCATEGORYCODE | nvarchar(100) | yes | Query category |
| OWNERID | nvarchar(128) | yes | Owner |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| SITEID | uniqueidentifier | yes | Site ID |
| LASTRUNDATE | datetime | yes | |
| LASTRUNDURATION | time | yes | |
| LASTRUNBY_USERNAME | nvarchar(255) | yes | |
| LASTRUNRESULTCOUNT | int | yes | |
| RUNCOUNT | int | yes | |
| ODATALASTRUNDATE | datetime | yes | |
| ODATALASTRUNDURATION | time | yes | |
| ODATALASTRUNBY_USERNAME | nvarchar(255) | yes | |
| ODATALASTRUNRESULTCOUNT | int | yes | |
| ODATARUNCOUNT | int | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:27 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SMARTQUERYINSTANCE AS
select
[QUERY].[ID],
(select dbo.[UFN_RECORDTYPE_GETNAMEBYID]([SMARTQUERYCATALOG].[RECORDTYPEID]) from dbo.[SMARTQUERYCATALOG] where [SMARTQUERYCATALOG].[ID] = [QUERY].[SMARTQUERYCATALOGID]) as [RECORDTYPE],
[QUERY].[NAME],
[QUERY].[DESCRIPTION],
dbo.[UFN_QUERYCATEGORYCODE_GETDESCRIPTION]([QUERY].[QUERYCATEGORYCODEID]) as [QUERYCATEGORYCODE],
(select [APPUSER].[USERNAME] from dbo.[APPUSER] where ID = [QUERY].OWNERID) as [OWNERID],
[QUERY].[DATEADDED],
[QUERY].[DATECHANGED],
[QUERY].[TSLONG],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[QUERY].[SITEID],
[RUNINFO].[LASTRUNDATE],
[RUNINFO].[LASTRUNDURATION],
case
when [LASTRUNBY].[DISPLAYNAME] = '' then [LASTRUNBY].[USERNAME]
else [LASTRUNBY].[DISPLAYNAME]
end [LASTRUNBY_USERNAME],
[RUNINFO].[LASTRUNRESULTCOUNT],
[RUNINFO].[RUNCOUNT],
[ODATA].[LASTRUNDATE] as [ODATALASTRUNDATE],
[ODATA].[LASTRUNDURATION] as [ODATALASTRUNDURATION],
case
when [ODATALASTRUNBY].[DISPLAYNAME] = '' then [ODATALASTRUNBY].[USERNAME]
else [ODATALASTRUNBY].[DISPLAYNAME]
end [ODATALASTRUNBY_USERNAME],
[ODATA].[LASTRUNRESULTCOUNT] as [ODATALASTRUNRESULTCOUNT],
[ODATA].[RUNCOUNT] as [ODATARUNCOUNT]
/*#EXTENSION*/
from
dbo.[SMARTQUERYINSTANCE] [QUERY]
left join dbo.[CHANGEAGENT] [ADDEDBY] on [ADDEDBY].[ID] = [QUERY].[ADDEDBYID]
left join dbo.[CHANGEAGENT] [CHANGEDBY] on [CHANGEDBY].[ID] = [QUERY].[CHANGEDBYID]
left join dbo.[SMARTQUERYINSTANCERUNINFO] [RUNINFO] on [QUERY].[ID] = [RUNINFO].[ID]
left join dbo.[APPUSER] as [LASTRUNBY] on [RUNINFO].[LASTRUNBY] = [LASTRUNBY].[ID]
left join dbo.[ODATAQUERYRUNINFO] as [ODATA] on [ODATA].[QUERYID] = [QUERY].[ID]
left join dbo.[APPUSER] as [ODATALASTRUNBY] on [ODATA].[LASTRUNBY] = [ODATALASTRUNBY].[ID]