V_QUERY_QUERYVIEWOUTPUT
Provides support for querying on the output for query views in the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| QUERYVIEWID | uniqueidentifier | Query View ID | |
| NAME | nvarchar(max) | yes | Name |
| CAPTION | nvarchar(max) | yes | Caption |
| ISHIDDEN | int | yes | Is hidden |
| CATEGORY | nvarchar(max) | yes | Category |
| DATATYPE | nvarchar(18) | yes | Data type |
| ISDEFAULTOUTPUT | int | yes | Is default output |
| FORMAT | nvarchar(max) | yes | Format |
| ISGROUPBYFIELD | int | yes | Is Group By field |
| ISENCRYPTEDFIELD | int | yes | Is encrypted field |
| CAPTIONRESOURCEKEY | nvarchar(max) | yes | Caption resource key |
| CATEGORYRESOURCEKEY | nvarchar(max) | yes | Category resource key |
| ALLOWMONTHDAYONFUZZYDATE | int | yes | Allow Month/Day on fuzzy date |
| LOOKUPINFOTYPE | varchar(15) | Lookup info type | |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed product list |
| INSTALLED | bit | yes | Installed |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/2/2010 7:06:48 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.7.1654.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_QUERYVIEWOUTPUT AS
with xmlnamespaces ('bb_appfx_queryview' as tns, 'bb_appfx_commontypes' as common)
select
Q.ID as QUERYVIEWID,
queryviewoutput.fields.value('@Name', 'nvarchar(max)') as NAME,
queryviewoutput.fields.value('@Caption', 'nvarchar(max)') as CAPTION,
coalesce(queryviewoutput.fields.value('@IsHidden', 'bit'), 0) as ISHIDDEN,
queryviewoutput.fields.value('@Category', 'nvarchar(max)') as CATEGORY,
coalesce(queryviewoutput.fields.value('@DataType', 'nvarchar(18)'), 'String') as DATATYPE,
coalesce(queryviewoutput.fields.value('@IsDefaultOutput', 'bit'), 0) as ISDEFAULTOUTPUT,
queryviewoutput.fields.value('@Format', 'nvarchar(max)') as FORMAT,
coalesce(queryviewoutput.fields.value('@IsGroupByField', 'bit'), 0) as ISGROUPBYFIELD,
coalesce(queryviewoutput.fields.value('@IsEncryptedField', 'bit'), 0) as ISENCRYPTEDFIELD,
queryviewoutput.fields.value('@CaptionResourceKey', 'nvarchar(max)') as CAPTIONRESOURCEKEY,
queryviewoutput.fields.value('@CategoryResourceKey', 'nvarchar(max)') as CATEGORYRESOURCEKEY,
coalesce(queryviewoutput.fields.value('@AllowMonthDayOnFuzzyDate', 'bit'), 0) as ALLOWMONTHDAYONFUZZYDATE,
case
when queryviewoutput.fields.exist('tns:LookupInfo/tns:CodeTable') = 1 then 'CodeTable'
when queryviewoutput.fields.exist('tns:LookupInfo/tns:SearchList') = 1 then 'SearchList'
when queryviewoutput.fields.exist('tns:LookupInfo/tns:SimpleDataList') = 1 then 'SimpleDataList'
when queryviewoutput.fields.exist('tns:LookupInfo/tns:TranslationList') = 1 then 'TranslationList'
else 'None'
end as LOOKUPINFOTYPE,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(queryviewoutput.fields.query('common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(queryviewoutput.fields.query('common:InstalledProductList')) as INSTALLED
/*#EXTENSION*/
from dbo.QUERYVIEWCATALOG as Q
cross apply Q.QUERYVIEWSPEC.nodes('tns:QueryViewSpec/tns:Output/tns:OutputFields/tns:OutputField') as queryviewoutput(fields)