V_QUERY_SEARCHLISTOUTPUT
Provides support for querying on the output for search lists from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| SEARCHLISTID | uniqueidentifier | Search List ID | |
| FIELDID | nvarchar(max) | yes | Field ID |
| CAPTION | nvarchar(max) | yes | Caption |
| ISHIDDEN | int | yes | Is hidden |
| DATATYPE | nvarchar(20) | yes | Data type |
| CAPTIONRESOURCEKEY | nvarchar(max) | yes | Caption resource key |
| DISPLAYSEQUENCE | int | yes | Display sequence |
| DISPLAYDATEASDATETIME | int | yes | Display Date as DateTime |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed product list |
| INSTALLED | bit | yes | Installed |
| ISOPTIONALOUTPUT | int | Is optional output | |
| FORMAT | nvarchar(50) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:30:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SEARCHLISTOUTPUT AS
with xmlnamespaces ('bb_appfx_searchlist' as tns, 'bb_appfx_commontypes' as common)
select
S.ID as SEARCHLISTID,
searchlistoutput.fields.value('@FieldID', 'nvarchar(max)') as FIELDID,
searchlistoutput.fields.value('@Caption', 'nvarchar(max)') as CAPTION,
coalesce(searchlistoutput.fields.value('@IsHidden', 'bit'), 0) as ISHIDDEN,
searchlistoutput.fields.value('@DataType', 'nvarchar(20)') as DATATYPE,
searchlistoutput.fields.value('@CaptionResourceKey', 'nvarchar(max)') as CAPTIONRESOURCEKEY,
searchlistoutput.fields.value('@DisplaySequence', 'integer') as DISPLAYSEQUENCE,
coalesce(searchlistoutput.fields.value('@DisplayDateAsDateTime', 'bit'), 0) as DISPLAYDATEASDATETIME,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(searchlistoutput.fields.query('common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(searchlistoutput.fields.query('common:InstalledProductList')) as INSTALLED,
case
when exists (select 1
from S.SEARCHLISTSPEC.nodes('tns:SearchListSpec/tns:OptionalFields/tns:OutputFields/tns:OutputFieldID') as optional(fields)
where optional.fields.value('.', 'nvarchar(max)') = searchlistoutput.fields.value('@FieldID', 'nvarchar(max)'))
then 1
else 0
end as ISOPTIONALOUTPUT,
searchlistoutput.fields.value('@Format', 'nvarchar(50)') as FORMAT
/*#EXTENSION*/
from dbo.SEARCHLISTCATALOG as S
cross apply S.SEARCHLISTSPEC.nodes('tns:SearchListSpec/tns:Output/tns:OutputFields/tns:OutputField') as searchlistoutput(fields)