V_QUERY_MKTSEGMENTATIONSEED
Provides the ability to query a marketing effort's seeds.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| SEGMENTATIONID | uniqueidentifier | Effort ID | |
| SEEDID | uniqueidentifier | Seed ID | |
| INACTIVE | bit | yes | Inactive |
| FULLNAME | nvarchar(154) | yes | Full name |
| TITLE | nvarchar(100) | yes | Title |
| FIRSTNAME | nvarchar(50) | yes | First name |
| MIDDLENAME | nvarchar(50) | yes | Middle name |
| LASTNAME | nvarchar(100) | yes | Last name |
| SUFFIX | nvarchar(100) | yes | Suffix |
| PHONENUMBER | nvarchar(100) | yes | Phone number |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | Email address |
| COUNTRY_ABBREVIATION | nvarchar(5) | yes | Country abbreviation |
| COUNTRY_DESCRIPTION | nvarchar(100) | yes | Country |
| ADDRESSBLOCK | nvarchar(150) | yes | Address block |
| ADDRESSLINE1 | nvarchar(150) | yes | Address line 1 |
| ADDRESSLINE2 | nvarchar(150) | yes | Address line 2 |
| ADDRESSLINE3 | nvarchar(150) | yes | Address line 3 |
| ADDRESSLINE4 | nvarchar(150) | yes | Address line 4 |
| ADDRESSLINE5 | nvarchar(150) | yes | Address line 5 |
| CITY | nvarchar(50) | yes | City |
| STATE_ABBREVIATION | nvarchar(50) | yes | State abbreviation |
| STATE_DESCRIPTION | nvarchar(100) | yes | State |
| POSTCODE | nvarchar(12) | yes | Post code |
| CART | nvarchar(10) | yes | CART |
| DPC | nvarchar(8) | yes | DPC |
| LOT | nvarchar(5) | yes | LOT |
| 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 | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:14:10 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_MKTSEGMENTATIONSEED AS
select
[MKTSEGMENTATIONSEED].[ID],
[MKTSEGMENTATIONSEED].[SEGMENTATIONID],
[MKTSEGMENTATIONSEED].[SEEDID],
[MKTSEED].[INACTIVE],
[MKTSEED].[NAME] as [FULLNAME],
[TITLECODE].[DESCRIPTION] as [TITLE],
[MKTSEED].[FIRSTNAME],
[MKTSEED].[MIDDLENAME],
[MKTSEED].[LASTNAME],
[SUFFIXCODE].[DESCRIPTION] as [SUFFIX],
[MKTSEED].[PHONENUMBER],
[MKTSEED].[EMAILADDRESS],
/* Address fields */
[COUNTRY].[ABBREVIATION] as [COUNTRY_ABBREVIATION],
[COUNTRY].[DESCRIPTION] as [COUNTRY_DESCRIPTION],
[MKTSEED].[ADDRESSBLOCK],
[ADDRLINES].[ADDRESSLINE1],
[ADDRLINES].[ADDRESSLINE2],
[ADDRLINES].[ADDRESSLINE3],
[ADDRLINES].[ADDRESSLINE4],
[ADDRLINES].[ADDRESSLINE5],
[MKTSEED].[CITY],
[STATE].[ABBREVIATION] as [STATE_ABBREVIATION],
[STATE].[DESCRIPTION] as [STATE_DESCRIPTION],
[MKTSEED].[POSTCODE],
[MKTSEED].[CART],
[MKTSEED].[DPC],
[MKTSEED].[LOT],
/* System fields */
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[MKTSEED].[DATEADDED],
[MKTSEED].[DATECHANGED],
[MKTSEED].[TSLONG]
/*#EXTENSION*/
from dbo.[MKTSEGMENTATIONSEED]
left outer join dbo.[MKTSEED] on [MKTSEED].[ID] = [MKTSEGMENTATIONSEED].[SEEDID]
left outer join dbo.[TITLECODE] on [MKTSEED].[TITLECODEID] = [TITLECODE].[ID]
left outer join dbo.[SUFFIXCODE] on [MKTSEED].[SUFFIXCODEID] = [SUFFIXCODE].[ID]
left outer join dbo.[COUNTRY] on [MKTSEED].[COUNTRYID] = [COUNTRY].[ID]
left outer join dbo.[STATE] on [MKTSEED].[STATEID] = [STATE].[ID]
--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]([MKTSEED].[ADDRESSBLOCK])) as [ADDRLINES]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [MKTSEED].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [MKTSEED].[CHANGEDBYID]