Use CatRegionMap to avoid hard-coding in Categories

There is one elephant in the room, when using categories, hardcoding

Remember this funny LLAttrData?

SELECT d.name Name,d.dataid, d.versionnum, a.valint 'Green?', b.valstr 'Language'
FROM DTree d, LLAttrData a, LLAttrData b
WHERE (d.dataid = a.id AND d.versionnum = a.vernum
AND a.defid = 11523 AND a.attrid = 2)
AND (d.dataid = b.id AND d.versionnum = b.vernum
AND b.defid = 11523 AND b.attrid = 3)
ORDER BY d.Dataid        

There is a lot of defids and attrids, you have to hardcode.

Catregionmap to the rescue

The CatRegionMap table maps Categories to Attributes, using their names. The example below will give you a good idea of the data stored in this table.

The RegionName column stores a string such as Attr_11523_4, where 11523 is the Category’s DataID and 4 is the AttrID. If you want to write queries where you specify Attribute and Category names instead of learning their ID numbers, you can use this table.

There are some advantages to using the CatRegionMap table to avoid hard-coding. For example, if you want to create a LiveReport that allows a user to query by entering a Category or Attribute, they are going to want to enter the names, not an arbitrary Category number. be careful, parsing strings is almost always more costly than just using numbers.

• In MS SQL, you can determine the location of a string within a string using the CHARINDEX function. The basic spec is: charindex( searchfor, instring, startingat)
• For example, charindex('_', regionname, 6 ) will return the number 10 for the string Attr_11512_4. You can use this in a SUBSTRING function to return the portion of the string from the second "_" to the end of the string
• In Oracle, you can determine the location of a string within a string using the INSTR function. The basic spec is: instr(instring, searchfor, startingat[, occurrence]) For example, instr(regionname,'_',1,2) will return the number 10 for the string Attr_11512_4. You can use this in a SUBSTR function to return the portion of the string from the second "_" to the end of the string

For example

SELECT *
FROM catregionmap
WHERE catname = 'ProductSpec'        

gives the catregionmap for "ProductSpec"

and here (for "Proposal Element" category) you can find the Attr_11523_3 p.ex by selectinb the regionname and doing the substring

this is MS-SQL

SELECT distinct d.Name
FROM DTree d, LLAttrData a
WHERE d.DataID = a.ID
AND a.DefID = (select distinct catid
FROM CatRegionMap
WHERE CatName = 'Proposal Element')
AND a.AttrID = (select SUBSTRING
(regionname,charindex('_', regionname, 6) + 1,
len(regionname))
FROM CatRegionMap
WHERE CatName = 'Proposal Element'
AND AttrName = 'Element type')
AND a.ValStr = 'Appendix'
        

and this is Oracle

SELECT SUBSTRING( regionname, CHARINDEX('_',regionname, 6 )+1, LEN(regionname) )
to: INSTR( regionname,'_',1,2 ) +1,LENGTH(regionname)        

and in PostgresSql

SELECT 
  SUBSTRING (regionname, SUBSTRING('_',regionname), LENGHT(regionname)-SUBSTRING('_',regionname from 2);        

This is a nice oppotunity to use multiple SELECTS