Ice.SplitDelimString function in BAQs

rhuebnerNNI · August 16, 2024, 12:09pm

Hello,

I’m experimenting with using the Ice.SplitDelimString function in an external BAQ and am having some trouble getting it to work. So, I was curious if anyone had any luck using this and how they were able to get it to work.

josecgomez · August 16, 2024, 1:33pm

Those helpers in there are SQL functions and they are in the context of your Query. They live in the Epicor DB, if you are using an external Db those helpers will not work.

You have to use your own function here’s the one I use in my external Db

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Function[SplitDelimString_EXT]
 (
    @stringIn NVARCHAR(4000),
    @delimiterChar NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@delimiterChar,@stringIn) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@delimiterChar,@stringIn,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 
        'value' = SUBSTRING(@stringIn,stpos,COALESCE(NULLIF(endpos,0),LEN(@stringIn)+1)-stpos)
    FROM Split
)
GO

Once you add it in, then you can use it.

rhuebnerNNI · August 16, 2024, 1:42pm

I’m using an external BAQ, but it is pointing to the main Epicor database, these functions are available to me, but I’m not sure how to get a value from a column for the parameter of this function.