[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 580: sizeof(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/phpbb/session.php on line 636: sizeof(): Parameter must be an array or an object that implements Countable
Unofficial UNIT-e Forum • TVF_AddressDetails
Page 1 of 1

TVF_AddressDetails

Posted: Thu Mar 02, 2017 10:36 pm
by robinwilson16
Splits the learner address into separate fields using the TVF_SplitStrings split strings function

Code: Select all

CREATE FUNCTION [dbo].[TVF_AddressDetails] (
    @AddressID numeric(16, 0)
)
RETURNS TABLE
AS

RETURN
/*
	DECLARE @AddressID numeric(16, 0) = 727000001678952
*/

	SELECT
		AddressLine1 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 1 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine2 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 2 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine3 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 3 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine4 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 4 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine5 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 5 THEN ADS.Value 
					ELSE NULL 
				END
			),
		PostCode = AD.a_reference
	FROM ulive.dbo.capd_address AD
	CROSS APPLY GC.dbo.TVF_SplitStrings ( AD.a_name, CHAR(10) ) ADS
	WHERE
		AD.a_id = @AddressID
	GROUP BY
		AD.a_reference