November 18, 2008 at 4:39 pm
We have in our system a table that contains a Free Text field containing Conditional logic (not sure if this is the correct way to go about it, but I've inherited the system).
As part of our new release, the schema has changed with the removal of one Entity and replaced with more specialised Entities and we need to modify this field to use the new Entities, and I'm having a bit of trouble attempting this.
Unfortunately, this step was missed as part of the update procedure and is required to be written rather quickly.
More, this step needs to run prior to other steps being run (dont ask me how the developer managed to test the migration!).
Direct hard-coding string replacement is not an option as we need to run this is 3 global regions and new Letters are being created every day.
Sample Data
create table dbo.IndemnityLetters
(
LetterID int
,Condition nvarchar(4000)
)
insert into dbo.IndemnityLetters (LetterID ,Condition)
select 1, ''
union
select 2, 'PlaceOfIncorporation Contains "Canada"'
union
select 42, 'IndemnityType = "BLAH1" AND HomeSite = "CIT" OR HomeSite = "UCG" AND ValueTrans "CGP"'
union
select 45, 'IndemnityType = "BLAH2" AND Homesite <> "CIT"'
create table dbo.CNV_HomeSiteMapping
(
HomeSite nvarchar(20)
,GroupID int
,GroupDesc nvarchar(20)
)
insert into dbo.CNV_HomeSiteMapping (HomeSite ,GroupID ,GroupDesc)
select 'CIT', 2, 'Mapping1'
union
select 'UCG', 5, 'Mapping2'
The change that needs to be made is to replace all {[font="Courier New"]HomeSite = "HomeSiteValue"[/font]} conditions with {[font="Courier New"]GroupID = GroupIDValue[/font]} where the "[font="Courier New"]HomeSiteValue[/font]" in the condition matches the "[font="Courier New"]HomeSite[/font]" column of CNV_HomeSiteMapping.
ie
[font="Courier New"]'IndemnityType = "BLAH1" AND HomeSite = "CIT" OR HomeSite = "UCG" AND ValueTrans "CGP"'[/font]
becomes
[font="Courier New"]'IndemnityType = "BLAH1" AND GroupID = 2 OR GroupID = 5 AND ValueTrans "CGP"'[/font]
I've managed to create a table with the following contents (albeit with some very messy code), but now I'm stuck.
LetterIDConditionIndexOperator Condition Joiner
---------------------------------------------------------------------------------------------------
42 0 IndemnityType = BLAH1 AND
42 1 = CIT OR
42 2 = UCG AND ValueTrans "CGP"
45 0 IndemnityType = BLAH2 AND
45 1 = CIT
-- John Oliver
Sometimes banging your head against a wall is the only solution.
November 18, 2008 at 5:17 pm
To clarify:
Are you looking for something to clean up all existing data that would run on a scheduled basis... or something that corrects the entries as they go into the table? Would either work? Is one or the other preferable?
November 18, 2008 at 5:21 pm
The script only needs to run as a once off - pre upgrade (if that make sense) - but needs to run over a number of databases globally.
The task is to fix the data to match the new schema.
-- John Oliver
Sometimes banging your head against a wall is the only solution.
November 18, 2008 at 5:59 pm
You mentioned that hardcoding string replacement wasn't an option... why is that? My first thought with something like this would be to make a lookup table of what to replace with what.
For example:
[font="Courier New"]
IF OBJECT_ID('tempdb..#OriginalTable') IS NOT NULL DROP TABLE #OriginalTable
IF OBJECT_ID('GroupReplace') IS NOT NULL DROP TABLE GroupReplace
IF OBJECT_ID('tempdb..#GroupReplace') IS NOT NULL DROP TABLE #GroupReplace
IF OBJECT_ID('tempdb..#GroupReplaceMod') IS NOT NULL DROP TABLE #GroupReplaceMod
------- Create Sample Data Table --------------------------
CREATE TABLE #OriginalTable(
Letter VARCHAR(10),
Condition VARCHAR(100))
INSERT INTO #OriginalTable(Letter,Condition)
SELECT 'A','IndemnityType = "BLAH1" AND HomeSite = "CIT" OR HomeSite = "UCG" AND ValueTrans "CGP"' UNION ALL
SELECT 'B','IndemnityType = "BLAH1" AND HomeSite = "CIT" OR HomeSite <> "UCG" AND ValueTrans "CGP"' UNION ALL
SELECT 'C','IndemnityType = "BLAH1" AND HomeSite <> "CIT" OR HomeSite = "UCG" AND ValueTrans "CGP"'
-----------------------------------------------------------
----- Grab all the HomeSite/Group ID conversions ----------
CREATE TABLE GroupReplace(
GRID INT IDENTITY(1,1) PRIMARY KEY,
OrigValue VARCHAR(100),
NewValue VARCHAR(100))
INSERT INTO GroupReplace(OrigValue,NewValue)
SELECT 'HomeSite = "CIT"','GroupID = 2' UNION ALL
SELECT 'HomeSite = "UCG"','GroupID = 5'
-----------------------------------------------------------
---------- Grab a snapshot of this table for Replaces -----
SELECT OrigValue,NewValue
INTO #GroupReplace
FROM GroupReplace
-----------------------------------------------------------
---------- Repeat this as many times as necessary for all the different ways Homesite could be searched (<,>,<>,=,LIKE)
SELECT *
INTO #GroupReplaceMod
FROM #GroupReplace
UPDATE #GroupReplaceMod
SET OrigValue = REPLACE(OrigValue,'=','<>'),
NewValue = REPLACE(NewValue ,'=','<>')
INSERT INTO GroupReplace(OrigValue,NewValue)
SELECT OrigValue,NewValue FROM #GroupReplaceMod
DROP TABLE #GroupReplaceMod
-----------------------------------------------------------
------- Declare / Initialize Variables for Loop -----------
DECLARE @C INT,
@Orig VARCHAR(100),
@New VARCHAR(100)
SET @C = 1
-----------------------------------------------------------
---- Loop through the replace table and change values------
WHILE @C <= (SELECT MAX(GRID) FROM GroupReplace)
BEGIN
SELECT @Orig = OrigValue,
@New = NewValue
FROM GroupReplace
WHERE GRID = @C
UPDATE #OriginalTable
SET Condition = REPLACE(Condition,@Orig,@New)
FROM #OriginalTable
SET @C = @C + 1
END
-----------------------------------------------------------
SELECT * FROM GroupReplace
SELECT * FROM #OriginalTable[/font]
It would also make this way easier if you did some basic standardization first... for example, change anything like "a=b","a =b","a= b","a = b" to "a = b"
November 18, 2008 at 7:54 pm
Thanks Garadin for the reply.
We are unable to use the hard coded strings as there are a few thousand records that need to be updated in each database and there are a number of different mappings that need to be performed (not just HomeSite).
I believe one of our developers has managed to find a solution.
Basically, the solution updates the first instance where mapping needs to be performed, then loop through again until no more records are found that require updating.
It's certainly not pretty, but it seems to work.
while @v_ContinueFlag = 'Y'
begin
insert into #IndemnityLetters (HomeSite, HomeSiteStartPos, HomeSiteEndPos, HomeSiteOperator, {same set of fields for other mappings})
select
''
,case charindex('HomeSite = "', Condition)
when 0 then
case charindex('HomeSite <> "', Condition)
when 0 then 0
else charindex('HomeSite <> "', Condition) + 13 --select len('HomeSite <> "')
end
else charindex('HomeSite = "', Condition) + 12 --select len('HomeSite = "')
end
,-1
,case charindex('HomeSite = "', Condition)
when 0 then
case charindex('HomeSite <> "', Condition)
when 0 then ''
else '<>'
end
else '='
end
from dbo.IndemnityLetters
where Condition like '%HomeSite%'
if exists (select 1 from #IndemnityLetters)
begin
-- Update temp table HomeSiteEndPos value
-- Update temp talbe HomeSite value
-- Update real table using lookup table
delete from #IndemnityLetters
end
else
set @v_ContinueFlag = 'N'
end
-- John Oliver
Sometimes banging your head against a wall is the only solution.
November 18, 2008 at 9:30 pm
I don't really understand how that's going to work better than the method I proposed. Don't get me wrong, it doesn't bother me that you're going a different route, I just don't see where that one has any advantage over mine. With my method, you identify what needs to be changed to what (which you will have to do with any method), and then loop through and change it. I have a feeling the loop is unnecessary and could be replaced fairly easily, but since this is a one shot deal I didn't sweat it too much.
The number of records to be changed doesn't matter, the only thing that takes time is inputting what needs to be changed to what. (the initial a = b input.) The other equalities are just duplicated and re-added. There's even a high possibility you can pull most of it into the replace table via existing reference tables. While I'm by no means convinced that what I proposed is the best way to do this, I don't understand why nested case statements which will get more complicated with each added qualifier and have to be re-written for every mapping beat it. :blink:
Could you explain?
November 18, 2008 at 10:11 pm
Ok, penny has now dropped.
You are not suggesting to modify the entire condition, but extending the mapping table to include the "HomeSite = 'CIT'", "HomeSite <> 'CIT' with "GroupID = 2", "GroupID = 5" then performing a simple replace (sorry, late night last night and busy with other migration work).
I reckon that'd work.
I understand your concern regarding the complexity of the case statements, and is certainly not the method I was attempting last night (before I got stuck). I'll send your input on to the devs.
Thankyou Seth for your input and throwing that penny hard enough!
-- John Oliver
Sometimes banging your head against a wall is the only solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply