Navegando por en este blog encontre una funcion de SQL para convertir un filtro de navision en una clausula where de SQL. He modificado la funcion para que tenga en cuenta los condicionantes "| ". La cuelgo a continuacion:
ALTER FUNCTION [dbo].[CONVERT_NAV_FILTER]
(
-- Add the parameters for the function here
@parFieldName VARCHAR(MAX),
@parFilter VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @CurrentStringPOSPoints INT
DECLARE @CurrentStringPOSPipe INT
DECLARE @CurrentStringPOSAmp INT
DECLARE @CurrentStringPOSAt INT
DECLARE @CurrentStringPOSNot INT
DECLARE @CurrentStringPOSHaakOpen iNT
DECLARE @CurrentStringPOSHaaksluit int
DECLARE @CurrentStringPOSStar int
DECLARE @From VARCHAR(MAX)
DECLARE @To VARCHAR(MAX)
Declare @IndexToRead INT
Declare @ObjectToRead INT
Declare @IndexRead INT
Declare @ObjectRead INT
DECLARE @NextIndexToRead INT
Declare @NextObjectToRead INT
DECLARE @FirstRun INT
DECLARE @ReturnValue VARCHAR(MAX)
SET @ReturnValue = ''
SET @IndexToRead = 0
SET @CurrentStringPOSPoints = 0
SET @CurrentStringPOSPipe = 0
SET @CurrentStringPOSAmp = 0
SET @CurrentStringPOSAt = 0
SET @CurrentStringPOSNot = 0
SET @CurrentStringPOSHaakOpen = 0
SET @CurrentStringPOSHaaksluit = 0
SET @ObjectToRead = 0
SET @IndexRead = 0
SET @FirstRun = 1
WHILE 1 = 1
BEGIN
SET @NextIndexToRead = LEN(@parFilter)
SET @IndexToRead = LEN(@parFilter)
SET @ObjectToRead = 0
SET @NextObjectToRead = 0
--Find the index to read
IF @IndexToRead >= Charindex('..', @parFilter, @IndexRead + 1) and (Charindex('..', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('..', @parFilter, @IndexRead+1)
SET @ObjectToRead = 1
END
IF @IndexToRead >= Charindex('', @parFilter, @IndexRead + 1) and (Charindex('', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('', @parFilter, @IndexRead+1)
SET @ObjectToRead = 2
END
IF @IndexToRead >= Charindex('&', @parFilter, @IndexRead + 1) and (Charindex('&', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('&', @parFilter, @IndexRead+1)
SET @ObjectToRead = 3
END
IF @IndexToRead >= Charindex('|', @parFilter, @IndexRead + 1) and (Charindex('|', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('|', @parFilter, @IndexRead+1)
SET @ObjectToRead = 30
END
IF @IndexToRead >= Charindex('<>', @parFilter, @IndexRead + 1) and (Charindex('<>', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('<>', @parFilter, @IndexRead+1)
SET @ObjectToRead = 5
END
IF @IndexToRead >= Charindex('(', @parFilter, @IndexRead + 1) and (Charindex('(', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex('(', @parFilter, @IndexRead +1)
SET @ObjectToRead = 6
END
IF @IndexToRead >= Charindex(')', @parFilter, @IndexRead + 1) and (Charindex(')', @parFilter, @IndexRead + 1) > 0)
BEGIN
SET @IndexToRead = Charindex(')', @parFilter, @IndexRead +1)
SET @ObjectToRead = 7
END
-- Find the next index to read
IF @NextIndexToRead >= Charindex('..', @parFilter, @IndexToRead + 1) and (Charindex('..', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('..', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 1
END
IF @NextIndexToRead >= Charindex('', @parFilter, @IndexToRead + 1) and (Charindex('', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 2
END
IF @NextIndexToRead >= Charindex('&', @parFilter, @IndexToRead + 1) and (Charindex('&', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('&', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 3
END
IF @NextIndexToRead >= Charindex('|', @parFilter, @IndexToRead + 1) and (Charindex('|', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('|', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 30
END
IF @NextIndexToRead >= Charindex('<>', @parFilter, @IndexToRead + 1) and (Charindex('<>', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('<>', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 5
END
IF @NextIndexToRead >= Charindex('(', @parFilter, @IndexToRead + 1) and (Charindex('(', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex('(', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 6
END
IF @NextIndexToRead >= Charindex(')', @parFilter, @IndexToRead + 1) and (Charindex(')', @parFilter, @IndexToRead + 1) > 0)
BEGIN
SET @NextIndexToRead = Charindex(')', @parFilter, @IndexToRead+1)
SET @NextObjectToRead = 7
END
-- *********************************************
-- Checks Complete start converting
-- *********************************************
-- If this is the first time the conversion is done and there is a or & sign,
-- Set a filter on the first part
IF @FirstRun = 1 and @IndexToRead > 1
BEGIN
IF @ObjectToRead = 2 --
BEGIN
SET @From = Substring(@parFilter, 1, @IndexToRead - 1)
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '
END
IF @ObjectToRead = 3 -- &
BEGIN
SET @From = Substring(@parFilter, 1, @IndexToRead - 1)
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') AND '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') AND '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') AND '
END
IF @ObjectToRead = 30 -- |
BEGIN
SET @From = Substring(@parFilter, 1, @IndexToRead - 1)
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '
END
END
SET @FirstRun = 0
IF @ObjectToRead = 1 -- 1 = ..
BEGIN
SET @From = Substring(@parFilter, @IndexRead + 1, @IndexToRead - (@IndexRead + 1))
IF @nextobjecttoread = 0
SET @To = substring(@parFilter, @IndexToRead + 2 , @NextIndexToRead -(@IndexToRead + 1))
else
SET @To = substring(@parFilter, @IndexToRead + 2 , @NextIndexToRead -(@IndexToRead + 2))
if charindex('@', @From, 0) > 0 or charindex('@', @To, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') BETWEEN ''' + lower(replace(@From, '@', '')) + ''' AND ''' + lower(replace(@To,'@','')) + ''')'
ELSE
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' BETWEEN ''' + @From + ''' AND ''' + @To + ''')'
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
IF @NextObjectToRead = 30 -- &
SET @ReturnValue = @ReturnValue + ' OR '
END
IF @ObjectToRead = 2 -- 2 =
BEGIN
IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1
BEGIN
if @NextObjectToRead = 0
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)
else
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'
END
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
IF @NextObjectToRead = 30 -- &
SET @ReturnValue = @ReturnValue + ' OR '
END
IF @ObjectToRead = 3 -- 3 = &
BEGIN
IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1
BEGIN
if @NextObjectToRead = 0
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)
else
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%') , '@', '')) + ''')'
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'
END
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
IF @NextObjectToRead = 30 -- &
SET @ReturnValue = @ReturnValue + ' OR '
END
IF @ObjectToRead = 30 -- 3 = &
BEGIN
IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1
BEGIN
if @NextObjectToRead = 0
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)
else
SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%') , '@', '')) + ''')'
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'
END
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
IF @NextObjectToRead = 30 -- &
SET @ReturnValue = @ReturnValue + ' OR '
END
IF @ObjectToRead = 4 -- 4 = @
BEGIN
SET @ReturnValue = @ReturnValue
END
IF @ObjectToRead = 5 -- 5 = <>
BEGIN
SET @ReturnValue = @ReturnValue + ' NOT '
SET @From = substring(@parFilter, @IndexToRead + 2, @NextIndexToRead - (@IndexToRead + 2))
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
END
IF @ObjectToRead = 6 -- 6 = (
BEGIN
SET @ReturnValue = @ReturnValue + '('
SET @From = Substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead +1))
IF @NextObjectToRead = 2 --
BEGIN
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '
END
IF @NextObjectToRead = 3 -- &
BEGIN
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') AND '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') AND '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') AND '
ENd
IF @NextObjectToRead = 30 -- &
BEGIN
if charindex('@', @From, 0) > 0
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
else
if charindex('*', @From, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '
ENd
END
IF @ObjectToRead = 7 -- 7 = )
BEGIN
SET @ReturnValue = @ReturnValue + ')'
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
IF @NextObjectToRead = 30 -- &
SET @ReturnValue = @ReturnValue + ' OR '
END
IF @ObjectToRead = 8 -- 8 = *
BEGIN
SET @ReturnValue = @ReturnValue
END
-- Nothing found? => BREAK loop
IF @IndexToRead = LEN(@parFilter)
BREAK
SET @ObjectRead = @ObjectToRead
SET @IndexRead = @IndexToRead
SET @IndexToRead = @NextIndexToRead
END -- LOOP
IF @ReturnValue = ''
BEGIN
if charindex('@', @parFilter, 0) > 0
if charindex('*', @parFilter, 0) > 0
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@parFilter, '*', '%'), '@', '')) + ''')'
else
SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@parFilter, '@', '')) + ''')'
else
if charindex('*', @parFilter, 0) > 0
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@parFilter, '*', '%') + ''')'
else
SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @parFilter + ''')'
END
-- Return the result of the function
RETURN '(' + @ReturnValue + ')'
END
