V_QUERY_CONSTITUENTADDRESS
This provides the ability to query address information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| ADDRESSTYPECODEID_TRANSLATION | nvarchar(100) | yes | Type |
| ISPRIMARY | bit | Primary address | |
| DONOTMAIL | bit | Do not mail | |
| STARTDATE | UDT_MONTHDAY | Seasonal start date | |
| ENDDATE | UDT_MONTHDAY | Seasonal end date | |
| COUNTRYID_TRANSLATION | nvarchar(100) | yes | Country |
| COUNTRYID_ABBREVIATION | nvarchar(5) | yes | Country abbreviation |
| ADDRESSBLOCK | nvarchar(150) | Address | |
| CITY | nvarchar(50) | City | |
| STATEID_TRANSLATION | nvarchar(100) | yes | State |
| STATEID_ABBREVIATION | nvarchar(50) | yes | State abbreviation |
| POSTCODE | nvarchar(12) | ZIP | |
| CART | nvarchar(10) | CART | |
| DPC | nvarchar(8) | DPC | |
| LOT | nvarchar(5) | LOT | |
| NCOALASTSUBMITDATE | datetime | yes | NCOA Last submit date |
| NCOARETURN | nvarchar(72) | yes | NCOA Return code |
| NCOAFOOTNOTE | nvarchar(69) | yes | NCOA Footnote |
| NCOADPVFOOTNOTE | nvarchar(145) | yes | NCOA DPV footnote |
| NCOAMOVEDATE | UDT_FUZZYDATE | yes | NCOA Move date |
| NCOADMASUPPRESSION | bit | yes | NCOA DMA suppression |
| NCOAMAILGRADE | nvarchar(44) | yes | NCOA Mail grade |
| OMITFROMVALIDATION | bit | yes | Omit from validation |
| COUNTYCODEID_TRANSLATION | nvarchar(100) | yes | County |
| CONGRESSIONALDISTRICTCODEID_TRANSLATION | nvarchar(100) | yes | Congressional district |
| STATEHOUSEDISTRICTCODEID_TRANSLATION | nvarchar(100) | yes | State house district |
| STATESENATEDISTRICTCODEID_TRANSLATION | nvarchar(100) | yes | State senate district |
| LOCALPRECINCTCODEID_TRANSLATION | nvarchar(100) | yes | Local precinct |
| INFOSOURCECODEID_TRANSLATION | nvarchar(100) | yes | Information source |
| REGIONCODEID_TRANSLATION | nvarchar(100) | yes | Region |
| LASTVALIDATIONATTEMPTDATE | datetime | yes | Last validation attempt date |
| VALIDATIONMESSAGE | nvarchar(200) | yes | Validation message |
| 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 |
| DONOTMAILREASONCODEID_TRANSLATION | nvarchar(100) | yes | Reason to not mail |
| FULLADDRESS | nvarchar(300) | yes | Full Address |
| HISTORICALSTARTDATE | date | yes | Start date |
| HISTORICALENDDATE | date | yes | End date |
| INFOSOURCECOMMENTS | nvarchar(256) | yes | Information source comments |
| ISCONFIDENTIAL | bit | Confidential | |
| RELATIONSHIPID | uniqueidentifier | yes | Relationship ID |
| ADDRESSVALIDATIONUPDATE_ORIGIN | nvarchar(9) | yes | Origin |
| ADDRESSVALIDATIONUPDATE_ORIGININFO | nvarchar(100) | yes | Origin information |
| ADDRESSLINE1 | nvarchar(150) | yes | |
| ADDRESSLINE2 | nvarchar(150) | yes | |
| ADDRESSLINE3 | nvarchar(150) | yes | |
| ADDRESSLINE4 | nvarchar(150) | yes | |
| ADDRESSLINE5 | nvarchar(150) | yes | |
| ADDRESSTYPECODEID | uniqueidentifier | yes | |
| COUNTYCODEID | uniqueidentifier | yes | |
| CONGRESSIONALDISTRICTCODEID | uniqueidentifier | yes | |
| STATEHOUSEDISTRICTCODEID | uniqueidentifier | yes | |
| STATESENATEDISTRICTCODEID | uniqueidentifier | yes | |
| LOCALPRECINCTCODEID | uniqueidentifier | yes | |
| INFOSOURCECODEID | uniqueidentifier | yes | |
| REGIONCODEID | uniqueidentifier | yes | |
| DONOTMAILREASONCODEID | uniqueidentifier | yes | |
| COUNTRYID | uniqueidentifier | ||
| STATEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:46 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTADDRESS AS
select
ADDRESS.ID,
ADDRESS.CONSTITUENTID,
ADDRESSTYPECODE.DESCRIPTION as [ADDRESSTYPECODEID_TRANSLATION],
ADDRESS.ISPRIMARY,
ADDRESS.DONOTMAIL,
ADDRESS.STARTDATE,
ADDRESS.ENDDATE,
COUNTRY.DESCRIPTION as [COUNTRYID_TRANSLATION],
COUNTRY.ABBREVIATION as [COUNTRYID_ABBREVIATION],
ADDRESS.ADDRESSBLOCK,
ADDRESS.CITY,
STATE.DESCRIPTION as [STATEID_TRANSLATION],
STATE.ABBREVIATION as [STATEID_ABBREVIATION],
ADDRESS.POSTCODE,
ADDRESS.CART,
ADDRESS.DPC,
ADDRESS.LOT,
ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE,
ADDRESSVALIDATIONUPDATE.NCOARETURN,
ADDRESSVALIDATIONUPDATE.NCOAFOOTNOTE,
ADDRESSVALIDATIONUPDATE.NCOADPVFOOTNOTE,
ADDRESSVALIDATIONUPDATE.NCOAMOVEDATE,
ADDRESSVALIDATIONUPDATE.NCOADMASUPPRESSION,
ADDRESSVALIDATIONUPDATE.NCOAMAILGRADE,
ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION,
COUNTYCODE.Description as [COUNTYCODEID_TRANSLATION],
CONGRESSIONALDISTRICTCODE.Description as [CONGRESSIONALDISTRICTCODEID_TRANSLATION],
STATEHOUSEDISTRICTCODE.Description as [STATEHOUSEDISTRICTCODEID_TRANSLATION],
STATESENATEDISTRICTCODE.Description As [STATESENATEDISTRICTCODEID_TRANSLATION],
LOCALPRECINCTCODE.Description As [LOCALPRECINCTCODEID_TRANSLATION],
INFOSOURCECODE.Description As [INFOSOURCECODEID_TRANSLATION],
REGIONCODE.Description As [REGIONCODEID_TRANSLATION],
ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE,
ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
ADDRESS.DATEADDED,
ADDRESS.DATECHANGED,
ADDRESS.TSLONG,
DONOTMAILREASONCODE.DESCRIPTION AS [DONOTMAILREASONCODEID_TRANSLATION],
dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK,ADDRESS.CITY,STATE.ID,ADDRESS.POSTCODE,COUNTRY.ID) as [FULLADDRESS],
ADDRESS.HISTORICALSTARTDATE,
ADDRESS.HISTORICALENDDATE,
ADDRESSVALIDATIONUPDATE.INFOSOURCECOMMENTS,
ADDRESS.ISCONFIDENTIAL,
ADDRESS.RELATIONSHIPID,
ADDRESSVALIDATIONUPDATE.ORIGIN as ADDRESSVALIDATIONUPDATE_ORIGIN,
case
when ADDRESSVALIDATIONUPDATE.ORIGINCODE <> 0 then
dbo.UFN_ADDRESSVALIDATIONUPDATE_ORIGINCODE_GETDESCRIPTION(ADDRESSVALIDATIONUPDATE.ORIGINCODE)
else INFOSOURCECODE.DESCRIPTION
end as [ADDRESSVALIDATIONUPDATE_ORIGININFO],
[ADDRLINES].[ADDRESSLINE1],
[ADDRLINES].[ADDRESSLINE2],
[ADDRLINES].[ADDRESSLINE3],
[ADDRLINES].[ADDRESSLINE4],
[ADDRLINES].[ADDRESSLINE5],
[ADDRESS].[ADDRESSTYPECODEID],
[COUNTYCODE].[ID] as [COUNTYCODEID],
[CONGRESSIONALDISTRICTCODE].[ID] as [CONGRESSIONALDISTRICTCODEID],
[STATEHOUSEDISTRICTCODE].[ID] as [STATEHOUSEDISTRICTCODEID],
[STATESENATEDISTRICTCODE].[ID] as [STATESENATEDISTRICTCODEID],
[LOCALPRECINCTCODE].[ID] as [LOCALPRECINCTCODEID],
[INFOSOURCECODE].[ID] as [INFOSOURCECODEID],
[REGIONCODE].[ID] as [REGIONCODEID],
[DONOTMAILREASONCODE].[ID] as [DONOTMAILREASONCODEID],
[ADDRESS].[COUNTRYID],
[ADDRESS].[STATEID]
/*#EXTENSION*/
from dbo.ADDRESS
left outer join dbo.ADDRESSTYPECODE on ADDRESSTYPECODE.ID = ADDRESS.ADDRESSTYPECODEID
left outer join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
left outer join dbo.STATE on STATE.ID = ADDRESS.STATEID
--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.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
left outer join dbo.COUNTYCODE on ADDRESSVALIDATIONUPDATE.COUNTYCODEID = COUNTYCODE.ID
left outer join dbo.CONGRESSIONALDISTRICTCODE on ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODE.ID
left outer join dbo.STATEHOUSEDISTRICTCODE on ADDRESSVALIDATIONUPDATE.STATEHOUSEDISTRICTCODEID = STATEHOUSEDISTRICTCODE.ID
left outer join dbo.STATESENATEDISTRICTCODE on ADDRESSVALIDATIONUPDATE.STATESENATEDISTRICTCODEID = STATESENATEDISTRICTCODE.ID
left outer join dbo.LOCALPRECINCTCODE on ADDRESSVALIDATIONUPDATE.LOCALPRECINCTCODEID = LOCALPRECINCTCODE.ID
left outer join dbo.INFOSOURCECODE on ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID = INFOSOURCECODE.ID
left outer join dbo.REGIONCODE on ADDRESSVALIDATIONUPDATE.REGIONCODEID = REGIONCODE.ID
--pdg 3.15.2007 use outer join so if the field is not included CHANGEAGENT will not be in the plan.
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = ADDRESS.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = ADDRESS.CHANGEDBYID
left outer join dbo.DONOTMAILREASONCODE ON DONOTMAILREASONCODE.ID = ADDRESS.DONOTMAILREASONCODEID