Menu: Found in the DATA menu
Use the SQL command to construct a single SQL Select statement ("on the fly") to retrieve data via ODBC from a relational data source (either local or on an Arbutus Windows Server).
Note: The SQL command only supports specification of one Select statement at a time.
The SQL command creates a temporary live read of the specified relational data source using the SQL Select criteria specified. When you close Analyzer or switch Analyzer projects the retrieved data is discarded.
Note: You must specify a temporary object prefix prior to issuing the SQL command. This is because temporary prefix is always added to the front of the temporary table layout name created by the issued SQL Select command. For more information see the "Use Temporary Object Prefix setting" in Interface Options.
To issue this command using the SQL command dialog, enter the following information:
| 1. | Specify whether the data source is local or server-based. If server-based, select the server profile for the appropriate Arbutus Windows Server. |
| 2. | Select either the Data Sources or File DSN's radio button. |
| 3. | Choose the desired data source from the "Choose ODBC Connection" pull-down menu. For File DSN's you can also click the [Browse] button to choose the applicable File DSN. |
| 4. | Select the "Save as Default" check box if you want to Analyzer to default to the selected data source each time the SQL command dialog is launched. |
| 5. | Enter the applicable UserID and Password for the selected data source. Click the "Save" check box if you want to save these values for future use in your registry. |
| 6. | Choose the "Convert SQL to ASCII" check box if you want any UTF16 data that is read to be outputted as ASCII. This option should only be used if you are confident that the data only contains North American English characters. This option inserts the keyword ASCII into the resulting SQL command syntax. |
| 7. | Enter the desired SQL Select statement to retrieve the desired data from the selected data source. Click the [Validate] button to verify the validity of the entered SQL Select statement. |
| 8. | Click OK. |
Since the retrieved data set is temporary, if you want to keep the data, use the Extract command to create a permanent copy as a flat file.
SQL Select statements can also be issued via the Data Definition Wizard. For more information see Importing ODBC Data Sources and Defining Relational Data Sources.
The SQL command supports all valid SQL Select parameters applicable to the chosen data source. Use the [Validate] button to test whether you have correctly entered only valid Select parameters for the chosen data source.
From the command line or in a procedure, the desired SQL Select statement must be preceded with the following syntax (which is automatically added to the Select command if entered via the command dialog).
SQL SOURCE "data-source-name" USERID user-id PASSWORD n <ASCII> {LOCAL|SERVER "server-profile-name"} SELECT...¿
Note: If a data source has already been set, then the information between the keyword SQL and the keyword SELECT is optional. For more information on setting a default data source see Set Source.
SQL SOURCE "SQL Server 2008" USERID arbpm1 PASSWORD 1 SERVER "Arbutus Windows Server" SELECT Orders.CustomerID, Orders.OrderID, Customers.CustomerID, Customers.CompanyName FROM Orders LEFT JOIN Customers ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CompanyName ¿
Note: The SQL command only supports specification of one Select statement at a time.