Title

sql-do script -- Modified sql code for execution by sql do

Introduction

There are four things distinguishing sql-do script from ordinary sql:

o- It can only include a series of table-creation statements - no declare's or similar (yet).

o- It can include data from local (non-DB, usually stata) files.

o- It can include 'stored script' from local files.

o- It can include 'swappable tags' that can have values supplied at runtime.

It also requires a 'wrapper', which delimits the sql_do script from stata script in the same file.

Example

*---sql:cheese, this script lists some world cheeses, and the animals that provide the milk.

^t=british, cheeses in this table are made in Britain
select name, odor, runnyness, ^m:jfield
from ^t:worldcheese
where nationality='britain'

^t=animals
select AA.*, BB.commonname
from ^t:british AA
join milkgiving.animals BB
on AA.^m:jfield=BB.animalid

*---/sql

sql do thisfile.do, destination(*)
tab name order
list name odor runnyness

¤--- Wrapper

The target file of a sql do command can have many sections of sql-do script mixed with other things, like so:

[other things]

*---sql:[wrapper-id][ ,description]
part 1
.
.
part n
*---/sql

[other things]

Any number of wrapper-ids can be used, any number of times, in the same file. A single sql do command will execute all, and only, the script with a matching wrapper-id.
When sql do does not specify a wrapper-id, script with NO wrapper-id is executed.

description is only read from the first relevant wrapper in the file. It will be saved in the extended meta-data as the query description.

¤¤-- part

Each part (above) is:

^t=table-name [, description]
part definition
blank line

where blank line is a blank line (no text).

¤¤¤- part definition

part definition can be one of:

1) sql statement

2) ^use= filename [options]

3) ^codex= filename [options]

4) ^ssc= filename [options]

¤¤¤¤ sql statement

sql statement is generally any well-formed sql statement that creates a table (eg, select, union, pivot). It can include swappable tags.

sql statement can be written across multiple lines, as long as no blank lines are included -- a blank line would be interpreted as the end of the part.

¤¤¤¤ ^use=

^use= filename [if] [in] [, keep(varelist)]

filename is any local file that can be read as data by usel, or * to use the data in memory.

keep() and [if] [in] function as in usel.

¤¤¤¤ ^codex=

^codex= filename [, filter-option tag(name)]

filename must refer to a codex file.

filter-option is one of those described in codex review.

tag(name) specifies the swappable tag to use for id values.

This part definition uploads data from a codex file. It creates a table with the columns: type, grp, id, and either (code) or (code1 and code2), depending on the nature of the codes.
It also specifies a swap for one swappable tag; by default, the tag is ^g:cxids,
but you can specify a different name (with or without the ^g:) in the tag() option.
The specified tag will be swapped out for the (comma separated) list of ids retrieved from the codex.

[+] The swap is useful for sql pivot statements: If a codex defined 5 conditions, based on 50 icd9 codes, then after uploading the table of codes with ^codex=,
and selcting patients and conditions with a join on code, one could use a pivot statement with ^g:cxids to return a table with one row per patient, and one column for each of the 5 conditions
(whether or not any of the conditions were actually found).

¤¤¤¤ ^ssc=

^ssc=filename [, dir(directory) swap(tag(value) [...]) ]

filename refers to a stored script file; that is, a local file containing sql-do script. It will use the default file-extension .sql.

dir(directory) specifies a (super) directory for filename. This is a convenience option: The filenames can be kept more readable, and also directory can contain (or be) a stata macro
- the only place in sql-do script where a macro will function.

swap() specfies any swaps to implement in the sql statements of stored script: Each tag will be replaced with the corresponding value.
Note that value can specify another swappable tag.

When a part definition is an ^ssc=, the original part (say, p1) is replaced by all the parts in the stored script (say, pA-pZ),
and the final replacement part (pZ) gets the original (p1) ^t=table-name.

Swappable Tags

swappable tags are a sort of macro. They are marked bits of text which can be swapped out for other bits of text at runtime. The syntax is:

^t:name

or

^g:name

where name is a valid stata name.

^t: tags will ultimately be interpreted as sql table names, while generic ^g: tags will not. Replacement text can be specified in the swap() option of either a ^ssc= part definition, or the sql do command itself.

If no swap is specified for a particular tag, the prefix is stripped and, for ^g: tags, name is used in the final sql. For ^t: tags, name is first interpreted as a table:
if it matches an earlier ^t=name (one that hasn't been excluded by only()), that table will be referenced; otherwise, name is given the current schema accoring to the sql path.

Although you can specify tables withuot a ^t: tag, it generally makes sense to use the tags when referring to your own tables.
For example, if you create tableA early in your script and refer to it at a later point, you can have the unmodified script execute
using some alternate-tableA by setting the only() and/or not() options on the command line. Handy for debugging.

Also, note that uploads (ie, ^use= and ^codex=) are created on the server with temporary names, and using ^t:name will reference the correct temporary table.

All tags and their ultimate renditions (and the tables used & excluded by the script) are tracked by sql do, and summarized in its preview.