1 Topic by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Topic: Aggregating statuses from subsites

We have a user function which produces a text string listing all the statuses of a particular location. I modified this from ufn_GetDesignations in order to get the desired result. It works fine.

Trouble is, I now want to modify it to return all statuses applied to the location and all of its subsites. Early attempts began with some code modified from the JNCC_L6 xml report, which creates a temporary table & loops through all the subsites of a location populating the table with location keys, then joins the table to others to extract the data needed.

My new code looks great, but I can't use it because I am informed that it is not possible to create temporary tables etc. within a user function.

Any suggestions of how to get around this would be gratefully received.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Aggregating statuses from subsites

The following creates a Table_valued function which return the Location  keys. You can use this in a join or sub query to limit the results to the location selected and it children etc, Table-Valued functions are only valid from SQl sever 2005.



USE [NBNData]
GO
/****** Object:  UserDefinedFunction [dbo].[Location_Sub_Sites]    Script Date: 17/12/2014 22:32:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Location_Sub_Sites] (@Locationkey varchar(16))
RETURNS @LocationHierarchy TABLE (LocationKey char(16)) 
AS
BEGIN
DECLARE @LocationList varchar(500)
DECLARE @PrevLocationList varchar(500)
DECLARE @LocationList2 varchar(500)
DECLARE @DesignationTypes varchar(8000)
DECLARE @x int
Set @LocationList = @LocationKey
Set @PrevLocationList = ''

While @PrevLocationList <> @LocationList
BEGIN
  SET @PrevLocationList = @LocationList
  SELECT @LocationList = @LOCATIONLIST + LOCATION_KEY FROM LOCATION
  WHERE @LOCATIONLIST LIKE '%'+PARENT_KEY+'%' AND  @LOCATIONLIST NOT LIKE '%'+LOCATION_KEY+'%'
END

  INSERT INTO @LocationHierarchy
  SELECT LOCATION_KEY FROM LOCATION WHERE
  @LocationList  LIKE '%'+LOCATION.LOCATION_KEY+'%'

  RETURN;
END

Mike Weideli

MikeWeideli's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Aggregating statuses from subsites

The following illustrates the use of the table-valued function above.  The examples finds all the Vice Counties. 

1. In a sub query

SELECT  Location_Name.ITEM_NAME FROM LOCATION_NAME
WHERE LOCATION_KEY IN(SELECT LH.LocationKEY FROM [dbo].[Location_Sub_Sites]('JNCCIMPW00000001') LH )

2. In  a join

Select * From Location L INNER JOIN dbo.Location_Sub_Sites( 'JNCCIMPW00000001')  LSS
ON LSS.LocationKey = L.Location_Key

If you install the udf in the post above you should be able to refer to it from within your udf which  returns the designations  I assume you already passing the location key to  your udf so you would need to use this parameter in plsce of the locatio key used in the above exampkes.

Mike Weideli

MikeWeideli's Website