Retrieve Map Group Values
In order to retrieve the rate, a SQL needs to be written that links all three tables. The same rules and standards for SQL apply for the AsMapSeries lookup.
For example, to lookup the GMIB rate, assume that the following variables have been set:
• PlanGUID: E99F8927-B97E-40F4-B6A3-D53C6BA00F82
• SegmentRateActiveDate: 3/5/2006
• IssueState: 32
SELECT AsMapValue.IntValue FROM AsMapValue
JOIN AsMapGroup ON AsMapGroup.MapGroupGUID = AsMapValue.MapGroupGUID
AND AsMapGroup.MapGroupDescription = 'GMIBMaximumAge'
JOIN AsMapCriteria ON AsMapCriteria.MapValueGUID = AsMapValue.MapValueGUID
AND AsMapCriteria.MapCriteriaName = 'PlanGUID'
AND AsMapCriteria.TextValue = '[Policy:PlanGUID]'
JOIN AsMapCriteria AS R ON R.MapValueGUID = AsMapValue.MapValueGUID
AND R.MapCriteriaName = 'StateCode' AND ((R.TextValue = '[IssueState]') OR (R.TextValue = '*'))
ORDER BY R.TextValue ASC,S.TextValue ASC FETCH FIRST ROW ONLY
Without the FETCH FIRST ROW ONLY, the Query would return both rates associated with the GMIBMaximumAge MapGroup. Due to the ordering of R.TextValue, NY will appear prior to *, and therefore the FETCH FIRST ROW ONLY will correctly return an integer value of 65.
The above SQL shows:
• Hardcoded values (AsMapGroup.MapGroupDescription = 'GMIBMaximumAge'),
• Policy fields (AsMapCriteria.TextValue = '[Policy:PlanGUID]')
• A math variable (R.MapCriteriaName = 'StateCode' AND ((R.TextValue = '[IssueState]') OR (R.TextValue = '*')))
• The use of * (as seen in the StateCode criteria).
Free Look SQL Example
SELECT TOP 1 AsMapValue.IntValue FROM AsMapValue
JOIN AsMapGroup ON AsMapGroup.MapGroupGUID = AsMapValue.MapGroupGUID
AND AsMapGroup.MapGroupDescription = 'FreeLookDays'
JOIN AsMapCriteria ON AsMapCriteria.MapValueGUID = AsMapValue.MapValueGUID
AND AsMapCriteria.MapCriteriaName = 'PlanGUID'
AND (AsMapCriteria.TextValue = '[Policy:PlanGUID]'
OR AsMapCriteria.TextValue = '*')
JOIN AsMapCriteria AS Q ON Q.MapValueGUID = AsMapValue.MapValueGUID AND Q.MapCriteriaName = 'ReplacementType'
AND (Q.TextValue='[ReplacementCriteria]')
JOIN AsMapCriteria AS R ON R.MapValueGUID = AsMapValue.MapValueGUID AND R.MapCriteriaName ='StateCode' AND (R.TextValue = '[Policy:IssueStateCode]' OR R.TextValue='*') ORDER BY R.TextValue DESC
Copyright © 2009, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices