{smcl} {* 2jul2014}{...} {* @@ Written by Elliott Lowy, mostly on the US government's dime (17 US Code ยง 105).}{...} {vieweralsosee "sql" "sql"}{...} {vieweralsosee "sql do" "sql do"}{...} INCLUDE help also_vlowy {title:Title} {pstd}{bf:sql-do script} {hline 2} Modified sql code for execution by {help sql do} {title:Introduction} {pstd}There are four things distinguishing {hi:sql-do script} from ordinary sql: {phang}o-{space 2}It can {bf:only} include a series of table-creation statements {hline 1} no {cmd:declare}'s or similar (yet).{p_end} {phang}o-{space 2}It can include data from local (non-DB, usually stata) files.{p_end} {phang}o-{space 2}It can include 'stored script' from local files.{p_end} {phang}o-{space 2}It can include 'swappable tags' that can have values supplied at runtime.{p_end} {pstd}It also requires a 'wrapper', which delimits the {hi:sql_do} script from stata script in the same file. {title:Example} {p 5 21 2}{cmd:*---sql:}cheese{cmd:,} this script lists some world cheeses, and the animals that provide the milk. {col 6}{cmd:^t=}british{cmd:,} cheeses in this table are made in Britain {col 6}select name, odor, runnyness, {cmd:^m:}jfield {col 6}from {cmd:^t:}worldcheese {col 6}where nationality='britain' {col 6}{cmd:^t=}animals {col 6}select AA.*, BB.commonname {col 6}from {cmd:^t:british} AA {col 6}join milkgiving.animals BB {col 6}on AA.{cmd:^m:}jfield=BB.animalid {col 6}{cmd:*---/sql} {col 6}sql do {it:thisfile.do}, destination(*) {col 6}tab name order {col 6}list name odor runnyness {c 164}{hline 3} {ul:{bf:Wrapper}} {hline} {p 5 5 3}The target file of a {help sql do} command can have many sections of {hi:sql-do script} mixed with other things, like so: {col 10}[{it:other things}] {col 10}{cmd:*---sql:}[{it:wrapper-id}][ {cmd:,}{it:description}] {col 14}{it:part 1} {col 14}{space 3}. {col 14}{space 3}. {col 14}{it:part n} {col 10}{cmd:*---/sql} {col 10}[{it:other things}] {p 5 5 3}Any number of {it:wrapper-ids} can be used, any number of times, in the same file. A single {help sql do} command will execute all, and only, the script with a matching {it:wrapper-id}. When {help sql do} does not specify a {it:wrapper-id}, script with {bf:NO} {it:wrapper-id} is executed. {p 5 5 3}{it: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. {c 164}{c 164}{hline 2} {ul:{bf:part}} {hline} {p 5 5 3}Each {it:part} (above) is: {col 10}{cmd:^t=}{it:table-name} [{cmd:,} {it:description}] {col 10}{it:part definition} {col 10}{it:blank line} {p 5 5 3}where {it:blank line} is a blank line (no text).{p_end} {c 164}{c 164}{c 164}{hline 1} {ul:{bf:part definition}} {hline} {p 5 5 3}{it:part definition} can be one of: {p 9 13 3}1){space 2}{it:sql statement}{p_end} {p 9 13 3}2){space 2}{cmd:^use=} {it:filename} [{it:options}]{p_end} {p 9 13 3}3){space 2}{cmd:^codex=} {it:filename} [{it:options}]{p_end} {p 9 13 3}4){space 2}{cmd:^ssc=} {it:filename} [{it:options}]{p_end} {c 164}{c 164}{c 164}{c 164} {ul:{bf:sql statement}} {hline} {p 5 5 3}{it:sql statement} is generally any well-formed sql statement that creates a table (eg, {cmd:select}, {cmd:union}, {cmd:pivot}). It can include {help sql_do_script##swap:swappable tags}. {p 5 5 3}{it:sql statement} can be written across multiple lines, as long as no blank lines are included {hline 2} a blank line would be interpreted as the end of the {it:part}. {marker use}{c 164}{c 164}{c 164}{c 164} {ul:{bf:^use=}} {hline} {p 5 5 3}{cmd:^use=} {it:filename} {ifin} [{cmd:,} {opt k:eep(varelist)}]{p_end} {p 5 5 3}{it:filename} is any local file that can be read as data by {help usel}, or {cmd:*} to use the data in memory. {p 5 5 3}{opt keep()} and {ifin} function as in {help usel}. {marker codex}{c 164}{c 164}{c 164}{c 164} {ul:{bf:^codex=}} {hline} {p 5 5 3}{cmd:^codex=} {it:filename} [{cmd:,} {it:filter-option} {opt tag(name)}]{p_end} {p 5 5 3}{it:filename} must refer to a {help codex} file. {p 5 5 3}{it:filter-option} is one of those described in {help codex##review:codex review}. {p 5 5 3}{opt tag(name)} specifies the {help sql_do_script##swap:swappable tag} to use for {bf:id} values. {col 6}{hline 10} {p 5 5 3}This {it:part definition} uploads data from a {help codex} file. It creates a table with the columns: {cmd:type}, {cmd:grp}, {cmd:id}, and either ({cmd:code}) or ({cmd:code1} and {cmd:code2}), depending on the nature of the codes. It also specifies a swap for one {help sql_do_script##swap:swappable tag}; by default, the tag is {cmd:^g:cxids}, but you can specify a different {it:name} (with or without the {cmd:^g:}) in the {opt tag()} option. The specified tag will be swapped out for the (comma separated) list of {bf:ids} retrieved from the {help codex}. {p 5 9 3}{bf:[+]} The swap is useful for sql pivot statements: If a {help codex} defined 5 conditions, based on 50 icd9 codes, then after uploading the table of codes with {cmd:^codex=}, and selcting patients and conditions with a join on {cmd:code}, one could use a pivot statement with {cmd:^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). {c 164}{c 164}{c 164}{c 164} {ul:{bf:^ssc=}} {hline} {col 6}{cmd:^ssc=}{it:filename} [{cmd:,} {opt d:ir(directory)} {cmd:swap(}{it:tag}{cmd:(}{it:value}{cmd:)} [...]{cmd:)} ] {p 5 5 3}{it:filename} refers to a {hi:stored script} file; that is, a local file containing {hi:sql-do script}. It will use the default file-extension {cmd:.sql}. {p 5 5 3}{opt d:ir(directory)} specifies a (super) directory for {it:filename}. This is a convenience option: The {it:filenames} can be kept more readable, and also {it:directory} can contain (or be) a stata macro {hline 1} the only place in {hi:sql-do script} where a macro will function. {p 5 5 3}{opt s:wap()} specfies any swaps to implement in the {it:sql statements} of {hi:stored script}: Each {it:tag} will be replaced with the corresponding {it:value}. Note that {it:value} can specify another {help sql_do_script##swap:swappable tag}. {col 6}{hline 10} {p 5 5 3}When a {it:part definition} is an {cmd:^ssc=}, the original {it:part} (say, {hi:p1}) is replaced by {bf:all} the {it:parts} in the {hi:stored script} (say, {hi:pA}-{hi:pZ}), and the final replacement {it:part} ({hi:pZ}) gets the original ({hi:p1}) {cmd:^t=}{it:table-name}.{p_end} {marker swap}{title:Swappable Tags} {pstd}{hi: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: {pmore2}{cmd:^t:}{it:name}{p_end} {pmore}or{p_end} {pmore2}{cmd:^g:}{it:name} {pmore}where {it:name} is a valid stata name. {pstd}{cmd:^t:} tags will ultimately be interpreted as sql table names, while generic {cmd:^g:} tags will not. Replacement text can be specified in the {opt swap()} option of either a {cmd:^ssc=} {it:part definition}, or the {help sql do} command itself. {pstd}If no swap is specified for a particular tag, the prefix is stripped and, for {cmd:^g:} tags, {it:name} is used in the final sql. For {cmd:^t:} tags, {it:name} is first interpreted as a table: if it matches an earlier {cmd:^t=}{it:name} (one that hasn't been excluded by {opt only()}), that table will be referenced; otherwise, {it:name} is given the current schema accoring to the {help sql path}. {pstd}Although you can specify tables withuot a {cmd:^t:} tag, it generally makes sense to use the tags when referring to your own tables. For example, if you create {hi:tableA} early in your script and refer to it at a later point, you can have the unmodified script execute using some {hi:alternate-tableA} by setting the {opt only()} and/or {opt not()} options on the command line. Handy for debugging. {pstd}Also, note that uploads (ie, {cmd:^use=} and {cmd:^codex=}) are created on the server with temporary names, and using {cmd:^t:}{it:name} will reference the correct temporary table. {pstd}All tags and their ultimate renditions (and the tables used & excluded by the script) are tracked by {help sql do}, and summarized in its {bf:preview}.