V_QUERY_CONSTITUENTADDRESSNORELATIONSHIPS
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 | |
| ADDRESSVALIDATIONUPDATE_ORIGIN | nvarchar(9) | yes | Origin |
| ADDRESSVALIDATIONUPDATE_ORIGININFO | nvarchar(100) | yes | Origin information |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:26:31 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTADDRESSNORELATIONSHIPS 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,
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]
/*#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
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