February 14, 2010 at 1:08 pm
Hi ,
I have a Rules table like;
RuleNo level Attribute Operation value
R1 1 FirstName like 'Dav'
R1 2 LastName like 'Lloyd'
R2 1 FirstName = 'Joh'
R2 2 LastName = 'Smith'
R2 2 LastName like 'Jo'
I want to develop query,function so that it returns clauses(where clause).
result like,
RuleNo Clause
R1 FirstName like 'Dav' and LastName like 'Lloyd'
R2 FirstName ='Joh' and (LastName='Smith' OR LastName like 'Jo' )
Need Help,
Thanks,
Fanindra
February 14, 2010 at 1:37 pm
Easy stuff first: In order to benefit from the LIKE statement you'd need to change like 'Dav' to like 'Dav%'.
How do you know if you have to use AND or OR?
The following would also be possible for R2:
R2 FirstName ='Joh' OR (LastName='Smith' OR LastName like 'Jo%' )
How would someone know which way to go?
Please note that you have to make sure that a value of " 'Jo';drop table myTable --" can never ever inserted.
February 14, 2010 at 8:15 pm
Thank you lmu92 very much for your reply on this,
Use of AND/OR is depend on level within RuleNo.
if level are same then use 'OR' otherwise 'AND' within RuleNo.
February 15, 2010 at 2:51 pm
Ok, here's what I came up with based on the sample data.
I'm not sure if it works under all circumstances... Maybe the CASE statements have to be tuned further.
Furthermore I added parenthesis even if there's just a single statement to make it not more complicated than it already is... 😉
DECLARE @t TABLE (
RuleNo CHAR(2),
levl INT,
Attribute VARCHAR(30),
Operation VARCHAR(30),
val VARCHAR(30)
)
INSERT INTO @t
SELECT 'R1', 1 ,'FirstName','like','''Dav''' UNION ALL
SELECT 'R1', 2 ,'LastName','like','''Lloyd''' UNION ALL
SELECT 'R2', 1 ,'FirstName','=','''Joh''' UNION ALL
SELECT 'R2', 2 ,'LastName','=','''Smith''' UNION ALL
SELECT 'R2', 2 ,'LastName','like','''Jo'''
;WITH cte1 AS
-- concatenate Attribute, Operation and val including numbering each string within a level
(
SELECT
RuleNo,
levl,
ROW_NUMBER() OVER(PARTITION BY RuleNo,levl ORDER BY levl ) AS ROW,
attribute + ' ' + Operation + ' ' + val AS sub
FROM @t t1
),
cte2 AS
-- calculate AND/OR operators and add parenthesis
(
SELECT ruleno,levl,ROW,
CASE WHEN ROW = 1 AND levl=1 THEN '('
ELSE '' END
+CASE
WHEN levl=1 THEN ''
WHEN levl>1 AND ROW = 1 THEN ') and ('
ELSE ' or ' END
+ sub
+CASE
WHEN levl=1 THEN ''
WHEN levl>1 AND ROW_NUMBER() OVER(PARTITION BY RuleNo,levl ORDER BY ROW DESC ) = 1 THEN ')'
ELSE '' END
AS res
FROM cte1
)
--final string concatenation using FOR XML PATH
SELECT
RuleNo,
(
SELECT '' + res
FROM cte2 cte2b
WHERE cte2b.RuleNo = cte2a.RuleNo
ORDER BY levl,ROW
FOR XML PATH('')
) as Clause
FROM cte2 cte2a
GROUP BY RuleNo
/* result set
RuleNoClause
R1(FirstName like 'Dav') and (LastName like 'Lloyd')
R2(FirstName = 'Joh') and (LastName = 'Smith' or LastName like 'Jo')
*/
February 15, 2010 at 9:49 pm
Thanks again lmu92 for your help,
I tried the following solution .
CREATE TABLE [dbo].[Rules](
[RuleNo] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[level] [int] NULL,
[Attribute] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Operation] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[value] [varchar](50) COLLATE Latin1_General_CI_AI NULL
)
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R1',1,'FirstName','like','Fan'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R1',2,'LastName','like','Bhor'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R2',1,'FirstName','=','Jonh'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R2',2,'LastName','=','Smith'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R2',2,'LastName','like','Joe'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R3',1,'Age','=','35'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R3',1,'FirstName','=','Bhushan'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R4',1,'Company','like','IBM'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R4',2,'Age','=','28'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R4',3,'ID','=','23424'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R4',3,'Wt','=','67'
insert Rules ( RuleNo,level,Attribute,Operation,value ) select 'R2',3,'ID','=','6767'
-------------------------------------------------------------------------------------
create view v1
as
SELECT RuleNo,level,Attribute +' '+ Operation +' '+case when isnumeric([value])=1 then [value] else + ''''+ [value]+
(case when Operation='like' then '%''' else '''' end ) end Clause from Rules
----------------------------------------------------------------------------
/*create function to concat of the column value*/
create function dbo.concate (@RuleNo varchar(4),@level int)
returns nvarchar(4000)
as
begin
declare @concat nvarchar(4000)
select @concat=coalesce(@concat,'')+Clause+' OR ' from V1 where RuleNo =@RuleNo and level=@level
select @concat = substring(@concat,1,len(@concat)-2)
return (@concat)
end
----------------------------------------------------------------------------
/*create function to concat of the column value*/
create function dbo.concate_AND (@RuleNo varchar(4))
returns nvarchar(4000)
as
begin
declare @concat nvarchar(4000)
declare @t as table
(
[RuleNo] [varchar](50) NULL,
[level] [int] NULL,
Clause [varchar](4000) NULL
)
insert @t
select RuleNo,level,+'('+ (select dbo.concate (RuleNo,level)) + ')' as Clause
from V1
group by RuleNo,level
select @concat=coalesce(@concat,'')+Clause+' AND ' from @t
where RuleNo =@RuleNo
select @concat = substring(@concat,1,len(@concat)-3)
return (@concat)
end
-----------------------------------------------------------------------
select RuleNo,(select dbo.concate_AND(RuleNo)) as Clause from v1
group by RuleNo order by RuleNo
----------------------------------------------------------------------------
Result :
RuleNo Clause
R1(FirstName like 'Fan%' ) AND (LastName like 'Bhor%' )
R2(FirstName = 'Jonh' ) AND (LastName = 'Smith' OR LastName like 'Joe%' ) AND (ID = 6767 )
R3(Age = 35 OR FirstName = 'Bhushan' )
R4(Company like 'IBM%' ) AND (Age = 28 ) AND (ID = 23424 OR Wt = 67 )
February 16, 2010 at 11:21 am
FANINDRA BHORTAKKE (2/15/2010)
Thanks again lmu92 for your help,I tried the following solution ....
So what did you get as results when compared to the solution I recommended?
And, if both solutions return the same (correct?) results, what did you see when comparing performance?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply