February 5, 2013 at 9:54 am
I have a process like this:
STEP 1: Data files get loaded into a staging database.
STEP 2: Phonetic Keys table get populated based on incoming data file.
Now the Keys table I've designed never changes. It has the following columns (simplified):
RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise
The incoming data file could have any of the following columns:
Forrename, Surname, Address1, Address2, Address3, Town, PostCode
In my stored procedure which populates the keys table for the new file, I have a number of IF statements which check the column names to build the SELECT string.
For example:
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.DoubleMetaPhone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.DoubleMetaPhone(Surname)'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.DoubleMetaPhone(dbo.GetFirstWord(Forename))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.DoubleMetaPhone(dbo.GetSecondWord(Forename))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
END
Is this an okay approach or am I over-complicating things?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 6, 2013 at 4:58 am
Not sure if I explained this very well so I will try again!
I have a table with the following columns:
TABLE NAME = Phonetic_Keys
RecordID, PhoneticSurname, PhoneticForename, PhoneticStreet, PostCode, Premise
This table will be populated from incoming data which could contain the following columns:
TABLE NAME = New_Data
ID, Forrename, Surname, Address1, Address2, Address3, Town, PostCode
If my incoming file is like above then I need to build an insert statment to populate the Phonetic_Keys table.
However, the incoming file may not have a forename or a address3 so I've used a series of IF ststements to build my SELECT statment to be used to insert into the phonetic keys table.
Please see code above. Is this a good way to do it?
I seem to have many IF statmements to check for columns before I build my dynamic insert statement.
Any ideas?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 7, 2013 at 3:52 am
So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....
I think you should read upon Coalesce.
If you still cant get it to work then get back to me here.....we'll work something out. 🙂
February 7, 2013 at 5:09 am
Abu Dina (2/6/2013)
I seem to have many IF statmements to check for columns before I build my dynamic insert statement.Any ideas?
'If' is a conditional statement ;so, when you need it , you use it ..
If that fieldMapping table has to be searched for creating dynamic sql then you can go on with it ;
another way could be , since you are not executing the sql's , and I suppose using the same variable elsewhere and deciding which to execute , you can put the conditions on executing the dynamic sql rather than setting the value to variables.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 7, 2013 at 5:42 am
vinu512 (2/7/2013)
So if the incoming data doesn't have a forename then what do you insert into the table??..Do you Insert NULL??....
Yes that's right, where I define the variables I have them default to = 'NULL'
so if this IF the statment is not valid, i.e. no forename then the SELECT variable for the forename will be NULL.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 7, 2013 at 5:44 am
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 7, 2013 at 8:46 pm
Abu Dina (2/7/2013)
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.
If you could post the code , may be we can work it out ...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 7, 2013 at 9:12 pm
demonfox (2/7/2013)
Abu Dina (2/7/2013)
Fair enough. I think I'm trying to be too clever when I should be concentrating on getting the rest of the project done first! This part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.If you could post the code , may be we can work it out ...
+1
Please post the Insert statements you are working with currently.
February 8, 2013 at 2:56 am
As requested here is the complete sproc:
CREATE PROCEDURE [dbo].[usp_GenerateKeys]
@SourceTable VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL NVARCHAR(MAX)
DECLARE @MSG NVARCHAR(500)
-- Get source table unique reference
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'FieldMappings')
DROP TABLE dbo.FieldMappings
select Label, FieldOrder into dbo.FieldMappings
from [Snapshot_MeritDirect].dbo.BFieldTypes
WHERE Label in (select column_name from Snapshot_MeritDirect_Load.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @SourceTable)
-- We need a table to hold all the fields and their definsitions, then based on what columns
-- the staging file has, we build the keys table
DECLARE @SourceTableUniqueReference VARCHAR(50)
-- Used to generate the insert into the keys table
DECLARE @InsertIntoPart VARCHAR(4000) = ''
DECLARE @SelectPart VARCHAR(4000) = ''
DECLARE @FromPart VARCHAR(4000) = ''
DECLARE @CROSSAPPLY VARCHAR(200) = ''
-- Person name details
DECLARE @mkNameKeySELECTString VARCHAR(800) = 'NULL'
DECLARE @mkName1SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkName2SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkName3SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkNormalisedName VARCHAR(800) = 'NULL'
-- Company specific keys
DECLARE @mkOrgNameKeyString VARCHAR(800) = 'NULL'
DECLARE @mkOrgNameSELECTString VARCHAR(800) = 'NULL'
DECLARE @mkOrgName1SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkOrgName2SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkOrgName3SELECTString VARCHAR(800) = 'NULL'
DECLARE @mkNormalisedOrganisation VARCHAR(800) = 'NULL'
-- Address specific details
DECLARE @mkPostInSELECTString NVARCHAR(800) = 'NULL'
DECLARE @mkPostOutSELECTString NVARCHAR(800) = 'NULL'
DECLARE @mkPhoneticStreetSELECTString NVARCHAR(800) = 'NULL'
DECLARE @mkPremiseSELECTString NVARCHAR(800) = 'NULL'
DECLARE @mkTownSELECTString NVARCHAR(800) = 'NULL'
SET @SourceTableUniqueReference = 'ID, GUID '
---- If we already have First and Last names then there is no need to split the names
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname) + LEFT(dbo.GetFirstWord(Forename), 1))'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(Surname)'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'dbo.GetLastWord(Surname) + '','' + dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
END
else IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
AND NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'NULL'
-- Phonetic Surname
SET @mkName1SELECTString = 'NULL'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(Forename))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(Forename))'
-- Normalised name = Forename + Middle Name
SET @mkNormalisedName = 'dbo.GetFirstWord(Forename) + '','' + dbo.GetSecondWord(Forename)'
END
else IF NOT EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Forename')
AND EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Surname')
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(Surname))'
-- Phonetic Forename
SET @mkName2SELECTString = 'NULL'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'NULL'
-- Normalised name = Surname
SET @mkNormalisedName = 'dbo.GetLastWord(Surname) '
END
ELSE IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'FullContactName')
BEGIN
-- Phonetic Surname + First letter of Forename
SET @mkNameKeySELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName) + LEFT(dbo.GetFirstWord(FullContactName), 1))'
-- Phonetic Surname
SET @mkName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetLastWord(FullContactName))'
-- Phonetic Forename
SET @mkName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetFirstWord(FullContactName))'
-- Phonetic Middle name or Initial
SET @mkName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.GetSecondWord(FullContactName))'
-- Normalised name = Surname + Forename + Middle Name
SET @mkNormalisedName = 'dbo.GetLastWord(FullContactName) + '','' + dbo.GetFirstWord(FullContactName) + '','' + dbo.GetSecondWord(FullContactName)'
END
ELSE
BEGIN
SET @MSG = N'No Forename, Surname or Full Contact Details present in the table.'
RAISERROR(@MSG,16,1);
RETURN
END
-- If we have compnay details then we need to break this down into 4 different keys
-- mkOrganisation
-- mkOrgName1
-- mkOrgName2
-- mkOrgName3
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'OrganisationName')
BEGIN
SET @mkOrgNameKeyString = 'dbo.clrFn_DoubleMetaphone(ISNULL(dbo.fn_NormaliseOrgName(OrganisationName, 0), ''''))'
SET @mkNormalisedOrganisation = 'dbo.fn_NormaliseOrgName(OrganisationName, 0)'
SET @mkOrgName1SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 1))'
SET @mkOrgName2SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 2))'
SET @mkOrgName3SELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fn_NormaliseOrgName(OrganisationName, 3))'
END
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Postcode')
BEGIN
SET @mkPostInSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 1)'
SET @mkPostOutSELECTString = 'dbo.fn_PostCodeSplitter(Postcode, 2)'
END
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Address1')
BEGIN
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Buildingname')
BEGIN
SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Buildingname + '' '' + Address1))'
SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(isnull(Buildingname, '''') + '' '' + Address1) AS a '
SET @mkPremiseSELECTString = 'a.number'
END
ELSE
BEGIN
SET @mkPhoneticStreetSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Address1))'
SET @CROSSAPPLY = ' CROSS APPLY dbo.GetNumbersFromText(Address1) AS a '
SET @mkPremiseSELECTString = 'a.number'
END
END
IF EXISTS (SELECT 1 FROM dbo.FieldMappings WHERE Label = 'Town')
BEGIN
SET @mkTownSELECTString = 'dbo.clrFn_DoubleMetaphone(dbo.fnStripString(Town))'
END
SET @InsertIntoPart = 'INSERT INTO dbo.' + @SourceTable + '_keys_ (ID, GUID, mkNameKey, mkName1, mkName2, mkName3, mkNormalizedName, mkOrganizationKey,
mkNormalizedOrganization, mkOrgName1, mkOrgName2, mkorgName3, mkPostIn, mkPostOut, mkPhoneticStreet, mkPremise, mkPhoneticTown)'
---- some more processing on the keys before we insert into the keys table
set @mkNameKeySELECTString = 'CASE WHEN LEN(replace(' + @mkNameKeySELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkNameKeySELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkNameKeySELECTString + ', '' '', '''') END'
set @mkName1SELECTString = 'CASE WHEN LEN(replace(' + @mkName1SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName1SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName1SELECTString + ', '' '', '''') END'
set @mkName2SELECTString = 'CASE WHEN LEN(replace(' + @mkName2SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName2SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName2SELECTString + ', '' '', '''') END'
set @mkName3SELECTString = 'CASE WHEN LEN(replace(' + @mkName3SELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkName3SELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkName3SELECTString + ', '' '', '''') END'
set @mkPhoneticStreetSELECTString = 'CASE WHEN LEN(replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkPhoneticStreetSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkPhoneticStreetSELECTString + ', '' '', '''') END'
set @mkTownSELECTString = 'CASE WHEN LEN(replace(' + @mkTownSELECTString + ', '' '', '''')) >=8 THEN LEFT(replace(' + @mkTownSELECTString + ', '' '', ''''), 8) ELSE replace(' + @mkTownSELECTString + ', '' '', '''') END'
SET @SelectPart = 'SELECT ' + @SourceTableUniqueReference + ', ' + @mkNameKeySELECTString + ', ' +
@mkNameKeySELECTString + ', ' + @mkName2SELECTString + ', ' + @mkName3SELECTString + ',' + @mkNormalisedName + ','
+ @mkOrgNameKeyString + ', ' + @mkNormalisedOrganisation +
', ' + @mkOrgName1SELECTString + ', ' + @mkOrgName2SELECTString + ', ' + @mkOrgName3SELECTString + ', ' + @mkPostInSELECTString + ', ' + @mkPostOutSELECTString +
', ' + @mkPhoneticStreetSELECTString + ', ' + @mkPremiseSELECTString + ', ' + @mkTownSELECTString
SET @FromPart = 'FROM Snapshot_MeritDirect_Load.dbo.' + @SourceTable
EXEC (@InsertIntoPart + ' ' + @SelectPart + ' ' + @FromPart + @CROSSAPPLY)
SET @SQL = ''
SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise = ''0'''
EXEC (@SQL)
SET @SQL = ''
SET @SQL = 'UPDATE ' + @SourceTable + '_keys_' + ' SET mkPremise = '''' WHERE mkPremise IS NULL'
EXEC (@SQL)
END;
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 27, 2015 at 1:28 pm
his part works but everytime I look at all the IFs I get confused so I thought there might be a better way to achive the same result.
Having before and after test data to work with (plus the English text to explain the transformations needed) also helps in these cases as I see you got no help after you posted the entire stored procedure. You may also have to explain the context, as in why you are using dynamic sql and not protecting against SQL injection in your code.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply