public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer) { return (String.Format( @" ALTER TABLE {0} Add {1}_bit bit GO UPDATE {0} SET {1}_bit = CASE WHEN {1} is null THEN 0 else 1 end GO ALTER TABLE {0} DROP column {1} GO EXEC sp_rename '{0}.{1}_bit', '{1}' , 'COLUMN' GO ", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName())); }
public void Check(ICheckNotifier notifier) { if (ColumnToDilute == null) { notifier.OnCheckPerformed(new CheckEventArgs("ColumnToDilute is null", CheckResult.Fail, null)); return; } if (ColumnToDilute.Destination != DiscardedColumnDestination.Dilute) { notifier.OnCheckPerformed(new CheckEventArgs("ColumnToDilute '" + ColumnToDilute.GetRuntimeName() + "' is not marked as DiscardedColumnDestination.Dilute", CheckResult.Fail, null)); } //Stamp out the type IDilutionOperation instance = null; try { var factory = new DilutionOperationFactory(ColumnToDilute); instance = factory.Create(Operation); } catch (Exception e) { notifier.OnCheckPerformed( new CheckEventArgs( "Could not create DilutionOperation of Type " + Operation + " using DilutionOperationFactory, see inner Exception for details", CheckResult.Fail, e)); } if (_loadStage != LoadStage.AdjustStaging) { notifier.OnCheckPerformed(new CheckEventArgs("Dilution can ONLY occur in load stage AdjustStaging, it is currently configured as load stage: " + _loadStage, CheckResult.Fail)); } if (instance != null) { instance.Check(notifier); } }
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer) { return (string.Format(@" IF OBJECT_ID('dbo.RemoveDodgyCharacters') IS NOT NULL DROP FUNCTION RemoveDodgyCharacters GO Create Function [dbo].[RemoveDodgyCharacters](@Temp VarChar(max)) Returns VarChar(max) AS Begin Declare @KeepValues as varchar(50) Set @KeepValues = '%[^A-Za-z0-9]%' While PatIndex(@KeepValues, @Temp) > 0 Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '') Return @Temp End GO IF OBJECT_ID('dbo.Left4OfPostcodes') IS NOT NULL DROP FUNCTION Left4OfPostcodes GO CREATE FUNCTION Left4OfPostcodes ( -- Add the parameters for the function here @str varchar(max) ) RETURNS varchar(4) AS BEGIN --Pass through nulls if @str IS NULL RETURN @str --Start by stripping out all dodgy characters (see method above) DECLARE @hackedStr varchar(max) set @hackedStr = dbo.RemoveDodgyCharacters(@str) --If the result is less than 3 characters, return the original string if LEN(@hackedStr) < 3 RETURN @str --Part we are about to discard DECLARE @discardedBit varchar(3) SET @discardedBit = RIGHT(@hackedStr,3) --http://www.mrs.org.uk/pdf/postcodeformat.pdf --PO1 3AX --Have we identified the 3AX bit correctly? if PATINDEX('[0-9][A-Za-z][A-Za-z]' ,UPPER(@discardedBit)) = 1 --Yes RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3) --Return the hacked string (no dodgy characters) minus the validated suffix --Suffix is missing or malformed but there is 5 or more characters so we aren't looking at 'DD3' we are looking at 'DD3 5L5' where the final digit should be a char but is an int by mistype if LEN(@hackedStr) > 4 AND LEN(@hackedStr) < 8 RETURN SUBSTRING(@hackedStr,1,LEN(@hackedStr)-3) RETURN @hackedStr --Else just return the hacked String (at least we did them the favour of removing dodgy characters and the varchar(4) means we probably fulfilled a contract reasonably anyway END GO UPDATE {0} SET {1}=dbo.Left4OfPostcodes({1}) GO", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName())); }
public override string GetMutilationSql(INameDatabasesAndTablesDuringLoads namer) { return(String.Format(@"IF OBJECT_ID('dbo.RoundDateToMiddleOfQuarter') IS NOT NULL DROP FUNCTION RoundDateToMiddleOfQuarter GO CREATE FUNCTION RoundDateToMiddleOfQuarter ( -- Add the parameters for the function here @DOB date ) RETURNS date AS BEGIN -- Declare the return variable here DECLARE @anonDOB date -- Add the T-SQL statements to compute the return value here IF MONTH(@DOB) IN (1,2,3) SET @anonDOB = LEFT(@DOB, 4) + '0215' ELSE IF MONTH(@DOB) IN (4,5,6) SET @anonDOB = LEFT(@DOB, 4) + '0515' ELSE IF MONTH(@DOB) IN (7,8,9) SET @anonDOB = LEFT(@DOB, 4) + '0815' ELSE IF MONTH(@DOB)IN (10,11,12) SET @anonDOB = LEFT(@DOB, 4) + '1115' ELSE SET @anonDOB = NULL -- Return the result of the function RETURN @anonDOB END GO UPDATE {0} SET {1}=dbo.RoundDateToMiddleOfQuarter({1}) GO", ColumnToDilute.TableInfo.GetRuntimeName(LoadStage.AdjustStaging, namer), ColumnToDilute.GetRuntimeName())); }