Lonely D3 Shell Variables

A question came up in comp.databases.pick that asked how we could use a pre-defined set of values in a D3 query. For example: SORT CUSTOMERS WITH STATE “AZ” “CA” “NM” “OR” … but getting the state codes dynamically. Sure, we can do that, using shell variables.

In SQL queries it’s quite common to nest selects like this:
Get the unknown code for the MyCo manufacturer, then find all products from that company and show us the Model info.

SELECT Model FROM Product
WHERE ManufacturerID IN
(SELECT ManufacturerID FROM Manufacturer
WHERE Manufacturer = ‘MyCo’)

or: Find countries that have a population greater than France.

SELECT Name FROM Countries
WHERE Population >
(SELECT Population FROM Countries
WHERE name=’France’)

The Primary query is called an Outer Query and the nested one is an Inner Query. These are not necessarily examples of an Inner Join which link files using common values. In D3 there isn’t a direct way to do this but with a tiny program we can. Other MV environments have an EVAL function that allows fields to be calculated at runtime in a query. EVAL can make use of I-Types which can then call to BASIC programs. D3 has shell variables which can be used just about anywhere but I honestly don’t know if anyone uses them. Check the doc for “@ Shell Variable” to read-up on this great feature.

The above example with CUSTOMERS is pretty easy. Use the following command to set a list of states:

SET STATES=”AZ” “CA” “NM” “OR”

Now use the new shell variable @STATES in a query:

SORT CUSTOMERS WITH STATE @STATES

At runtime the @STATES value is substituted with the value of the STATES environment variable. But what if we don’t want to hardcode the list? What if the list is stored in a save-list like this:

001 AZ
002 CA
003 NM
004 OR

We can’t just get-list because the command processor will use those values as item ID’s. The values need to be inserted into the command string itself, and in many cases surround the values with quotes. For this we can use a simple program which I will call DYNAMIC1.

LISTID = FIELD(TRIM(SENTENCE()),” “,2)
ENV = FIELD(TRIM(SENTENCE()),” “,3)
IF LISTID=”” OR ENV = “” THEN
CRT “Command is : verb savelist.id env.var.id”
STOP
END
EXECUTE “GET-LIST “:LISTID CAPTURING OUT RETURNING ERR
IF INDEX(ERR,”202”,1) THEN
CRT “List doesn’t exist”
STOP
END
DONE = 0
THE.LIST = “”
LOOP
READNEXT ID ELSE DONE=1
UNTIL DONE DO
THE.LIST := \ “\:ID:\”\
REPEAT
SET = \SET \:ENV:\=\:THE.LIST
CRT SET
EXECUTE SET; * CAPTURING OUT
CRT “command @”:ENV:” created”

Create the above save-list with four attributes and call it MY.STATES. Then execute this command.

DYNAMIC1 MY.STATES STATES

Using the PENV command to Print the Environment variables, you should see the new variable. To use it try the query above. The state codes with quotes are inserted into the query and then the command line is processed.

But what if we want to avoid creating a list at all? Wouldn’t it be neat if we could use an inner query like SQL? Sure, we can do that too.

Here is another program called DYNAMIC2 that allows us to enter an entire query with multiple statements, and insert it into the middle of a command line:

CMD = OCONV(SENTENCE(),’G1 999′)
CONVERT \~\ TO @AM IN CMD
FOR C = 1 TO DCOUNT(CMD,@AM)
EXECUTE CMD<C> CAPTURING OUT RETURNING ERR
IF INDEX(ERR,”202″,1) THEN STOP
NEXT C
DONE = 0
THE.LIST = “”
LOOP
READNEXT ID ELSE DONE=1
UNTIL DONE DO
THE.LIST := \ “\:ID:\”\
REPEAT
CRT THE.LIST
END

And here is an example using the database that comes with mv.NET:

SORT ORGANIZATION
@`DYNAMIC2 SELECT PRODUCT WITH
 DESCRIPTION RED]~
QSELECT PRODUCT (4`
BY NAME WITH NAME = CARPET] NAME

Let’s break it down. The intent is to find companies that provide red carpets. We start by selecting red products, then we get a list of the companies that provide them, then we list the organization file, but only those that have the word Carpet in their name.

Shell variables usually do a simple substitution of their value for wherever @name is found. The @`command` syntax (using back-quote, not the normal forward quote) allows a command to be executed and the output to be captured for replacement of @name. Here, I just call to my Dynamic2 program and pass in a command line that consists of a series of TCL commands. I delimit the commands with a tilde ( ~ ) but anything unique could have been used. So in the above command we do a select on Product, get all the values from atb4, use those in Dynamic2 to wrap the values in quotes, then output the quoted values – thus replacing the entire @`command` with the new quoted values. Once that’s done and the ID’s to the Organization file are provided, we further whittle down the selection using the name. Sure, we typically use item ID’s from a select list but we could easily have generated some company names and then quoted those for use with the Name or some other definition.

There are a couple things interesting about this query.

First, we usually wouldn’t be able to look at organizations and find out which products they provide. We would need to select products to get organization IDs and then in a separate statement either list organization names through a translate or execute a second statement on the Organization file. If we list through Products then we’ll need a Translate for every field we need from Organization, and that can be cumbersome. Really we are executing multiple statements, but we’re doing it all in one command line, which many people should find more elegant.

Second, we’re not creating an intermediate save-list – this statement is entirely self-contained and up-to-date.

Third, in this case each Product can have multiple vendor Organizations. There are often complications when selecting on multi-values, including duplicate data or empty values. After the Qselect above, you can just run the data through a program or some other process to clean up the final selection. The Dynamic2 program is just expecting the final result of all statements to be a select list of values that it can quote.

You can easily modify the Dynamic2 program into something else to manipulate the data in other ways. For example, what if you don’t know what color carpet you need? What if you just want to know which Organization supplies the carpet that is being requested in the current order? Try this:

SORT ORGANIZATION
@`DYNAMIC2 SELECT PRODUCT WITH
DESCRIPTION @ORDER.COLOR]~
QSELECT PRODUCT (4`
BY NAME WITH NAME = CARPET] NAME

The nested ORDER.COLOR environment variable has been set to run a program as follows:

SET ORDER.COLOR=@`GET.COLOR.FOR.CURRENT.ORDER`

The program simply returns a color name like “BLUE”, and the results of that are processed by Dynamic2. I don’t know what the limits are to this sort of recursion but even if we can just go a couple levels deep like this, it’s pretty neat.

For years I have been saying that we could do things like this but I’ve never sat down to provide examples. I hope I haven’t forgotten anything but I think this is enough information to give many people a whole new view on what’s possible at TCL. One thing that bothers me is that some people insist that D3 should have EVAL and other functions like this built-in. As you can see the tools are in D3 and we can do a lot with just a little extra coding. The Dynamic1 and Dynamic2 programs were numbered because anyone could easily start creating a whole numbered series of dynamic functionality to do neat things. We can’t always leave it up to the DBMS providers to write all functions for all possible purposes. In my opinion, I just want solid tools that allow us to easily construct our own utilities – and as you see, that’s exactly what we have here and it’s quite powerful. Other opinions will vary.

See a follow-up to this article here.