- Identify values lists that have data items that are nested over 50 items deep
The below query will find Values lists that are over 50 items deep:
WITH selectValues AS ( SELECT 0 generation, * FROM dbo.tblIVSelectValue sv WHERE sv.parent_select_value_id IS NULL UNION ALL SELECT r.generation + 1, sv.* FROM dbo.tblIVSelectValue sv JOIN selectValues r ON sv.parent_select_value_id = r.select_value_id ) SELECT MAX(sv.generation) as NestedCount, sv.select_id, sdt.select_def_type_name, sdt2.select_name FROM selectValues sv with(nolock) JOIN dbo.tblIVSelectDef sd with(nolock) ON sd.select_id = sv.select_id JOIN dbo.tblIVSelectDefType sdt with(nolock) ON sdt.select_def_type_id = sd.select_def_type_id JOIN dbo.tblIVSelectDefTranslation sdt2 with(nolock) ON sdt2.select_id = sd.select_id AND sdt2.is_default = 1 LEFT JOIN tblValueListValueTranslation VLVT with(nolock) on VLVt.select_value_id = sv.select_value_id GROUP BY sv.select_id, VLVT.select_value_name, sdt.select_def_type_name, sdt2.select_name HAVING MAX(sv.generation) > 50 ORDER BY 1 DESC option (maxrecursion 32000);
- Restructure the values list(s) to not be structured too deeply.
- Identify calculations that contain formulas that are nested over 40 functions deep
The below query will find calculations that are nested over 40 functions deep:
select ft.field_name, mt.module_name from tblCAlc c LEFT JOIN tblFieldTranslation ft on c.field_id = ft.field_id LEFT JOIN tblIVFieldDef fd on fd.field_id = c.field_id LEFT JOIN tblLevel l on l.level_id = fd.level_id LEFT JOIN tblModuleTranslation mt on mt.module_id = l.module_id where formula like '%))))))))))))))))))))))))))))))))))))))))))%'
- Split the calculation into multiple separate calculated fields so the formula does not contain items that are nested too deeply.
|