189 lines
5.5 KiB
Transact-SQL
189 lines
5.5 KiB
Transact-SQL
|
|
/****** Object: UserDefinedFunction [dbo].[FNCUST_GET_PROPERTY_VALUES] Script Date: 17.10.2021 19:11:04 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
-- T-SQL Table function to get WinLine property values
|
|
-- Requires min. SQL Version 2017!
|
|
|
|
-- See examples at the bottom!
|
|
|
|
-- Stand: MK // 19.10.2021
|
|
-- 19.10.2021 Description expanded
|
|
-- 17.10.2021 @mesocomp, @mesoyear and @ResultType implemented
|
|
-- 06.10.2021 Initial
|
|
|
|
alter FUNCTION [dbo].[FNCUST_GET_PROPERTY_VALUES] (
|
|
@PropertyID BIGINT, -- = eg 1011 or 1012, see (select [c000], [c010] from [CWLSYSTEM].[dbo].[T069CMP] where [C003] = 0 and [c006] = 0 and [c000] > 1000)
|
|
@ObjectID VARCHAR(200), -- = eg 10000002 = PersonalAccount; 4711 = ProductNr; 10i001 = PotentialBuyer
|
|
@PropertyTypeID BIGINT, -- = eg 3 = PersonalAccount; 1 = ProductNr; 5 = PotentialBuyer
|
|
@mesocomp VARCHAR(4), -- = OPTIONAL - Only valid in combination with @mesoyear
|
|
@mesoyear SMALLINT, -- = OPTIONAL - Only valid in combination with @mesocomp
|
|
@ResultType VARCHAR(20) -- = OPTIONAL - Determ if Result should be compressed in one line, see examples
|
|
)
|
|
RETURNS @vTB_RESULT TABLE ([MESOKEY] [BIGINT] NOT NULL,
|
|
[PROPERTY_COUNT] [BIGINT] NOT NULL,
|
|
[PROPERTY_VALUE] [VARCHAR](max) NULL)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @PropertyCounter BIGINT;
|
|
|
|
----------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
-- IF optional params are null
|
|
IF (@mesocomp is NULL) and (@mesoyear is NULL)
|
|
|
|
BEGIN
|
|
|
|
-- Count how many property values were found
|
|
SELECT @PropertyCounter = count(CountFunction.C010) FROM (
|
|
|
|
SELECT [CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
FROM [t070] (NOLOCK)
|
|
JOIN [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]
|
|
|
|
WHERE [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
|
|
and [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0
|
|
and [t070].[C000] = @ObjectID
|
|
and [t070].[C003] = @PropertyTypeID
|
|
|
|
GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
) as CountFunction
|
|
|
|
END
|
|
|
|
-- If optional params were set
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
-- Count how many property values were found and respect mesoyear and mesocomp
|
|
SELECT @PropertyCounter = count(CountFunction.C010) FROM (
|
|
|
|
SELECT [CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
FROM [t070] (NOLOCK)
|
|
JOIN [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]
|
|
|
|
WHERE [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
|
|
and [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0
|
|
and [t070].[C000] = @ObjectID
|
|
and [t070].[C003] = @PropertyTypeID
|
|
and [t070].[mesocomp] = @mesocomp
|
|
and [t070].[mesoyear] = @mesoyear
|
|
|
|
GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
) as CountFunction
|
|
|
|
END
|
|
|
|
----------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
IF (@PropertyCounter >= 1)
|
|
|
|
BEGIN
|
|
|
|
IF (@mesocomp is NULL) and (@mesoyear is NULL)
|
|
|
|
BEGIN
|
|
|
|
INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
|
|
|
|
SELECT [CWLSYSTEM].[dbo].[t069cmp].[MESOKEY],
|
|
@PropertyCounter,
|
|
[CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
FROM [t070] (NOLOCK)
|
|
JOIN [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]
|
|
|
|
WHERE [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
|
|
and [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0
|
|
and [t070].[C000] = @ObjectID
|
|
and [t070].[C003] = @PropertyTypeID
|
|
|
|
GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[mesokey],
|
|
[CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
END
|
|
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
|
|
|
|
SELECT [CWLSYSTEM].[dbo].[t069cmp].[MESOKEY],
|
|
@PropertyCounter,
|
|
[CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
FROM [t070] (NOLOCK)
|
|
JOIN [CWLSYSTEM].[dbo].[t069cmp] (NOLOCK) ON [t070].[c002] = [t069cmp].[C001]
|
|
|
|
WHERE [CWLSYSTEM].[dbo].[t069cmp].[c000] = @PropertyID
|
|
and [CWLSYSTEM].[dbo].[t069cmp].[C003] = 0
|
|
and [t070].[C000] = @ObjectID
|
|
and [t070].[C003] = @PropertyTypeID
|
|
and [t070].[mesocomp] = @mesocomp
|
|
and [t070].[mesoyear] = @mesoyear
|
|
|
|
GROUP BY [CWLSYSTEM].[dbo].[t069cmp].[mesokey],
|
|
[CWLSYSTEM].[dbo].[t069cmp].[c010]
|
|
|
|
END
|
|
|
|
END
|
|
|
|
ELSE
|
|
|
|
BEGIN
|
|
|
|
INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
|
|
|
|
SELECT 0, @PropertyCounter, NULL
|
|
|
|
END
|
|
|
|
----------------------------------------------------------------------------------------------------------------------------------
|
|
|
|
-- IF Result should be in one Line, delete temp table and use STRING_AGG function
|
|
IF (@ResultType is not NULL) and (@PropertyCounter > 1)
|
|
|
|
BEGIN
|
|
|
|
DECLARE @vTB_RESULT_TEMP TABLE ([MESOKEY] [BIGINT] NOT NULL,
|
|
[PROPERTY_COUNT] [BIGINT] NOT NULL,
|
|
[PROPERTY_VALUE] [VARCHAR](max) NULL)
|
|
|
|
INSERT INTO @vTB_RESULT_TEMP ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
|
|
SELECT * FROM @vTB_RESULT
|
|
|
|
DELETE FROM @vTB_RESULT
|
|
|
|
INSERT INTO @vTB_RESULT ([MESOKEY],[PROPERTY_COUNT],[PROPERTY_VALUE])
|
|
SELECT 9999999999, @PropertyCounter, (STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(PROPERTY_VALUE,'')), ';')) FROM @vTB_RESULT_TEMP;
|
|
|
|
RETURN;
|
|
|
|
END
|
|
|
|
RETURN;
|
|
|
|
END
|
|
|
|
|
|
-- ================ EXAMPLES ================
|
|
|
|
-- Get (minimum) 1 or n lines back
|
|
-- select * from [FNCUST_GET_PROPERTY_VALUES](1012,'10000002',3,null,null,null)
|
|
|
|
-- Get (always) 1 line back
|
|
-- select * from [FNCUST_GET_PROPERTY_VALUES](1012,'10000002',3,null,null,'not null')
|
|
|
|
-- ==========================================
|