Product(s): dspMigrate (Map)
Version(s): 6.0.x - 7.0.x (see note at the end of the article for 6.0.x differences)
Question
There is a field in the Value Mapping pages of Map called Description Update SQL. What does it do and how do I use it?
Answer
Description Update SQL is a very powerful setting that should be used carefully. When populated, it acts as an override to the default SQL query used to update the text descriptions of mapping values. The Description Update SQL setting actually appears twice in Map, because you can override the default SQL query for updating Target Value descriptions OR Legacy Value descriptions. You should only use this setting if you are unhappy with the results of the default SQL queries that update the text descriptions of mapping values.
Target Values
Map -> Configuration -> Value Mapping (Config) -> {vertical}**
Description Update SQL will override the following default SQL query used by cMap when clicking Refresh on the Map -> Configuration -> Value Mapping (Config) page. The default SQL query is a result of the "Configuration Values" on the vertical, which are actually set on the Design -> Configuration -> Lookup Table Setup page**. The #variables# come from columns in the cMap.dbo.webAEValueMappingCheckTableDescriptionUpdSel view.
default SQL query:
UPDATE [cMap].dbo.ttValueMappingCheckTableValue
SET dbo.ttValueMappingCheckTableValue.Description = SUBSTRING(
(SELECT cast(MAX([#TargetDatabase#].dbo.[#ViewName#].[#DescriptionField#]) AS NVARCHAR(4000))
FROM [#TargetDatabase#].dbo.[#ViewName#]
#WhereClauseDescriptionUpd#), 1, 4000)
WHERE WaveValueMappingCheckTableID = '#WaveValueMappingCheckTableID#'
The Description Update SQL will be a complete UPDATE statement since it must override the automated UPDATE statement that won't be run. Your override value cannot contain any #variables#, but rather it must specify exact object names.
Legacy Values
Map -> Configuration -> Value Mapping (Config) -> Sources -> Tables -> {vertical}**
Description Update SQL will override the following default SQL query used by cMap when clicking Refresh on the Map -> Configuration -> Value Mapping (Config) -> Sources -> Tables page. The default SQL query is a result of the settings on the vertical of the same page. The #variables# come from columns in the cMap.dbo.webAEValueMappingCheckTableLegacyDescriptionUpdSel view.
default SQL query:
UPDATE [cMap].[dbo].[ttValueMappingSourceValue]
SET [LegacyDescription] = SUBSTRING(b.#DescriptionField#, 1, 4000)
FROM [cMap].[dbo].[ttValueMappingSourceValue] a, [#SourceDatabase#].[dbo].[#DescriptionTable#] b
WHERE a.[ValueMappingSourceID] = '#ValueMappingSourceID#'
AND RTRIM(LTRIM([LegacyValue])) = #VALUEDESCRIPTIONJOIN# #LegacyDescriptionLanguage#
AND ((a.[LegacyDescription] IS NULL AND b.[#DescriptionField#] IS NOT NULL) OR (a.[LegacyDescription] <> b.[#DescriptionField#]))
The Description Update SQL will be a complete UPDATE statement since it must override the automated UPDATE statement that won't be run. Your override value cannot contain any #variables#, but rather it must specify exact object names.
**In the DSP 6.0.x framework, the Value Mapping (Config) page is called Cross Reference Check Tables instead. Also, target lookup tables are actually configured on the vertical of this page (since Design is not an app in 6.0.x).