Actuate Programming Journal

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

OSS( ): Readable SQL

with 2 comments

When I first started out, overriding the ObtainSelectStatement( ) method was some kind of crazy magic. Only the hardcore report developers did that. Now, the Graphical Query Builder background could be full of pictures of naked women and I wouldn’t know. I am sure I haven’t used it since v6. I believe that the work of reporting is done in the SQL and on the database–that Actuate is about making it pretty and accessable. Anyhow, that’s really the start of a whole different post I just wanted to share this little snippet for making the output of your SQL string readable when you capture it and re-run in the SQL editor or your choice (or your company’s).

  
Function ObtainSelectStatement( ) As String

dim SQL as string   '''HANDROLLED SQL
dim LF  as string   '''LINE FEED

LF      = Chr$(13) & Chr$(10)

SQL = SQL & "SELECT O.ORDER_ID AS ORDERID " & LF
SQL = SQL & ", O.ITEM          AS ITEM "    & LF
SQL = SQL & "FROM ORDER O "                 & LF

ObtainSelectStatement = SQL

End Function

There are plenty other tidbits in there aside from the nutso level of organization I like to impose on my code through the use of whitespace, but I will leave those and that for later.

Advertisements

Written by Douglas

20060109 at 15:08 pm

2 Responses

Subscribe to comments with RSS.

  1. neatly done. you make it look ohh so simple.

    but if you do it in the function part….you have to fetch the data row in function also. Right?

    taufeq

    20060721 at 01:55 am

  2. Absolutely.

    Here I was only illuminating the easy ways to add whitespace to make the resulting SQL string easier to read in another program once you pull it using a breakpoint.

    The whitespace helps line up the various columns and the linefeeds wrap the string where you want it wrapped.

    More on the relationship between the OSS( ) and the BDR( ) and why I prefer them to all the other options later.

    Douglas

    20060721 at 07:38 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: