Creating Data Sets Using SQL Queries

These topics explain how to create data sets using SQL queries.

• Entering SQL Queries

• Creating Non-Standard SQL Data Sets

• Using the SQL Query Builder

• Adding a Bind Variable to a Query

• Adding Lexical References to SQL Queries

• Defining SQL Queries Against the Oracle BI Server

Entering SQL Queries

Use these steps to enter SQL queries.

For information about optimizing your SQL Queries and the Generate Explain Plan option, see Best Practices for SQL Data Sets.

1. Click New Data Set and then click SQL Query. The Create Data Set - SQL dialog opens, as shown below.
   
   Description of the illustration GUID-32B5E9C3-C794-4F74-92EA-9978DC6E0A28-default.gif
2. Enter a name for the data set.
3. The data source defaults to the default data source that you selected on the Properties page. If you are not using the default data source for this data set, select the Data Source from the list.

   You can also use your private data source connections as data sources for SQL query data sets. See Managing Private Data Sources for information about private data source connections.

4. The SQL type defaults to Standard SQL used for normal SELECT statements interpreted to understand database schema. See Creating Non-Standard SQL Data Sets for information on using other types of SQL.
5. Enter the SQL query or click Query Builder to launch the Query Builder page. See Using the SQL Query Builder for more information about the Query Builder utility.
6. If you are using Flexfields, bind variables, or other special processing in your query, edit the SQL returned by the Query Builder to include the required statements.

   Note:

   If you include lexical references for text that you embed in a SELECT statement, then you must substitute values to get a valid SQL statement.

7. After entering the query, click OK to save. For Standard SQL queries the data model editor validates the query.

   If your query includes a bind variable, you are prompted to create the bind parameter. Click OK to have the data model editor create the bind parameter.

   See Adding Parameters and Lists of Values for more information on editing parameters.

Creating Non-Standard SQL Data Sets

In addition to creating data sets using basic SQL commands, you can create data sets using more complex commands.

Procedure Call

Use this query type to call a database procedure. For example, Oracle PL/SQL statements start with BEGIN. When you use this SQL data type, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. To construct your SQL with a procedure call enter the code directly in the text box or copy and paste from another SQL editor. You cannot use the Query Builder to modify or build these types of queries.

Non-standard SQL

Use this query type to issue SQL statements that can include the following:

• Cursor statements that return nested results sets

  For example:

    Ex:SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
    CURSOR
     (SELECT d.order_id department_id,
             d.order_mode department_name ,
             CURSOR
             (SELECT e.cust_first_name first_name,
               e.cust_last_name last_name,
               e.customer_id employee_id,
               e.date_of_birth hire_date
             FROM customers e
             WHERE e.customer_id IN (101,102)
             ) emp_cur
      FROM orders d
      WHERE d.customer_id IN (101,102)
    ) DEPT_CUR FROM dual
  

• Functions returning REF cursors

  For example:

  create or replace PACKAGE REF_CURSOR_TEST AS
    TYPE refcursor IS REF CURSOR;
    pCountry  VARCHAR2(10);
    pState   VARCHAR2(20);
    FUNCTION GET( pCountry IN VARCHAR2, pState   IN VARCHAR2) RETURN  REF_CURSOR_TEST.refcursor;
    END;
  
  

    create or replace PACKAGE BODY REF_CURSOR_TEST AS
    FUNCTION GET(
     pCountry  IN VARCHAR2,
     pState    IN VARCHAR2)
    RETURN REF_CURSOR_TEST.refcursor
    IS
    l_cursor REF_CURSOR_TEST.refcursor;
    BEGIN
       IF ( pCountry = 'US' ) THEN
         OPEN l_cursor FOR 
         SELECT TO_CHAR(sysdate,'MM-DD-YYYY') CURRENT_DATE ,
             d.order_id department_id,
             d.order_mode department_name
         FROM orders d
         WHERE d.customer_id IN (101,102);
       ELSE
         OPEN l_cursor FOR 
         SELECT * FROM EMPLOYEES;    
       END IF;   
       RETURN l_cursor;
     END GET;
    END REF_CURSOR_TEST;
  
  

  To use REF cursor in Oracle BI Publisher:

  create SQL dataset with query as  SELECT REF_CURSOR_TEST.GET(:PCNTRY,:PSTATE) AS CURDATA FROM DUAL 
  

• Anonymous blocks/Stored procedures

  BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.

  The requirements are:

  • The PL/SQL block must return a result set of type REF cursor

  • You must declare the out variable with the name, xdo_cursor;. If you do not declare the name properly, the first bind variable is treated as an out variable type and binds with REF cursor

  • Declare the data model parameter with name xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.

  Example:

  DECLARE
         type refcursor is REF CURSOR;
         xdo_cursor refcursor;
         empno number;
      BEGIN 
        OPEN :xdo_cursor FOR 
         SELECT * 
         FROM EMPLOYEES E
         WHERE E.EMPLOYEE_ID = :P2;
         COMMIT;
      END;
  

• Conditional queries can be executed if you use an if-else expression. You can define multiple SQL queries in a single data set, but only one query executes at run time depending on the expression value. The expression validates and returns a boolean value. If the value is true, executes that section of the SQL query.

  The limitations are:

  • The following syntax is supported to evaluate expressions: $if{, $elseif{, $else{

  • The expression must return true, false

  • Only the following operators are supported:

    == <= >= < >

   Example:
     create sql dataset with following query
     $if{ (:P_MODE == PRODUCT) }$
                  SELECT PRODUCT_ID
                  ,PRODUCT_NAME
                  ,CATEGORY_ID
                  ,SUPPLIER_ID
                  ,PRODUCT_STATUS
                  ,LIST_PRICE
                  FROM PRODUCT_INFORMATION 
                  WHERE ROWNUM < 5
     $elsif{(:P_MODE == ORDER )}$
                  SELECT ORDER_ID
                    ,ORDER_DATE
                    ,ORDER_MODE
                    ,CUSTOMER_ID
                    ,ORDER_TOTAL
                    ,SALES_REP_ID
                  FROM ORDERS
                  WHERE ROWNUM < 5
     $else{ 
                 SELECT PRODUCT_ID
                 , WAREHOUSE_ID
                 ,QUANTITY_ON_HAND
                 FROM INVENTORIES
                 WHERE ROWNUM < 5
                 }$
     $endif$
  

When your data set is created using non-standard SQL statements, no metadata is displayed on the data model structure tab, therefore you cannot modify the data structure or data fields. You cannot use Query Builder to modify or build these types of queries.

To define XML row tag for non-standard SQL dataset:

Use xmlRowTagName="" in data model definition to define xml row tag for non-standard SQL query dataset. This allows you to enter a valid tag name. If the attribute is empty, it defaults to ROW at runtime.

Dataset definition:

<dataSet name="Q1" type="simple">
        <sql dataSourceRef="bipdev4-demo" nsQuery="true"  xmlRowTagName="">
                ,,
        </sql>
</dataset>

Using the SQL Query Builder

Use the Query Builder to build SQL queries without coding. The Query Builder enables you to search and filter database objects, select objects and columns, create relationships between objects, and view formatted query results with minimal SQL knowledge.

This section describes how to use the Query Builder and includes the following topics:

• Overview of the Query Builder

• Understanding the Query Builder Process

• Supported Column Types

• Adding Objects to the Design Pane

• Removing or Hiding Objects in the Design Pane

• Specifying Query Conditions

• Creating Relationships Between Objects

• Saving a Query

• Editing a Saved Query

Overview of the Query Builder

The Query Builder page is divided into an Object Selection pane and a design and output pane.

• Object Selection pane contains a list of objects from which you can build queries. Only objects in the current schema display.

• Design and output pane consists of four tabs:

  • Model — Displays selected objects from the Object Selection pane.

  • Conditions — Enables you to apply conditions to your selected columns.

  • SQL — Displays the query.

  • Results — Displays the results of the query.

  
  

  
  Description of the illustration GUID-937D8FCE-95CC-41B4-AE8F-62E83DE383BC-default.gif

  
  

Understanding the Query Builder Process

Perform these steps to build a query.

1. Select a schema.

   The Schema list contains all the available schema in the data source. You might not have access to all that are listed.

2. Add objects to the Design pane and select columns.

   The Object Selection pane lists the tables, views, and materialized views from the selected schema, for Oracle Database, synonyms are also listed. Select the object from the list and it displays on the Design pane. Use the Design pane to identify how the selected objects are used in the query.

   You might need to use the Search field to enter a search string. If more than 100 tables are present in the data source, you must use the Search feature to locate and select the desired objects.

3. (Optional) Establish relationships between objects.

4. Add a unique alias name for any duplicate column.

5. (Optional) Create query conditions.

6. Execute the query and view results.

Supported Column Types

Columns of all types display as objects in the Design pane.

The column restrictions are:

• You can select no more than 60 columns for each query.

• You can select the following column types:

  • VARCHAR2, CHAR

  • NUMBER

  • DATE, TIMESTAMP

    Note:

    The TIMESTAMP WITH LOCAL TIMEZONE data type is not supported.

  • Binary Large Object (BLOB)

    Note:

    The BLOB must be an image. When you execute the query in the Query Builder, the BLOB does not display in the Results pane; however, the query is constructed correctly when saved to the data model editor. BI Publisher does not support querying of BLOB columns in an Oracle BI EE data source.

  • Character Large Object (CLOB)

    Note:

    BI Publisher does not support querying of CLOB columns in an Oracle BI EE data source.

    See Using Data Stored as a Character Large Object (CLOB) in a Data Model for more information about working with CLOB data in the data model.

Adding Objects to the Design Pane

Select each object you want to add to the Design pane.

• When you add an object, an icon representing the data type displays next to each column name.

• When you select a column, it appears on the Conditions tab. The Show check box on the Conditions tab controls whether a column is included in query results. Be default, this check box is selected.

• To select the first twenty columns, click the small icon in the upper left corner of the object and then select Check All.

• You can also execute a query by pressing the CTRL + ENTER keys.

1. Select an object.
2. Select the check box for each column to include in your query.
3. To execute the query and view results, select Results.

Removing or Hiding Objects in the Design Pane

You can remove or hide objects in the Design pane.

To remove an object:

1. Click Remove in the upper right corner of the object.

To temporarily hide the columns within an object:

1. Click Show/Hide Columns.

Specifying Query Conditions

Conditions enable you to filter and identify the data you want to work with.

As you select columns within an object, you can specify conditions on the Conditions tab (shown below). You can use these attributes to modify the column alias, apply column conditions, sort columns, or apply functions.

Description of the illustration GUID-9FBFD7BA-8576-48FA-8EA8-A6A540C6AD92-default.gif

The table below describes the attributes available on the Conditions tab.

---

Condition Attribute	Description
Up and Down Arrows	Controls the display order of the columns in the resulting query.
Column	Displays the column name.
Alias	Specify an optional column alias. An alias is an alternative column name. Aliases are used to make a column name more descriptive, to shorten the column name, or prevent possible ambiguous references. Note that multibyte characters are not supported in the alias name.
Object	Displays the object name.
Condition	The condition modifies the query's WHERE clause. When specifying a column condition, you must include the appropriate operator and operand. All standard SQL conditions are supported. For example: >=10 ='VA' IN (SELECT dept_no FROM dept) BETWEEN SYSDATE AND SYSDATE + 15
Sort Type	Select ASC (Ascending) or DESC (Descending).
Sort Order	Enter a number (1, 2, 3, and so on) to specify the order in which selected columns should display.
Show	Select this check box to include the column in your query results. You do not need to select Show to add a column to the query for filtering only. For example, to create following query: SELECT ename FROM emp WHERE deptno = 10 To create this query in Query Builder: From the Object list, select EMP. In the Design Pane, select ename and deptno. For the deptno column, in Condition enter =10 and clear the Show check box.
Function	Available argument functions include: Number columns — COUNT, COUNT DISTINCT, AVG, MAXIMUM,. MINIMUM, SUM VARCHAR2, CHAR columns — COUNT, COUNT DISTINCT, INITCAP, LENGTH, LOWER, LTRIM, RTRIM, TRIM, UPPER DATE, TIMESTAMP columns- COUNT, COUNT DISTINCT
Group By	Specify columns to be used for grouping when an aggregate function is used. Only applicable for columns included in output.
Delete	Deselect the column, excluding it from the query.

---

As you select columns and define conditions, Query Builder writes the SQL for you.

To view the underlying SQL:

• Select the SQL tab.

Creating Relationships Between Objects

You can create relationships between objects by creating a join. A join identifies a relationship between two or more tables, views, or materialized views.

• About Join Conditions

• Joining Objects Manually

About Join Conditions

When you write a join query, you specify a condition that conveys a relationship between two objects. This condition is called a join condition.

A join condition specifies how the rows from one object combine with the rows from another object.

Query Builder supports inner, outer, left, and right joins.

• An inner join, also called a simple join, returns the rows that satisfy the join condition.

• An outer join extends the result of a simple join.

  An outer join returns all rows that satisfy the join condition and returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Joining Objects Manually

Create a join manually by selecting the Join column in the Design pane.

To join objects manually:

1. From the Object Selection pane, select the objects you want to join.
2. Identify the columns you want to join.

   You create a join by selecting the Join column adjacent to the column name. The Join column displays to the right of the data type. When your cursor is in the appropriate position, the following help tip displays:

   Click here to select column for join

3. Select the appropriate Join column for the first object.

   When selected, the Join column is darkened. To deselect a Join column, simply select it again or press ESC.

4. Select the appropriate Join column for the second object.

   When joined, line connects the two columns. An example is shown below.

   
   

   
   Description of the illustration GUID-D318AA89-E55F-45E4-962A-B4D21C33483F-default.gif

   
   
5. Select the columns to be included in your query. You can view the SQL statement resulting from the join by positioning the cursor over the join line.
6. Click Results to execute the query.

Saving a Query

Save queries after building them.

Once you have built the query, click Save to return to the data model editor. The query appears in the SQL Query box. Click OK to save the data set.

Description of the illustration GUID-32B5E9C3-C794-4F74-92EA-9978DC6E0A28-default.gif

Editing a Saved Query

When you have saved the query from the Query Builder to the data model editor, you can also use the Query Builder to edit the query.

If you have made modifications to the query, or did not use the Query Builder to construct it, you may receive an error when launching the Query Builder to edit it. If the Query Builder cannot parse the query, you can edit the statements directly in the text box.

1. Select the SQL data set.
2. On the toolbar, click Edit Selected Data Set to launch the Edit Data Set dialog.
3. Click Query Builder to load the query to the Query Builder.
4. Edit the query and click Save.

Adding a Bind Variable to a Query

After creating a query, you may want users to be able to pass a parameter to the query to limit the results. For example, in the employee listing, you want users to be able to choose a specific department.

The image shows the columns in the department table.

Description of the illustration GUID-353A05D9-BD61-469A-818E-62541FF9FACA-default.gif

• In the Query Builder Conditions tab, add the bind variable for the column using the following:

  in (:PARAMETER_NAME)

  Where PARAMTER_NAME is the name you choose for the parameter.

  Note:

  After manually editing the query, the Query Builder can no longer parse it. You must make any additional edits manually.

Adding a Bind Variable Using a Text Editor

Use the Data Model Editor to update a SQL query.

1. In the Edit Data Set text box, update the SQL query by adding the following after the where clause in your query:

   and and "COLUMN_NAME" in (:PARAMETER_NAME)

   for example:

   and "DEPARTMENT_NAME" in (:P_DEPTNAME)

   where P_DEPTNAME is the name you choose for the parameter, as shown below.

   
   

   
   Description of the illustration GUID-39C5E8B8-94F4-4116-9104-E29550469710-default.gif

   
   
2. Click Save.
3. In the data model editor, create the parameter that you entered with the bind variable syntax as shown in the image.
   
   

   
   Description of the illustration GUID-3AA16E17-754C-4059-A544-AD22BF980B71-default.gif

   
   
4. Select the parameter, and click OK to enable the data model editor create the parameter entry for you.

   See Adding Parameters and Lists of Values for more information on defining parameter properties.

Adding Lexical References to SQL Queries

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING.

Use a lexical reference when you want the parameter to replace multiple values at runtime. Lexical references are supported in queries against Oracle applications only.

Create a lexical reference in the SQL query using the following syntax:

&parametername

Note:

You also use lexical references to include flexfields in your query. For more information about using flexfields, see Adding Flexfields.

1. Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.
2. In the data model editor, on the Properties page, specify the Oracle DB Default Package.
3. In the data model editor, create a Before Data event trigger to call the PL/SQL package. See Adding Before Data and After Data Triggers for more information about procedures.
4. Create your SQL query containing lexical references.
5. When you click OK to close your SQL query, you are prompted to enter the parameter.

For example, create a package called employee. In the employee package, define a parameter called where_clause:

Package employee
AS
 where_clause varchar2(1000);
  .....
 
Package body employee
 AS
 .....
where_clause := 'where DEPARTMENT_ID=10';
.....

Reference the lexical parameter in the SQL query where you want the parameter to be replaced by the code defined in the package, for example:

select        "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",
         "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",
         "EMPLOYEES"."LAST_NAME" as "LAST_NAME",
         "EMPLOYEES"."SALARY" as "SALARY",
 from   "OE"."EMPLOYEES" "EMPLOYEES"
 &where_clause

When you click OK on the Create SQL Data Set dialog box, the lexical reference dialog box prompts you to enter a value for lexical references you entered in the SQL query, as shown in the image that follows. Enter the value of the lexical reference as it is defined in the PL/SQL package.

Description of the illustration GUID-5DDED982-225A-42C8-95D7-61EFBC0484FE-default.gif

At runtime, the data engine replaces &where_clause with the contents of where_clause defined in the package.

Defining SQL Queries Against the Oracle BI Server

When you launch the Query Builder against the Oracle BI Server, the Query Builder displays the subject areas from the catalog. You can drag the subject areas to the Query Builder workspace to display the columns. Select the columns to include in your data model.

Keep the following points in mind when creating a data set against the Oracle BI Server:

• When you create a SQL query against the Oracle BI Server using the SQL Data Editor or the Query Builder, logical SQL is generated, not physical SQL like other database sources.

• Hierarchical columns are not supported. The highest level is always returned.

• Within a subject area, the join conditions between tables are already created; it is therefore not necessary to create joins in the Query Builder. The Query Builder does not expose the primary key.

  It is possible to link data sets using the data model editor's Create Link function. See Creating Element-Level Links. For data sets created from the Oracle BI Server, there is a limit of two element-level links for a single data model.

• In the Query Builder, the functions Sort Order and Group By shown on the Conditions tab are not supported for queries against the Oracle BI Server. If you enter a Sort Order or select the Group By check box, the Query Builder constructs the SQL, and write it to the BI PublisherBI Publisher SQL Query text box, but when you attempt to close the Data Set dialog, the query fails validation.

  To apply grouping to the data retrieved by the SQL query, you can use the data model editor's Group by function instead. See Creating Subgroups.

• If you pass parameters to the Oracle BI Server and you choose Null Value Passed for Can Select All, you must ensure that you handle the null value in your query.

1. In the data model editor, click New Data Set and then click SQL Query.
2. Enter a name for the data set.
3. From the Data Source list, select the Oracle BI Server connection, usually shown as Oracle BI EE.
4. Click Query Builder to launch the Query Builder page. See Using the SQL Query Builder for more information about the Query Builder utility.

   You can also enter the SQL syntax manually in the SQL Query text box, however you must use the Logical SQL syntax used by the Oracle BI Server.

5. From the Catalog drop-down list, select a subject area as shown below. The list displays the subject areas defined in the Oracle BI Server.
   
   

   
   Description of the illustration GUID-3DCEE546-80E7-46FD-A706-B0D14A106F60-default.gif

   
   
6. Select tables and columns for the query.
7. Click Save.
8. Click OK to return to the data model editor. The SQL that is generated is Logical SQL that follows a star schema. It is not physical SQL.
9. Save your changes to the data model.

Notes for Queries Against Oracle Fusion Applications Tables

Special considerations for Oracle Fusion Applications customers apply when writing queries against the Oracle Fusion Applications tables

• You cannot return month name from sysdate using to_char(sysdate,"mon"). This function returns the month number. To display month name, use one of the following solutions:

  • Format the date field in your layout using the following syntax: <?format_date:fieldname;MASK)?>

    For more information about date formatting in your layout, see Formatting Dates in Report Designer's Guide for Oracle Business Intelligence Publisher.

  • To display month name based on month number, use the following syntax in your layout:

    <?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?>

    where month is the numeric value of the month (January = 1) and

    [abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate.

    For example:

    <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?>

    returns January

  • To add an expression in the data model, use the following expression:

    Format_date(date, format_String)

    For example:

    SUBSTRING(FORMAT_DATE(G_1.SYSDATE,MEDIUM),0,3)

    returns Nov (when the current SYSTDATE is November)