Actuate Programming Journal

Report design from a guy who thinks he knows what he’s doing; you decide . . . .

Archive for the ‘SQL’ Category

Commonly Overridden: ObtainSelectStatement( )

with 8 comments

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.

Written by Douglas

20071120 at 18:44 pm

YYYYMMDDHHMMSS.ZZZZZZ WTF?

with 3 comments

Somewhere along the way I created a report to compare the elapsed time from OPEN to CLOSED to a target number of hours. And it works (or no one complained).

I must not have done enough research, because I learned today that in DB2 TODAY minus YESTERDAY doesn’t equal 1 day, 24 hours, 1440 minutes, or even 86400 seconds.

It equals 1000000.000000

Huh?

The value returned when subtracting one date from the next looks like a number, feels like a number, acts like a number, but isn’t. Not really. Its a coded string. And the format is: YYYYMMDDHHMMSS.ZZZZZZ. It took several minutes of banging my head to come to grips with this silliness followed by more than a bit of screwing around to realize that the answer to my problem was to convert the static target values I comparing my results with to this format:

4 hours = 40000
48 hours = 2000000 (not 480000)
47 hours = 1230000

And that’s great for me for this report since my targets are static and I just hardcode them in to the SQL in the report. But what happens when I need to display the returned ‘numeric’ value as a pleasant (pleasant = appealing to customer) looking duration?

From 3/30/2006 8:59:23 AM to 4/6/2006 3:26:32 PM returns 7062709.000000 (not pleasant)

7 days, 6 hours, 27 minutes, and 9 seconds (pleasant)

You and I can do that our heads, but in a report we’d have to distill that logic into a complex parsing code in order to get that pleasant result. Sure, that just a little more code maybe a handy function, but come on, why isn’t pleasant the default?

Further, what if the customer wanted this in just hours or minutes? Not only would I have to parse the returned ‘numeric’ value as if it were a string, but I would have to come up with all the multiplication too. Don’t even ask me to think about how to deal with the fact that there aren’t a consistent number of days in a month.

I have to assume that sharper folks than I are building enterprise class databases and that they decided that this was best for us all. Anyone know why?

Consider yourselves warned.

Tags: , , ,

Written by Douglas

20061025 at 12:21 pm

Posted in SQL

Reordering… sorta

leave a comment »

Stop reading when you find this lame. I love figuring out little tidbits of SQL. Not so much the SQL, but figuring out how to use any language to elegantly do my work for me.

I have a report that uses data from a table to build out the layout of the report. The customer wished for me to re-order the results of the report in descending order and for reasons I won’t get into I couldn’t just slap a modifying desc into the report’s SQL statement. I had to do a one time fix of the data itself.

Now, I could have just piped out the existing data into a spreadsheet, changed the numbers used for ordering, concocted a bunch of update statements, and run those all back to back, but where’s the grace in that?

So, I hit up a buddy that is good with numbers. There should be a way to do this with a single update statement I said. Here it is:
update table set ordering_value = abs(ordering_value - 200);

Where 200 was my personal interpretation of max(ordering_value + 1). No need to code up a sub-select for that bit when I can easily figure out that value.

Before
A 1
B 2
C 3
D 4
E 5
...
Z 199

After
Z 1
...
E 195
D 196
C 197
B 198
A 199

Written by Douglas

20061018 at 18:43 pm

Posted in SQL

Sort by One More Thing

leave a comment »

You should always sort by one more item than you are asked to in your requirements. Or for that matter one more item than your group sections require.

Unless it would be silly to do so.

Lately, I have been working a little fast and loose with requirements due to some time constraints. It’s a little like RAD but without any of the trappings of calling it that. In any case it’s nice for what it is, but a bit distracting for how it sucks my time away from other clients. The cool part is that for the first time in a long time, I have been able to impose my considerable report writing experience on the output more than I normally might.

Basically, if your last sort item in your ORDER BY clause is not detailed enough to reach the record level, make it that way.

Don’t stop at STATE when you could go to CITY. Don’t stop at MANUFACTURER when you could go to SERIAL_NUM.

You should never have a block of records on a report that could have just a little more organization than they do.

Many of the reports that I have been working with in the past weeks have been nearly nothing more than SELECT * FROMs. And at least in one case they asked me to sort by a worthless value first (one I would have chosen closer to last–if at all). We discussed and decided to insert two more sorting items before the requested one, but to leave the layout as plain as originally dictated. Without changing the layout by adding neat headers for each of the two group sections I added along with the additions to the ORDER BY, I was able to dress up the TOC so that the organization is still available even though its not visible in the normal report layout. And, for the first time ever in a real report, I got to use the GroupOn property to GroupOnHour. Looks good in the TOC after you apply this formatting to TocValueExp property:

Format$([BIRTH_DATE], "h AM/PM")

Tags: , ,

Written by Douglas

20061013 at 13:14 pm

Posted in Actuate, Best Practices, SQL

Date Wrapping

leave a comment »

I have a report that is the presentation piece for some end to end poor design.

For reasons that are no longer known to me the database uses what we refer to as snapshots. These snapshots are exactly what you would think: pictures of a specific table at a specific time. I am sure that some obvious drawbacks come to mind concerning the bulk of data we must be repeating in a table or the chance that a snapshot is missed on a particular day. That is not what I am here to divulge today.

The report provides eight weeks worth of snapshots as well as comparisons (adds and subtracts) between consecutive weeks. The data is displayed as couple of slightly different charts, a bar chart, and a pie chart. It captures the information using a complicated set of UNIONs between each snapshot to basically diff the data. Week 1 had 100 widgets; week 2 had 110. That means that 5 were lost and 15 were gained, not that 10 were gained.

This is all great till you discover that 2005 has 53 weeks not just 52.

This post serves as a warning more than instruction. If you have to compare week to week data wrapping across a year/year threashold keep in mind that–as hard as this can be on its own–you will need to find a place in your calculations for 53 weeks in a year. That and you’ll finally get to use mod( ).

In one of the charts I have used a column numbering system that allows me to put rolling weeks worth of data in it. So that this week is always in the first column no matter what week it is. To create these numbers (1-9) I am comparing the current date to the week (1-53) that the snapshot was taken.

(mod(((week(current timestamp) - WIDGETS.SNAPSHOT_WEEK) + 53), 53) + 1) as CHART_COLUMN

By subtracting the snapshot week from the current week and adding 53 I get a value that tells me how many weeks back from this week the snapshot was. So a snapshot on week 48 of the year is 5 weeks back from the first week of this year. Unfortunately this results in a vlaue for this week compared this week as being 53 instead of 0. Running the results through mod(53) keeps all the values the same except the 53 which is converted into a 0. So now with 9 weeks of data I have CHART_COLUMN values of (0-8).

Though I am only displaying 8 weeks of data, I need to have the 9th one back to properly display the differences for week 8.

In all, I had originally used mod(52), because–you know–there are 52 weeks in a year. The result was in one chart causing week 53 to be lumped in with week 1 an in another dropping it totally. By simply using mod(53) is solved all my problems.

Hope everyone had a great week 53 of 2005!

Written by Douglas

20060104 at 16:56 pm

Posted in SQL