Exporting Address Line 1, 2 etc rather than as whole Address Block

Neil Hunter

 Hi Everyone

 Can anyone advise where or how it is possible to select just the address lines rather than an entire address block from the SQL server?

 I can see the address block in dbo.ADDRESS but can't see just the address lines in a table by itself. It is possible to split the Address Block field?

 Thanks once again for your help.

 Neil

Patrick Manning

Neil Hunter:

 Hi Everyone

 Can anyone advise where or how it is possible to select just the address lines rather than an entire address block from the SQL server?

 I can see the address block in dbo.ADDRESS but can't see just the address lines in a table by itself. It is possible to split the Address Block field?

 Thanks once again for your help.

 Neil

The RE database contains a 'Scaler-valued Function' called DBO.ADDRESLINES.  If you create a query in Raiser's Edge and output each of the address lines, this function is referenced.

Not sure how familiar you are with the SQL Server Management Studio but you can find that function within your Raiser's Edge database stucture, under Programmability, Functions, Scaler-Valued Functions.  I don't think you can reference this within certain programs (like Crystal Reports) but you can create a custom 'View' in SSMS that will segment the address lines.  This is how I wrote it, we have SQL Server 2008 R2:

CREATE VIEW [dbo].[CUSTOM_ADDRESS_Preferred]

AS

SELECT

RECORDS.CONSTITUENT_ID AS 'ConstitID',

RECORDS.IMPORT_ID AS 'ImportID',

dbo.CONSTIT_ADDRESS.IMPORT_ID AS 'Address ImportID',

dbo.ADDRESSLINE(1,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 1',

dbo.ADDRESSLINE(2,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 2',

dbo.ADDRESSLINE(3,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 3',

dbo.ADDRESS.CITY AS 'City',

dbo.ADDRESS.STATE AS 'State',

dbo.ADDRESS.POST_CODE AS 'Zip',

dbo.CONSTIT_ADDRESS.ID AS 'Address Link'

FROM

dbo.RECORDS AS RECORDS INNER JOIN

dbo.CONSTIT_ADDRESS ON RECORDS.ID = dbo.CONSTIT_ADDRESS.CONSTIT_ID INNER JOIN

dbo.ADDRESS ON dbo.CONSTIT_ADDRESS.ADDRESS_ID = dbo.ADDRESS.ID LEFT OUTER JOIN

dbo.TABLEENTRIES AS MSATABLE ON dbo.ADDRESS.REGION = MSATABLE.TABLEENTRIESID LEFT OUTER JOIN

dbo.TABLEENTRIES AS ADDTYPETABLE ON dbo.CONSTIT_ADDRESS.TYPE = ADDTYPETABLE.TABLEENTRIESID

WHERE

(RECORDS.IS_CONSTITUENT = - 1) AND (dbo.CONSTIT_ADDRESS.PREFERRED = - 1)

Neil Hunter

Patrick Manning:

The RE database contains a 'Scaler-valued Function' called DBO.ADDRESLINES.  If you create a query in Raiser's Edge and output each of the address lines, this function is referenced.

Not sure how familiar you are with the SQL Server Management Studio but you can find that function within your Raiser's Edge database stucture, under Programmability, Functions, Scaler-Valued Functions.  I don't think you can reference this within certain programs (like Crystal Reports) but you can create a custom 'View' in SSMS that will segment the address lines.  This is how I wrote it, we have SQL Server 2008 R2:

CREATE VIEW [dbo].[CUSTOM_ADDRESS_Preferred]

AS

SELECT

RECORDS.CONSTITUENT_ID AS 'ConstitID',

RECORDS.IMPORT_ID AS 'ImportID',

dbo.CONSTIT_ADDRESS.IMPORT_ID AS 'Address ImportID',

dbo.ADDRESSLINE(1,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 1',

dbo.ADDRESSLINE(2,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 2',

dbo.ADDRESSLINE(3,dbo.ADDRESS.ADDRESS_BLOCK) AS 'Address Line 3',

dbo.ADDRESS.CITY AS 'City',

dbo.ADDRESS.STATE AS 'State',

dbo.ADDRESS.POST_CODE AS 'Zip',

dbo.CONSTIT_ADDRESS.ID AS 'Address Link'

FROM

dbo.RECORDS AS RECORDS INNER JOIN

dbo.CONSTIT_ADDRESS ON RECORDS.ID = dbo.CONSTIT_ADDRESS.CONSTIT_ID INNER JOIN

dbo.ADDRESS ON dbo.CONSTIT_ADDRESS.ADDRESS_ID = dbo.ADDRESS.ID LEFT OUTER JOIN

dbo.TABLEENTRIES AS MSATABLE ON dbo.ADDRESS.REGION = MSATABLE.TABLEENTRIESID LEFT OUTER JOIN

dbo.TABLEENTRIES AS ADDTYPETABLE ON dbo.CONSTIT_ADDRESS.TYPE = ADDTYPETABLE.TABLEENTRIESID

WHERE

(RECORDS.IS_CONSTITUENT = - 1) AND (dbo.CONSTIT_ADDRESS.PREFERRED = - 1)

 Thank you Patrick, that's exactly what I was after.