V_QUERY_RE7_CONSTITUENT
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| LOCALID | int | ||
| FULLNAME | nvarchar(255) | yes | |
| FIRSTNAME | nvarchar(50) | ||
| MIDDLENAME | nvarchar(50) | ||
| LASTNAME | nvarchar(100) | yes | |
| ORGNAME | nvarchar(100) | yes | |
| KEYNAME | nvarchar(100) | yes | |
| MAIDENNAME | nvarchar(100) | yes | |
| NICKNAME | nvarchar(50) | yes | |
| TITLE | nvarchar(100) | yes | |
| SUFFIX | nvarchar(100) | yes | |
| SUFFIX2 | nvarchar(100) | yes | |
| COUNTRY | nvarchar(100) | yes | |
| CITY | nvarchar(50) | yes | |
| STATE | nvarchar(100) | yes | |
| STATEABBREVIATION | nvarchar(6) | yes | |
| POSTCODE | nvarchar(12) | yes | |
| ADDRESSBLOCK | nvarchar(150) | yes | |
| ADDRESSLINE1 | nvarchar(150) | yes | |
| ADDRESSLINE2 | nvarchar(150) | yes | |
| ADDRESSLINE3 | nvarchar(150) | yes | |
| ADDRESSLINE4 | nvarchar(150) | yes | |
| ADDRESSLINE5 | nvarchar(150) | yes | |
| COUNTY | nvarchar(100) | yes | |
| REGION | nvarchar(100) | yes | |
| DATELASTCHANGED | datetime | yes | |
| LOOKUPID | nvarchar(100) | ||
| ISHEADOFHOUSEHOLD | bit | yes | |
| HOUSEHOLDID | int | yes | |
| ISHOUSEHOLD | bit | ||
| BIRTHDATE | UDT_FUZZYDATE | ||
| DECEASEDDATE | UDT_FUZZYDATE | ||
| GENDERCODE | varchar(7) | ||
| MARITALSTATUS | nvarchar(100) | yes | |
| ISINACTIVE | bit | ||
| FIRSTGIFTDATE | datetime | yes | |
| CART | nvarchar(20) | yes | |
| DPC | nvarchar(8) | yes | |
| LOT | nvarchar(5) | yes | |
| ADDRESSTYPE | nvarchar(100) | yes | |
| SENDMAIL | bit | yes | |
| EMAILADDRESS | UDT_EMAILADDRESS | yes | |
| ADDRESSEE | nvarchar(2000) | yes | |
| SALUTATION | nvarchar(2000) | yes | |
| ETHNICITY | nvarchar(100) | yes | |
| PHONENUMBER | nvarchar(100) | yes | |
| INCOME | nvarchar(100) | yes | |
| KEYINDICATOR | char(1) | yes | |
| DONOTCALL | bit | yes | |
| ISDECEASED | bit | ||
| NOEMAIL | bit | ||
| NOVALIDADDRESS | bit | ||
| GIVESANONYMOUSLY | bit | ||
| NETCOMMUNITYMEMBER | bit | ||
| SOLICITOR | bit | ||
| ADDEDBYUSER_LOCALID | int | ||
| CHANGEDBYUSER_LOCALID | int | ||
| DATEADDED | datetime | ||
| IMPORTID | nvarchar(20) | yes | |
| CHANGEDBY | nvarchar(50) | yes | |
| ADDEDBY | nvarchar(50) | yes | |
| SSN | nvarchar(30) | yes | |
| AGE | int | yes | |
| RELIGION | nvarchar(100) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/6/2025 8:50:21 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3901.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RE7_CONSTITUENT AS
select
[C].[LOCALID],
[C].[FULLNAME],
[C].[FIRSTNAME],
[C].[MIDDLENAME],
[C].[LASTNAME],
[C].[ORGNAME],
[C].[KEYNAME],
[C].[MAIDENNAME],
[C].[NICKNAME],
[T].[DESCRIPTION] as [TITLE],
[SU].[DESCRIPTION] as [SUFFIX],
[SU2].[DESCRIPTION] as [SUFFIX2],
[CO].[DESCRIPTION] as [COUNTRY],
[A].[CITY],
[S].[DESCRIPTION] as [STATE],
[S].[ABBREVIATION] as [STATEABBREVIATION],
[A].[POSTCODE],
[A].[ADDRESSBLOCK],
[A].[ADDRESSLINE1],
[A].[ADDRESSLINE2],
[A].[ADDRESSLINE3],
[A].[ADDRESSLINE4],
[A].[ADDRESSLINE5],
[CC].[DESCRIPTION] [COUNTY],
[RC].[DESCRIPTION] [REGION],
[C].[DATELASTCHANGED],
[C].[LOOKUPID],
[C].[ISHEADOFHOUSEHOLD],
[C].[HOUSEHOLDID],
[C].[ISHOUSEHOLD],
[C].[BIRTHDATE],
[C].[DECEASEDDATE],
case [C].[GENDERCODE] when 1 then 'Male' when 2 then 'Female' else 'Unknown' end as [GENDERCODE],
[M].[DESCRIPTION] as [MARITALSTATUS],
[C].[ISINACTIVE],
[LTG].[FIRSTGIFTDATE],
[A].[CART],
[A].[DPC],
[A].[LOT],
[AT].[DESCRIPTION] [ADDRESSTYPE],
cast((case [A].[DONOTMAIL] when 1 then 0 else 1 end) as bit) [SENDMAIL],
[CD].[EMAILADDRESS],
[C].[ADDRESSEE],
[C].[SALUTATION],
[E].[DESCRIPTION] [ETHNICITY],
[CD].[PHONENUMBER],
[I].[DESCRIPTION] [INCOME],
[C].[KEYINDICATOR],
[CD].[DONOTCALL],
[C].[ISDECEASED],
[C].[NOEMAIL],
[C].[NOVALIDADDRESS],
[C].[GIVESANONYMOUSLY],
[C].[NETCOMMUNITYMEMBER],
[C].[SOLICITOR],
[C].[ADDEDBYUSER_LOCALID],
[C].[CHANGEDBYUSER_LOCALID],
[C].[DATEADDED],
[C].[IMPORTID],
[UC].[NAME] [CHANGEDBY],
[UA].[NAME] [ADDEDBY],
convert(nvarchar, DecryptByKey([CE].[SSN])) [SSN],
[CA].[AGE],
[REL].[DESCRIPTION] [RELIGION]
from
dbo.[RE7_CONSTITUENT] as [C]
left outer join dbo.[RE7_CONSTITUENTPREFERRED] as [CD] on [C].[LOCALID] = [CD].[LOCALID]
left outer join dbo.[RE7_ADDRESS] as [A] on [CD].[PREFERREDADDRESS_LOCALID] = [A].[LOCALID]
left outer join dbo.[RE7_ADDRESSTYPE] [AT] on [A].[ADDRESSTYPE_LOCALID] = [AT].[LOCALID]
left outer join dbo.[RE7_STATE] as [S] on [A].[STATE_LOCALID] = [S].[LOCALID]
left outer join dbo.[RE7_SUFFIXCODE] as [SU] on [C].[SUFFIXCODE_LOCALID] = [SU].[LOCALID]
left outer join dbo.[RE7_SUFFIXCODE] as [SU2] on [C].[SUFFIXCODE2_LOCALID] = [SU2].[LOCALID]
left outer join dbo.[RE7_TITLECODE] as [T] on [C].[TITLECODE_LOCALID] = [T].[LOCALID]
left outer join dbo.[RE7_COUNTRY] as [CO] on [A].[COUNTRY_LOCALID] = [CO].[LOCALID]
left outer join dbo.[RE7_COUNTYCODE] [CC] on [A].[COUNTY_LOCALID] = [CC].[LOCALID]
left outer join dbo.[RE7_REGIONCODE] [RC] on [A].[REGION_LOCALID] = [RC].[LOCALID]
left outer join dbo.[RE7_MARITALSTATUSCODE] as [M] on [C].[MARITALSTATUSCODE_LOCALID] = [M].[LOCALID]
left outer join dbo.[RE7_ETHNICITYCODE] as [E] on [C].[ETHNICITYCODE_LOCALID] = [E].[LOCALID]
left outer join dbo.[RE7_INCOMECODE] [I] on [C].[INCOMECODE_LOCALID] = [I].[LOCALID]
left outer join dbo.[RE7_CONSTITUENTLTGIVING] [LTG] on [C].[LOCALID] = [LTG].[CONSTITUENT_LOCALID]
left outer join dbo.[RE7_USER] [UC] on [C].[CHANGEDBYUSER_LOCALID] = [UC].[LOCALID]
left outer join dbo.[RE7_USER] [UA] on [C].[ADDEDBYUSER_LOCALID] = [UA].[LOCALID]
left outer join dbo.[RE7_CONSTITUENTENCRYPTED] [CE] on [C].[LOCALID] = [CE].[LOCALID]
left outer join dbo.[RE7_CONSTITUENTAGE] [CA] on [C].[LOCALID] = [CA].[LOCALID]
left outer join dbo.[RE7_RELIGIONCODE] [REL] on [C].[RELIGIONCODE_LOCALID] = [REL].[LOCALID]
where
[C].[ISCONSTITUENT] = 1;