SQL subcommand (GET DATA command)

SQL specifies any SQL select statement accepted by the database that you access.

• You can select columns from more than one related table in a data source using either the inner join or the outer join.
• Each line of SQL must be enclosed in quotation marks and cannot exceed 255 characters.
• When the command is processed, all of the lines of the SQL statement are merged together in a very literal fashion; so each line should either begin or end with a blank space where spaces should occur between specifications.
• For TYPE=OLEDB (available only on Windows operating systems), table joins are not supported; you can specify fields only from a single table.

Example

GET DATA /TYPE=ODBC /CONNECT=
  'DSN=Microsoft Access;DBQ=/data/demo.mdb;DriverId=25;'+
  'FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'
 /SQL =
  'SELECT  SurveyResponses.ID,  SurveyResponses.Internet,'
  ' [Value Labels].[Internet Label]'
  ' FROM SurveyResponses LEFT OUTER JOIN [Value Labels]'
  ' ON SurveyResponses.Internet'
  ' = [Value Labels].[Internet Value]'.

If the SQL contains WHERE clauses with expressions for case selection, dates and times in expressions need to be specified in a special manner (including the curly braces shown in the examples):

• Date literals should be specified using the general form {d 'yyyy-mm-dd'}.

• Time literals should be specified using the general form {t 'hh:mm:ss'}.
• Date/time literals (timestamps) should be specified using the general form {ts 'yyyy-mm-dd hh:mm:ss'}.
• The entire date and/or time value must be enclosed in single quotes. Years must be expressed in four-digit form, and dates and times must contain two digits for each portion of the value. For example January 1, 2005, 1:05 AM would be expressed as:

  {ts '2005-01-01 01:05:00'}

For functions used in expressions, a list of standard functions is available at http://msdn.microsoft.com/en-us/library/ms711813(VS.85).aspx.