6.8.3 Building and Saving a Query

Note: Building and Saving a Query can be done in either Query Builder Dialog Box or SQL Editor Dialog Box in Origin. Query Builder is only available for Origin 32-bit. You can run Add or Remove Files and choose Modify to install both 32-bit and 64 bit Origin.

Using Query Builder

Query Builder Dialog box can be opened in the following ways:

  • Click the Open Query Builder button on the Database Access toolbar.
  • From the Origin menu, select File: Database Import: New.
Query dialog box.png

Connecting to a Datasource

If you have not previously connected to your datasource, you will need to set up a connection:

  1. In the Query Builder Dialog, choose Query: Data Source: New... menu. This opens the Data Link Properties dialog box.
  2. On Provider tab, select applicable OLE DB Provider, then click Next button to go to Connection tab.
  3. Select or enter a database name (If the database is not listed, simply type its name).
  4. Enter User name and Password.
  5. Click OK (other tab controls are optional) to connect to your data source.
  6. You may also need to set some SQL syntax options, depending on the type of database being queried, in the Setting: Options menu.

Building a query

Once you have established a connection to your data source, you will see a list of objects to the right side of the Query Builder Dialog box.

Note: The following example is using Stars.mdb under Samples\Import and Export\ folder of installed Origin.

Building and Saving a Query-2.png
  1. Drag the desired objects from the Tables pane to the Query Building pane.
  2. In the Query Building pane, select the fields that you wish to include in your query. To include all fields from an object, select the "*" box.
  3. Use the controls in the Columns pane to remove fields from the output (Output), sort the output (Sort Type and Sort Order), define criteria for the Expression (Criteria), Define multiple Criteria using the Or columns, or define groups (Group By). For more information on use of the Columns pane controls, see The Query Builder dialog box in the Origin Help file..
  4. To preview the first 50 lines of your query, click the Show Preview button Button Expand Preview Down.png and click Preview.
  5. Click the Import button to import data to your Origin workbook.

Creating a custom query by SQL scripting

With a data source connection, you can create a custom query using SQL scripts. Click the Show SQL button to open the query string dialog box. Then you can enter your own SQL script and click the Apply button. After this, you may see the changes in Column pane and Query Building Pane. If you are satisfied with the query, click the Import button to import data.

Saving a query

You can save a query to a workbook or an ODQ file by selecting either Query: Save to Active Sheet or Query: Save as ODQ File from the menu of the Query Builder dialog box. In addition, clicking the Import button in the Query Builder dialog box will cause the query to be saved in the target workbook.

Saving a query to workbook allows you to preview the data on the workbook and import the data at any time you want. The workbook can be saved as a template, which can also store information on the formatting of the workbook. Next time you open the template, you will have a formatted workbook with a customized query saved in it. Merely clicking the Import Data Button db Import Data.png button on the Database Access toolbar will fill the formatted workbook with data acquired from the data source. To view or modify a query saved in active worksheet, click Open Query Builder button on Database Access toolbar.

ODQ files can be loaded by clicking the Load ODQ File button Button db Load ODQ File.png on the Database Access toolbar or by selecting Query: Open from the Query Builder dialog box. After an ODQ file is loaded, you can apply the query saved in it to the active workbook. This is useful when you want to apply the query to many different workbooks.

Using SQL Editor

SQL Editor Dialog box can be opened in the following ways:

  • Click the Open SQL Editor button on the Database Access toolbar.
  • From the Origin menu, select File: Database Import: New.
Sqleditor.png

Connecting to a Datasource

If you have not previously connected to your datasource, you will need to set up a connection:

  1. In the SQL Editor Dialog, choose File: New... menu. This opens the Data Link Properties dialog box.
  2. On Provider tab, select applicable OLE DB Provider, then click Next button to go to Connection tab.
  3. Select or enter a database name (If the database is not listed, simply type its name).
  4. Enter User name and Password.
  5. Click OK (other tab controls are optional) to connect to your data source.

Building a query

Once you have established a connection to your data source, you will see a list of objects on the left panel of SQL Editor dialog.

Double click the table or node below the table will add the table name to the SQL Edit box on right panel. Or you can directly paste the SQL string you received from somewhere else.

Saving a query

You can save a query to a workbook or an ODQ file by selecting either File: Save to Active Sheet or File : Save Connection and Query as... menu. In addition, clicking the Import button in the SQL Editor dialog box will automatically save the Query in the target worksheet.

Saving a query to workbook allows you to preview the data on the workbook and import the data at any time you want. The workbook can be saved as a template, which can also store information on the formatting of the workbook. Next time you open the template, you will have a formatted workbook with a customized query saved in it. Merely clicking the Import Data Button db Import Data.png button on the Database Access toolbar will fill the formatted workbook with data acquired from the data source. To view or modify a query saved in active worksheet, click Open SQL Editor button on Database Access toolbar.

ODQ files can be loaded by clicking the Load ODQ File button Button db Load ODQ File.png on the Database Access toolbar. All recently saved ODQ files are also listed under File: Database Import: menu. After an ODQ file is loaded, you can preview or import it to the active workbook. This is useful when you want to apply the query to many different workbooks.