V_QUERY_CONSTITUENTMARKETING
This query is used as a record source for the marketing component.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | System record ID | |
| NAME | nvarchar(154) | yes | Name |
| KEYNAME | nvarchar(100) | Last name/Organization name | |
| KEYNAMEPREFIX | nvarchar(50) | Key name prefix | |
| FIRSTNAME | nvarchar(50) | First name | |
| MIDDLENAME | nvarchar(50) | Middle name | |
| TITLECODEID_TRANSLATION | nvarchar(100) | yes | Title |
| SUFFIXCODEID_TRANSLATION | nvarchar(100) | yes | Suffix |
| TITLE2CODEID_TRANSLATION | nvarchar(100) | yes | Title 2 |
| SUFFIX2CODEID_TRANSLATION | nvarchar(100) | yes | Suffix 2 |
| GENDERCODEID_TRANSLATION | nvarchar(100) | yes | |
| AGE | int | yes | Age |
| GENDER | nvarchar(7) | yes | Gender |
| FIRSTGIFTDATE | datetimeoffset | yes | First gift date |
| ADDRESSID | uniqueidentifier | yes | Address ID |
| COUNTRYID_TRANSLATION | nvarchar(100) | yes | Country |
| COUNTRYID_ABBREVIATION | nvarchar(5) | yes | Country abbreviation |
| ADDRESSBLOCK | nvarchar(150) | yes | Address |
| 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 |
| STATEID_TRANSLATION | nvarchar(100) | yes | State |
| STATEID_ABBREVIATION | nvarchar(50) | yes | State abbreviation |
| POSTCODE | nvarchar(12) | yes | ZIP |
| CART | nvarchar(10) | yes | CART |
| DPC | nvarchar(8) | yes | DPC |
| LOT | nvarchar(5) | yes | LOT |
| ADDRESSEE | nvarchar(700) | yes | Addressee |
| SALUTATION | nvarchar(700) | yes | Salutation |
| CONTACT | nvarchar(700) | yes | Contact addressee |
| POSITION | nvarchar(100) | yes | Position |
| CONTACTID | uniqueidentifier | yes | Contact ID |
| MAILTOCONSTITUENTID | uniqueidentifier | yes | Mail to constituent ID |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | Email address |
| PHONENUMBER | nvarchar(100) | yes | Phone number |
| ISHOUSEHOLD | bit | Is household | |
| HEADOFHOUSEHOLD | bit | Head of household | |
| HOUSEHOLDID | uniqueidentifier | yes | Household 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 |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| ORGANIZATIONNAME | nvarchar(100) | ||
| CONTACTORADDRESSEE | nvarchar(700) | yes | |
| TITLECODEID | uniqueidentifier | yes | |
| SUFFIXCODEID | uniqueidentifier | yes | |
| TITLE2CODEID | uniqueidentifier | yes | |
| SUFFIX2CODEID | uniqueidentifier | yes | |
| GENDERCODEID | uniqueidentifier | yes | |
| COUNTRYID | uniqueidentifier | yes | |
| STATEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:18:26 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTMARKETING AS
select
[CONSTITUENT].[ID] as [CONSTITUENTID],
[CONSTITUENT].[NAME],
[CONSTITUENT].[KEYNAME],
[CONSTITUENT].[KEYNAMEPREFIX],
[CONSTITUENT].[FIRSTNAME],
[CONSTITUENT].[MIDDLENAME],
[TITLECODE1].[DESCRIPTION] as [TITLECODEID_TRANSLATION],
[SUFFIXCODE1].[DESCRIPTION] as [SUFFIXCODEID_TRANSLATION],
[TITLECODE2].[DESCRIPTION] as [TITLE2CODEID_TRANSLATION],
[SUFFIXCODE2].[DESCRIPTION] as [SUFFIX2CODEID_TRANSLATION],
[GENDERCODE].[DESCRIPTION] as [GENDERCODEID_TRANSLATION],
(case when exists (select top 1 1 from dbo.[DECEASEDCONSTITUENT] where [ID] = [CONSTITUENT].[ID]) then null else [CONSTITUENT].[AGE] end) as [AGE],
[CONSTITUENT].[GENDER],
(select min([DATE]) from dbo.[FINANCIALTRANSACTION] where [CONSTITUENTID] = [CONSTITUENT].[ID] and [TYPECODE] in (0,1,2,3,4,5,6,7,8,9) and [DELETEDON] is null) as [FIRSTGIFTDATE],
[ADDRESS].[ID] as [ADDRESSID],
[COUNTRY].[DESCRIPTION] as [COUNTRYID_TRANSLATION],
[COUNTRY].[ABBREVIATION] as [COUNTRYID_ABBREVIATION],
[ADDRESS].[ADDRESSBLOCK],
[ADDRLINES].[ADDRESSLINE1],
[ADDRLINES].[ADDRESSLINE2],
[ADDRLINES].[ADDRESSLINE3],
[ADDRLINES].[ADDRESSLINE4],
[ADDRLINES].[ADDRESSLINE5],
[ADDRESS].[CITY],
[STATE].[DESCRIPTION] as [STATEID_TRANSLATION],
[STATE].[ABBREVIATION] as [STATEID_ABBREVIATION],
[ADDRESS].[POSTCODE],
[ADDRESS].[CART],
[ADDRESS].[DPC],
[ADDRESS].[LOT],
(select
(case when [NAMEFORMATFUNCTIONID] is null
then
[CUSTOMNAME]
else
(case when [NAMEFORMATFUNCTIONID] = (select [ID] from dbo.[NAMEFORMATFUNCTION] where [FORMATSQLFUNCTION] = 'UFN_NAMEFORMATFUNCTION_SIMPLE')
then
dbo.[UFN_NAMEFORMATFUNCTION_SIMPLE]([CONSTITUENT].[ID], [CONSTITUENT].[KEYNAME], [CONSTITUENT].[FIRSTNAME], [CONSTITUENT].[MIDDLENAME], [TITLECODE1].[DESCRIPTION], [SUFFIXCODE1].[DESCRIPTION], '', '', '', '', '')
else
dbo.[UFN_NAMEFORMAT_FROMID]([NAMEFORMATFUNCTIONID], [CONSTITUENTID])
end)
end)
from dbo.[NAMEFORMAT]
where [CONSTITUENTID] = [CONSTITUENT].[ID]
and [PRIMARYADDRESSEE] = 1) as [ADDRESSEE],
(select
(case when [NAMEFORMATFUNCTIONID] is null
then
[CUSTOMNAME]
else
(case when [NAMEFORMATFUNCTIONID] = (select [ID] from dbo.[NAMEFORMATFUNCTION] where [FORMATSQLFUNCTION] = 'UFN_NAMEFORMATFUNCTION_SIMPLE')
then
dbo.[UFN_NAMEFORMATFUNCTION_SIMPLE]([CONSTITUENT].[ID], [CONSTITUENT].[KEYNAME], [CONSTITUENT].[FIRSTNAME], [CONSTITUENT].[MIDDLENAME], [TITLECODE1].[DESCRIPTION], [SUFFIXCODE1].[DESCRIPTION], '', '', '', '', '')
else
dbo.[UFN_NAMEFORMAT_FROMID]([NAMEFORMATFUNCTIONID], [CONSTITUENTID])
end)
end)
from dbo.[NAMEFORMAT]
where [CONSTITUENTID] = [CONSTITUENT].[ID]
and [PRIMARYSALUTATION] = 1) as [SALUTATION],
cast('' as nvarchar(700)) as [CONTACT],
cast('' as nvarchar(100)) as [POSITION],
cast(null as uniqueidentifier) as [CONTACTID],
cast(null as uniqueidentifier) as [MAILTOCONSTITUENTID],
[EMAILADDRESS].[EMAILADDRESS],
[PHONE].[NUMBER] as [PHONENUMBER],
isnull([CONSTITUENTHOUSEHOLD].[ISHOUSEHOLD], 0) as [ISHOUSEHOLD],
isnull([CONSTITUENTHOUSEHOLD].[ISPRIMARYMEMBER], 0) as [HEADOFHOUSEHOLD],
[CONSTITUENTHOUSEHOLD].[HOUSEHOLDID],
[ADDEDBY].[APPLICATIONNAME] as [ADDEDBY_APPLICATION],
[ADDEDBY].[USERNAME] as [ADDEDBY_USERNAME],
[CHANGEDBY].[APPLICATIONNAME] as [CHANGEDBY_APPLICATION],
[CHANGEDBY].[USERNAME] as [CHANGEDBY_USERNAME],
[CONSTITUENT].[DATEADDED],
[CONSTITUENT].[DATECHANGED],
[CONSTITUENT].[TSLONG],
[CONSTITUENT].[LOOKUPID],
case [CONSTITUENT].[ISORGANIZATION] when 1 then [CONSTITUENT].[KEYNAME] else '' end [ORGANIZATIONNAME],
cast('' as nvarchar(700)) as [CONTACTORADDRESSEE],
[CONSTITUENT].[TITLECODEID],
[CONSTITUENT].[SUFFIXCODEID],
[CONSTITUENT].[TITLE2CODEID],
[CONSTITUENT].[SUFFIX2CODEID],
[CONSTITUENT].[GENDERCODEID],
[ADDRESS].[COUNTRYID],
[ADDRESS].[STATEID]
/*#EXTENSION*/
from dbo.[CONSTITUENT]
left outer join dbo.[TITLECODE] as [TITLECODE1] on [TITLECODE1].[ID] = [CONSTITUENT].[TITLECODEID]
left outer join dbo.[SUFFIXCODE] as [SUFFIXCODE1] on [SUFFIXCODE1].[ID] = [CONSTITUENT].[SUFFIXCODEID]
left outer join dbo.[GENDERCODE] as [GENDERCODE] on [GENDERCODE].[ID] = [CONSTITUENT].[GENDERCODEID]
left outer join dbo.[TITLECODE] as [TITLECODE2] on [TITLECODE2].[ID] = [CONSTITUENT].[TITLE2CODEID]
left outer join dbo.[SUFFIXCODE] as [SUFFIXCODE2] on [SUFFIXCODE2].[ID] = [CONSTITUENT].[SUFFIX2CODEID]
/*###BEGIN_JOIN_ADDRESS###*/
left outer join dbo.[ADDRESS] on ([ADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [ADDRESS].[ISPRIMARY] = 1)
/*###END_JOIN_ADDRESS###*/
--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]([ADDRESS].[ADDRESSBLOCK])) as [ADDRLINES]
left outer join dbo.[COUNTRY] on [COUNTRY].[ID] = [ADDRESS].[COUNTRYID]
left outer join dbo.[STATE] on [STATE].[ID] = [ADDRESS].[STATEID]
/*###BEGIN_JOIN_EMAIL###*/
left outer join dbo.[EMAILADDRESS] on ([EMAILADDRESS].[CONSTITUENTID] = [CONSTITUENT].[ID] and [EMAILADDRESS].[ISPRIMARY] = 1)
/*###END_JOIN_EMAIL###*/
left outer join dbo.[PHONE] on ([PHONE].[CONSTITUENTID] = [CONSTITUENT].[ID] and [PHONE].[ISPRIMARY] = 1)
left outer join dbo.[CONSTITUENTHOUSEHOLD] on [CONSTITUENTHOUSEHOLD].[ID] = [CONSTITUENT].[ID]
left outer join dbo.[CHANGEAGENT] as [ADDEDBY] on [ADDEDBY].[ID] = [CONSTITUENT].[ADDEDBYID]
left outer join dbo.[CHANGEAGENT] as [CHANGEDBY] on [CHANGEDBY].[ID] = [CONSTITUENT].[CHANGEDBYID]
where [CONSTITUENT].[ISCONSTITUENT] = 1