Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

Answers provides us with various methods for constructing dynamic SQL.  For example, we can create a column selector or we can embed a reference to a presentation variable within a filter.  However, the in-built opportunities to create dynamic SQL are limited, and – let’s be honest – they are “fiddly”, hidden away as the values of GUI items in various pop-up windows.  All too often it’s difficult to see “the wood for the trees”.

 

What we really need is a proper API for Answers, an API that would include the ability to dynamically create and run arbitrary SQL statements based on user and database inputs.

 

In this article we’ll get “stroppy” with the “Advanced” tab and bend its “nannying” functionality to achieve the objective of generating dynamic SQL based on presentation and server variable references.

 

 

Defining Dynamic SQL

 

Let’s start with a subject area of “SCOTT” – based on the “SCOTT” Oracle database schema – that contains presentation columns “Location”, “Department Name”, and “Total Salaries”.

 

If we create a new request for this subject area, and if we don’t select any columns, but go straight to the “Advanced” tab, then the “FROM” field contains “SCOTT” and the “SQL Issued” text box contains the text “SELECT SCOTT”:

 

Now, ideally, we’d like to replace this SQL fragment with a variable reference; for example, using a presentation variable:

 

If this approach actually worked, then we could populate the presentation variable dynamically based on user and database inputs, and we would have achieved our objective of dynamically generating an SQL statement.  Unfortunately, referencing a presentation variable in this manner does not work.  If we press the “Set SQL” button then we get an error “Invalid Select Argument”.  If we navigate to the “Criteria” tab and then back again to the “Advanced” tab, then our presentation variable reference disappears and the SQL fragment “SELECT SCOTT” reappears.

 

If we experiment with the “Advanced” tab functionality we discover that the minimum requirement to produce a working SQL statement is to have the “FROM” field populated and to have a column defined on the “Criteria” tab.  We also discover that the only fields that will allow presentation variable substitution are the “Prefix” and “Postfix” fields.

 

So let’s start off with a “Criteria” column of “Location”:

 

which gives us a SQL statement of:

 

If we add our presentation variable reference to the “Prefix” field:

 

and then navigate to the “Criteria” tab and back again to the “Advanced” tab then we get:

 

Now, of course, this formulation won’t work.  But if we can’t remove the unwanted default SQL created by Answers, then we need to disable it in some way.  The solution is to add the characters “/*” after the presentation variable reference in the “Prefix” field:

 

Now navigating to the “Criteria” tab and back again gives us:

 

With this prefix Answers will be happy and won’t throw an error, and the default SQL will be ignored when it arrives at the BI Server so that only the SQL referenced by the presentation variable will be executed.

 

We can eliminate the reference to the presentation column and change the column heading as required; for example:

 

so that our “Criteria” column becomes:

 

and the “SQL Issued” becomes:

 

 

Example – Presentation Variable Reference

 

To illustrate this dynamic SQL, let’s create a presentation variable based on an edit box:

 

If we create a dashboard based on this prompt and our dynamic request, then on entry we find that the default SQL statement contained in the presentation variable reference has been executed and has been used to populate the column:

 

If we type into the edit box the following SQL statement:

 

select 'Department ' || "department name" || ' is located in ' ||
       location
  from scott
 where "department name" != 'OPERATIONS'

 

then on pressing the “Go” button we get:

 

With a more generous text area we would have a mini SQL Plus.  Of course, in practice the SQL statement would be derived programmatically based on user and database inputs.

 

 

Example – Session Variable Reference

 

We can also use a reference to a session variable to define our dynamic SQL.  Let’s create an initialization block, “IB_SQL”, for session variable “SN_SQL” as follows:

 

So when a session is created the value of session variable “SN_SQL” will be:

 

select "department name" from scott

 

We can reference this session variable in the “Prefix” field on the “Advanced” tab as follows:

 

Note that, as is so often the case in Answers, a reference in the form:

 

VALUEOF(NQ_SESSION.SN_SQL)

 

will not work.  Also note that the correct form of the session variable reference, in the form “biServer.variables”, will only be refreshed at session start-up, so you will need to log out and back in again to Answers if you are testing this method of creating dynamic SQL .

 

If we add the request to a dashboard we get:

 

as expected.

 

 

Variants

 

The main limitation of this approach is that we can’t apply the same technique to the XML that defines the request formatting.  Hence, while the SQL may be dynamic the number of columns is not.  We can however apply this approach with a multiple number of predefined columns.  And as the “Prefix” field will allow multiple variable references we can use a construction such as:

 

select @{pv_col1}{coldef1},@{pv_col2}{coldef2}
  from @{pv_sql}{sqldef}

 

if required.  If we need a dynamic number of columns or dynamic column headers, then we can always post process the web page using HTML DOM.

 

 

Summary

 

This method for generating dynamic SQL opens up a raft of new functionality within Answers and Dashboards that is impossible to achieve using the default methodology or which requires complicated, multi-stage workarounds.  With this method we are one step removed from the tyranny of the standard “fill in the box” approach to creating SQL requests – an approach that has so many limitations.  And we are a little closer to achieving the goal of creating a flexible API for Answers and Dashboards.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2009 PWG Consulting, All Rights Reserved