May 11, 2016 at 10:35 am
Right now I am doing it this way... and there must be a better way.
select cContactID
,statuscode.value cContactStatus
,statecode.value cContactState
,donotpursue.Value DoNotPursue
,DoNotBulkEMail.Value DoNotBulkEMail
,DoNotBulkPostalMail.value DoNotBulkPostalMail
,DoNotEMail.value DoNotEMail
,DoNotFax.value DoNotEMail
,DoNotPhone.value DoNotPhone
,DoNotPostalMail.value DoNotPostalMail
,DoNotSendMM.value DoNotSendMM
from contact c
join #contact_strings StatusCode on statuscode.attributename = 'statuscode' and c.statuscode = statuscode.attributevalue and objectTypeCode = 2
join #contact_strings statecode on statecode.attributename = 'statecode' and c.statecode = statecode.attributevalue and objectTypeCode = 2
join #contact_strings donotpursue on donotpursue.attributename = 'datatel_donotpursue' and datatel_donotpursue = donotpursue.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotBulkEMail on DoNotBulkEMail.attributename = 'DoNotBulkEMail' and datatel_donotpursue = DoNotBulkEMail.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotBulkPostalMail on DoNotBulkPostalMail.attributename = 'DoNotBulkPostalMail' and datatel_donotpursue = DoNotBulkPostalMail.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotEMail on DoNotEMail.attributename = 'DoNotEMail' and datatel_donotpursue = DoNotEMail.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotFax on DoNotFax.attributename = 'DoNotFax' and datatel_donotpursue = DoNotFax.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotPhone on DoNotPhone.attributename = 'DoNotPhone' and datatel_donotpursue = DoNotPhone.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotPostalMail on DoNotPostalMail.attributename = 'DoNotPostalMail' and datatel_donotpursue = DoNotPostalMail.AttributeValue and objectTypeCode = 2
join #contact_strings DoNotSendMM on DoNotSendMM.attributename = 'DoNotSendMM' and datatel_donotpursue = DoNotSendMM.AttributeValue and objectTypeCode = 2
Basically each column in the contact table contains a numeric value. So I have to join that numeric value on the #contract_strings table to convert the number to actual text. If it was one or two columns, it wouldn't be so bad, but as you can see it is quite a few columns and it is getting kind of drawn out. Any other ideas?
May 11, 2016 at 11:25 am
It would help if you provided sample data and expected output.
My first question, is whether you really need to reference a table for your various DoNotX attributes. Do they encompass anything other than Yes/No (or the equivalent)? And what kind of transformation do you expect to make to those values that isn't already represented by the current values?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 11, 2016 at 11:40 am
Well one option is to just store the values directly on your contact table instead of trying to normalize them.
Another would be to just return the values on the contact table and then return the status values separately and let the application figure it out.
May 11, 2016 at 11:59 am
Right now the data looks like...
ContactIDDoNotPhoneDoNotFaxDoNotEMailDoNotPostalMailDoNotBulkEMailDoNotBulkPostalMailAccountRoleCodeTerritoryCodeIsPrivate
ABC0000003510
DEF0000001210
AQW0000003220410
FEW000000110
ERW000000410
It should look like this... (I'm guessing it was done this way for internationalization/language transcoding now that I think about it)
ContactIDDoNotPhoneDoNotFaxDoNotEMailDoNotPostalMailDoNotBulkEMailDoNotBulkPostalMailAccountRoleCodeTerritoryCodeIsPrivate
ABCNoNoNoSkipSkipSkipBahamasPrimaryNo
DEFNoNoNoSkipSkipSkipMiamiPrimaryNo
AQWNoNoNoSkipSkipSkipAlbertaPrimaryNo
FEWNoNoNoSkipSkipSkipKendallPrimaryNo
ERWNoNoNoSkipSkipSkipTampaPrimaryNo
May 12, 2016 at 4:55 pm
Another alternative that was presented to me would be to run the query as originally gathered with all the numeric values and then run an update sql addendum to convert the numeric values into text based on the language of choice. It's two queries as opposed to one... but I imagine the two queries would run faster because the first query would not be laden down with so much complex logic.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply