retrieve meta-data inorder to make comparison

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

retrieve meta-data inorder to make comparison

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2004-04-21 : 11:52:45

Hi,
I'm building an application that will have some dynamic rule capabilities. Esentially I am building a data driven rule generator (for loss of a better name). The generator will consist of a number of different tables. In the tables, I will store metadata about a particular record. I will need to get the meta-data from these tables, query the appropriate records and then return the values inorder to make some comparisons. I'm curious if anyone has done anything like this before and might have some suggestions (I can power through it, but there might be an elegant way to do it). In a rule table I store a tableName and fieldName information. I need to build a procedure that will query this table, retrieve the values which will then be used inorder to get the required data.


-- rule table
CREATE TABLE [HomeEndorsementFieldRuleDetails] (
	[RuleName] [varchar] (20),
	[DataTable] [varchar] (50),
	[DataField] [varchar] (50),
	[StartRange] [varchar] (50),
	[EndRange] [varchar] (50),
	[DataType] [varchar] (10)
)
GO

-- table where I need to make a comparison
CREATE TABLE [QuoteDwellings] (
	[QuoteID] int,
	[FormType] [varchar] (20),
	[RoofType] [varchar] (20),
	-- other columns
)
GO

INSERT INTO HomeEndorsementFieldRuleDetails VALUES ('Rule1','QuoteDwellings','RoofType','Clay','Steel','str')
go
INSERT INTO HomeEndorsementFieldRuleDetails VALUES ('Rule1','QuoteDwellings','RoofType','Wood','Wood','str')
go
INSERT INTO HomeEndorsementFieldRuleDetails VALUES ('Rule1','QuoteDwellings','FormType','HO3','HO4','str')
go

INSERT INTO QuoteDwellings VALUES (100,'HO3','Wood')
go
INSERT INTO QuoteDwellings VALUES (101,'DP','Clay')
go

for the particular rule (rule1) I need to get the FormType and RoofType for a particular record inorder to make some comparisons. Depending on whether the comparison is true/false I will read from other tables to perform some action

So for QuoteID 100, I want to see if rule1 is valid. It is since both FormType and RoofType meet the comparison values (StartRange/EndRange allows me to make string comparisons as will as range (i.e. between 100 and 200) comparisons)

For QuoteID 101, rule1 fails. RoofType is true but FormType returns false.

This is a shot in the dark, but everyone in this forum have great ideas and was curious what people think.

This is my idea:
Create a sp that retrieves the table and field information which is put into a cursor, loop through the result set building the dynamic sql which will then return one select statement per comparison item. Then in the application code (C#) I will compare what is in the meta-data table with what was returned from the cursor. If the comparison is true, an action will be performed.
(I know cursors are generally bad but I don't see any other way, which is why I'm posting a question. I could return the data to C# and loop through it that way but why make two calls when I could do it in one sp (with a cursor))

Nic

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 12:12:30

Great Post!

[thud]

ouch

OMG

DDL and sample Data!

Gotta get up off the floor and climb on to my barst...uh office chari

[/thud]



    SELECT * 
      FROM QuoteDwellings q
INNER JOIN HomeEndorsementFieldRuleDetails r1
	ON q.FormType >= r1.StartRange
       AND q.FormType <= r1.StartRange
       AND r1.DataField = 'FormType'
INNER JOIN HomeEndorsementFieldRuleDetails r2
	ON q.RoofType >= r2.StartRange
       AND q.RoofType <= r2.StartRange
       AND r2.DataField = 'RoofType'

Brett

8-)

nic
Posting Yak Master

209 Posts

Posted - 2004-04-21 : 14:11:49

Thanks for the help. The problem is in the ruleDetails table the tableName and FieldName are dynamic. I need to read from that table and then use that data to retrieve the data from the "Quotes" tables. For each rule there may be a number of different records (for comparisons of different fields in different tables).

I need to return what is currently stored in the quotes table (in this example the QuoteDwellings) so I can make the comparison to see if the rule returns as true.

Here is a rough example (in reality there are more tables involved)


-- this will be part of a sp 
declare 
@sqlTxt varchar(1000)
,@dataField varchar(50)
,@dataTable varchar(50)
,@state varchar(2)
,@id int
,@maxId int
,@QuoteID int

-- this will be passed into the sp
set @QuoteID = 100

-- sp will also return all the field data that this result set will need to be compared against

-- create temp table
CREATE TABLE #EndorseRulesTemp
(
	id INT identity (1,1)
	,DataTable varchar(50)
	,DataField varchar(50)
) 

-- get table/field info and insert into temp table
-- in reality the ruls are associated with different companies etc (so there is a where clause etc)
INSERT #EndorseRulesTemp
(
	DataTable
	,DataField
) 
SELECT
	a.DataTable
	,a.DataField
FROM 
	HomeEndorsementFieldRuleDetails a	
GROUP BY
	a.DataTable
	,a.DataField

-- get ordinal positions of temp table
SELECT 
	@id = 0 
	,@maxid = MAX(id) 
FROM
	#EndorseRulesTemp 

WHILE @id < @maxid 
    BEGIN
	-- get next column id 
	SELECT @id = MIN(id) FROM #EndorseRulesTemp WHERE id > @id 
	-- retrieve data from temp table
	SELECT 
		@dataTable = DataTable
		,@dataField = DataField 
	FROM 
		#EndorseRulesTemp 
	WHERE
		id = @id 

	-- build data
	SELECT @sqlTxt = 'SELECT '
	SELECT @sqlTxt = @sqlTxt + '''' + @dataTable + ''' AS DataTable '
	SELECT @sqlTxt = @sqlTxt + ',''' + @dataField + ''' AS DataField '
	SELECT @sqlTxt = @sqlTxt + ',' + @dataField + ' AS DataValue ' 
	SELECT @sqlTxt = @sqlTxt + 'FROM ' + @dataTable + ' WHERE QuoteID = ' + CAST(@QuoteID AS varchar)
	--PRINT(@sqlTxt)
	EXEC(@sqlTxt)
    END

DROP TABLE #EndorseRulesTemp

Nic

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 14:31:34

I think it'd be easier if it was more normalized like this



CREATE TABLE [QuoteDwellings] (
	[QuoteID] int,
	[HousePartType]	varchar(20), 	
	[HousePart] [varchar] (20)
	-- No other columns
)
GO
INSERT INTO QuoteDwellings (QuoteID, HousePartType, HousePart) 
SELECT 100,'FormType','H03' UNION ALL
SELECT 100,'RoofType','Wood' UNION ALL
SELECT 101,'FormType','DP' UNION ALL
SELECT

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 15:44:07

I'm actually trying this...but I'm having a little trouble...

[CODE]
USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE [HomeEndorsementFieldRuleDetails] (
[RuleName] [varchar] (20),
[DataTable] [varchar] (50),
[DataField] [varchar] (50),
[StartRange] [varchar] (50),
[EndRange] [varchar] (50),
[DataType] [varchar] (10)
)
GO
INSERT INTO HomeEndorsementFieldRuleDetails([RuleName],[DataTable],[DataField],[StartRange],[EndRange],[DataType])
SELECT 'Rule1','QuoteDwellings','RoofType','Clay','Steel','str' UNION ALL
SELECT 'Rule1','QuoteDwellings','RoofType','Wood','Wood','str' UNION ALL
SELECT 'Rule1','QuoteDwellings','FormType','HO3','HO4','str'
GO

CREATE TABLE [QuoteDwellings] (
[QuoteID] int,
[HousePartType] varchar(20),
[HousePart] [varchar] (20),
[RowId] int IDENTITY(1,1)
-- No other columns
)
GO
INSERT INTO QuoteDwellings (QuoteID, HousePartType, HousePart)
SELECT 100,'FormType','HO3' UNION ALL
SELECT 100,'RoofType','Wood' UNION ALL
SELECT 101,'FormType','DP' UNION ALL
SELECT 101,'RoofType','Clay'
GO

SELECT * FROM QuoteDwellings

DECLARE @sql varchar(8000), @QuoteId int

SELECT @SQL = 'SELECT * FROM QuoteDwellings q ', @QuoteId = 100

SELECT @SQL = @SQL + 'INNER JOIN HomeEndorsementFieldRuleDetails r'+CONVERT(varchar(3),[RowId])
+ ' ON q.HousePart >= r'+CONVERT(varchar(3),[RowId])+'.StartRange'
+ ' AND q.HousePart <= r'+CONVERT(varchar(3),[RowId])+'.EndRange'
+ ' AND r'+CONVERT(varchar(3),[RowId])+'.DataField = ' + '''' + HousePartType+ ''''
FROM QuoteDwellings
WHERE QuoteID = @QuoteId

SELECT @SQL = @SQL + 'WHERE QuoteId = ' + CONVERT(varchar(10),@QuoteId)

SELECT @SQL
EXEC(@SQL)

SET NOCOUNT OFF
GO

DROP TABLE QuoteDwellings
DROP TABLE HomeEndorsementFieldRuleDetails
GO

[/CODE]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources