It is entirely possible to have a significant installation of Actuate that never needs to override the ObtainSelectStatement( ) (OSS). I haven’t seen one and I doubt it happens much. Understanding how to override this method is a core skill for a good Actuate developer.
Overriding the DataStream object’s OSS allows you to programmatically design your SQL query based on any sort of external information: user input, configuration files, or even live data. Users running reports directly or by proxy through schedules could feed your query simple information about the date range desired or the shipping destination of an order. An external flat file might direct the report to use a development database instead of a production database or maybe one schema instead of another. A report can even be designed such that one part of the report queries the database first and returns information transmitted in a second query that then populates your report. There are lots of reasons to modify a SQL statement at run time.
Of course you may just want to preserve the whitespace and layout of your SQL as you go from one tool to build the code to Actuate to run it.
Disable the Superclass
Function ObtainSelectStatement( ) As String
''' ObtainSelectStatement = Super::ObtainSelectStatement( )
' Insert your code here
End Function
In nearly every case you are going to comment out or remove the call to the superclass. Your code will be doing all the work.
Local Variables
Function ObtainSelectStatement( ) As String
''' CREATE LOCAL VARIABLES
dim sqlstmt as String
dim NL as String
dim sqlSchema as String
dim beginDate as String
dim endDate as String
dim category as String
''' ASSIGN LOCAL VARIABLES
sqlstmt = "" 'used throughout to contain the growing SQL statement
NL = Chr$(13) & Chr$(10) 'easier to read output
sqlSchema = cfgSchema 'aids scaling from one schema to another
beginDate = reqBeginDate 'allows for user or schedule input at run time
endDate = reqEndDate 'same
category = reqCategory 'user input
[snip - sql statement]
ObtainSelectStatement = sqlstmt
End Function
Plain Substitution
Function ObtainSelectStatement( ) As String
[snip - variable declarations]
''' SELECT ''''''''''
sqlstmt = sqlstmt & "SELECT c.customer_name as NAME " & NL
sqlstmt = sqlstmt & ", c.customer_id as CUSTID" & NL
sqlstmt = sqlstmt & ", c.address as ADDRESS " & NL
sqlstmt = sqlstmt & ", c.city as CITY " & NL
sqlstmt = sqlstmt & ", c.state as STATE " & NL
sqlstmt = sqlstmt & ", c.postal_code as ZIP " & NL
''' FROM ''''''''''
sqlstmt = sqlstmt & NL & "FROM " & sqlSchema & ".customer c " & NL
''' WHERE ''''''''''
sqlstmt = sqlstmt & NL & "WHERE c.active_dttm BETWEEN timestamp('" & beginDate & "') " & NL
sqlstmt = sqlstmt & " AND timestamp('" & endDate & "') " & NL
''' ORDER BY ''''''''''
sqlstmt = sqlstmt & NL & "ORDER BY NAME " & NL
sqlstmt = sqlstmt & ", CUSTID " & NL
ObtainSelectStatement = sqlstmt
End Function
Above you should locate five of the local variables used in the construction of the SQL statement. ’sqlstmt’ and ‘NL’ are employed in constructing the statement itself. ’sqlSchema’, ‘beginDate’, and ‘endDate’ are used for replacement of strings based on implied user input for the date range and configuration information for the schema.
Yes, I am a little nuts when it comes to whitespace and readability.
Conditional Code
Function ObtainSelectStatement( ) As String
[snip]
''' WHERE ''''''''''
If myCategory = "Active" Then
sqlstmt = sqlstmt & NL & "WHERE c.active_dttm BETWEEN timestamp('" & beginDate & "') " & NL
sqlstmt = sqlstmt & " AND timestamp('" & endDate & "') " & NL
ElseIf myCategory = "Closed" Then
sqlstmt = sqlstmt & NL & "WHERE c.closed_dttm BETWEEN timestamp('" & beginDate & "') " & NL
sqlstmt = sqlstmt & " AND timestamp('" & endDate & "') " & NL
Else
'No Where Clause
End If
[snip]
ObtainSelectStatement = sqlstmt
End Function
Based on the value a user (or a pre-made schedule) submits for the “Category” parameter a different date field can be used to filter the data. ACTIVE_DTTM when “Active” or CLOSED_DTTM when “Closed”.
I’ve seen the OSS overridden to swap out values for every aspect of the SQL statement; swap out values in the select, choose different tables–even add additional tables, drastically remodel the filtering of the where, or resort the ordering. Most typically I see it used to customize the where clause.
Recent Comments