Sometimes we just have to deal with tables we didn't design (and would have designed differently had we had the opportunity). Sometimes we just need a tiny little tweak to make that table perfect (reg., U.S. Patent Pending). Sometimes we want to let everyone know that this table row is unusual either for what it has or for what it hasn't. Enter the extension variable.
Every table row in a given table has room for every KEY and NAME field originally specified when the table was TBCREATEd. Flexible little devils that they are, table rows also have unlimited* space for other stuff, and every table row can be unique as to what other stuff it holds. Observe:
"TBCREATE BILOMAT KEYS(BMITEM) NAMES(BMDESCR BMONHAND BMECOQTY)"
our Bill-of-Materials table. It is keyed by the item number (BMITEM), and has fields for a description, the number on hand in inventory, and the economic order quantity. Wha...? Where are the 'materials' that make up the 'bill'? They're in extension variables, naturally.
"TBVCLEAR BILOMAT" bmitem = 'ER4590T' bmdescr = 'Whatsis, large, left-handed, tan' bmonhand = qty.whatsis_l_lh_tan bmecoqty = ecocalc.whatsis_l_lh xvars = 'DD114 DF33 DF34 DF36 DF38 EM2030' dd114 = 6 df33 = 1 df34 = 1 df36 = 1 df38 = 2 em2030 = 4 "TBADD BILOMAT SAVE("xvars")"
The row for 'ER4590T' in table BILOMAT has all four canonical fields filled, and there are six additional extension variables, one each for the six components of every ER4590T. When the supplier for the EM2030 component advises you there will be an unavoidable 40% increase in the price of that component, you're going to want to know which of your products (BMITEMs) is going to be affected and by how much. The catalog entries for each of those products will have to be adjusted to correctly show the new price of everything that relies on the EM2030, right? Of course!
"TBVCLEAR BILOMAT" em2030 = 0 "TBSCAN BILOMAT ARGLIST(EM2030) CONDLIST(GT)" "TBDISPL BILOMAT PANEL(SRCHPART) AUTOSEL(NO)"
TBVCLEAR zaps all the 'regular' names mentioned in the definition of table BILOMAT, variable 'EM2030' is set to zero, TBSCAN specifies that we seek all rows where an extension variable 'EM2030' has a value greater than zero. Panel SRCHPART should, if coded correctly, display a scrollable list of all rows (all BMITEM keys) associated with subassembly EM2030.
Zounds! This is almost DB2! Except that it didn't take us eight months and require the approval of two vice-presidents. And if they decide later that they really do want to implement it in DB2, you already have the proof-of-concept ready to demonstrate.
(*) — You didn't think when I said 'unlimited' I actually meant 'without limit', did you? Tsk. Of course there are limits. It's just that you're unlikely to hit them before your table is so large your system chokes trying to open it. I've seen that happen with complex tables as small as 6,000 rows, but I have also seen 50,000-row tables that worked just fine, and I suspect they could have grown much larger without causing a problem.