V_QUERY_MKTSEGMENTPASSIVE
Provides the ability to query media outlets, time slots and marketing locations.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| CATEGORY | nvarchar(100) | yes | Category |
| CODE | nvarchar(10) | Code | |
| ISINUSE | bit | yes | Is in use? |
| SEGMENTTYPE | nvarchar(18) | yes | Segment type |
| VENDOR | nvarchar(154) | yes | Vendor |
| IMPRESSIONS | int | yes | Impressions |
| IMPRESSIONCALCULATIONMETHOD | nvarchar(20) | yes | Impressions per |
| MEDIAOUTLET | nvarchar(100) | yes | Media outlet |
| SCHEDULESTARTTIME | time | yes | Start time |
| SCHEDULEENDTIME | time | yes | End time |
| SCHEDULEDURATION | int | yes | Duration |
| LOCATIONADDRESSBLOCK | nvarchar(150) | yes | Address |
| LOCATIONCITY | nvarchar(50) | yes | City |
| LOCATIONSTATE | nvarchar(100) | yes | State |
| LOCATIONPOSTCODE | nvarchar(12) | yes | Post code |
| LOCATIONCOUNTRY | nvarchar(100) | yes | Country |
| KPICONTEXTRECORDID | nvarchar(100) | yes | KPIs context record ID |
| VENDORID | uniqueidentifier | yes | Vendor system record ID |
| PARENTMEDIAOUTLETSEGMENTID | uniqueidentifier | yes | Media outlet system record ID |
| SITEID | uniqueidentifier | yes | Site ID |
| 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 |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| LOCATIONADDRESSLINE1 | nvarchar(150) | yes | |
| LOCATIONADDRESSLINE2 | nvarchar(150) | yes | |
| LOCATIONADDRESSLINE3 | nvarchar(150) | yes | |
| LOCATIONADDRESSLINE4 | nvarchar(150) | yes | |
| LOCATIONADDRESSLINE5 | nvarchar(150) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:11 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTPASSIVE AS
select
[MKTSEGMENT].[ID],
[MKTSEGMENT].[NAME],
[MKTSEGMENT].[DESCRIPTION],
(select [DESCRIPTION] from dbo.[MKTSEGMENTCATEGORYCODE] where [ID] = [MKTSEGMENT].[SEGMENTCATEGORYCODEID]) as [CATEGORY],
[MKTSEGMENT].[CODE],
dbo.[UFN_MKTSEGMENT_ISINUSE]([MKTSEGMENT].[ID]) as [ISINUSE],
[MKTSEGMENT].[SEGMENTTYPE],
[VENDOR].[NAME] as [VENDOR],
[MKTSEGMENTPASSIVE].[IMPRESSIONS],
[MKTSEGMENTPASSIVE].[IMPRESSIONCALCULATIONMETHOD],
[MEDIAOUTLET].[NAME] as [MEDIAOUTLET],
[MKTSEGMENTPASSIVE].[SCHEDULESTARTTIME],
[MKTSEGMENTPASSIVE].[SCHEDULEENDTIME],
[MKTSEGMENTPASSIVE].[SCHEDULEDURATION],
[MKTSEGMENTPASSIVE].[LOCATIONADDRESSBLOCK],
[MKTSEGMENTPASSIVE].[LOCATIONCITY],
[STATE].[DESCRIPTION] as [LOCATIONSTATE],
[MKTSEGMENTPASSIVE].[LOCATIONPOSTCODE],
[COUNTRY].[DESCRIPTION] as [LOCATIONCOUNTRY],
cast([MKTSEGMENT].[ID] as nvarchar(100)) as [KPICONTEXTRECORDID],
[MKTSEGMENTPASSIVE].[VENDORID],
[MKTSEGMENTPASSIVE].[PARENTMEDIAOUTLETSEGMENTID],
[MKTSEGMENT].[SITEID],
/* System fields */
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEGMENT].[DATEADDED],
[MKTSEGMENT].[DATECHANGED],
[MKTSEGMENT].[TSLONG],
[ADDRLINES].[ADDRESSLINE1] as [LOCATIONADDRESSLINE1],
[ADDRLINES].[ADDRESSLINE2] as [LOCATIONADDRESSLINE2],
[ADDRLINES].[ADDRESSLINE3] as [LOCATIONADDRESSLINE3],
[ADDRLINES].[ADDRESSLINE4] as [LOCATIONADDRESSLINE4],
[ADDRLINES].[ADDRESSLINE5] as [LOCATIONADDRESSLINE5]
/*#EXTENSION*/
from dbo.[MKTSEGMENT]
left outer join dbo.[MKTSEGMENTPASSIVE] on [MKTSEGMENTPASSIVE].[ID] = [MKTSEGMENT].[ID]
left outer join dbo.[CONSTITUENT] as [VENDOR] on [VENDOR].[ID] = [MKTSEGMENTPASSIVE].[VENDORID]
left outer join dbo.[MKTSEGMENT] as [MEDIAOUTLET] on [MEDIAOUTLET].[ID] = [MKTSEGMENTPASSIVE].[PARENTMEDIAOUTLETSEGMENTID]
left outer join dbo.[COUNTRY] on [COUNTRY].[ID] = [MKTSEGMENTPASSIVE].[LOCATIONCOUNTRYID]
left outer join dbo.[STATE] on [STATE].[ID] = [MKTSEGMENTPASSIVE].[LOCATIONSTATEID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEGMENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEGMENT].[CHANGEDBYID]
--Since we are using a SQL CLR function, the optimizer cannot figure that out the table function only returns one row, and so the optimizer
--will include this "outer apply" in even the simplest execution plans and hurt performance. To remedy the situation, we are using a "top 1"
--clause to help out the optimizer so that it doesn't execute the function unless you return one of the address line fields.
outer apply (select top 1 [ADDRESSLINE1], [ADDRESSLINE2], [ADDRESSLINE3], [ADDRESSLINE4], [ADDRESSLINE5] from dbo.[UFN_ADDRESS_GETADDRESSLINES]([MKTSEGMENTPASSIVE].[LOCATIONADDRESSBLOCK])) as [ADDRLINES]
where [MKTSEGMENT].[SEGMENTTYPECODE] in (6, 7, 8)