SQL Lookup to Current Notes DB

HCL Domino Domino Forum
1

I’ve created a database with two forms and associated viewsThe first form (MainData) contains a list of documents with various key fields and number values e.g. order value. Each document using this form has three key fields that identify the grouping

The second form (TotalData) is to load documents that have documents that sum the order value for certain key conditions.

I have then created a lotusscript agent that reads through the MainData form/view and for each unique key (out of the four fields) , create a new TotalData document. Then want to run an SQL query over MainData to calculate the totals over the whole MainData db to enter into the new total document.

I have looked at the various information for @DBcolumn formula but this only appears to give totals for all documents in a view rather than a series of specific conditional selection and cannot seem to locate the lotusscript equivalent. Having a series of views with column totals for each permutation of keys is not realistic given the number of documents

I have managed to get this to work by creating an ODBC datasource and then performing ODBC lookups in lotusscript using this datasource, this concept which has been widely documented in help and this forum for connecting to other remote ODBC databases.

However as I am actually trying to execute an SQL query over the same database it seems a bit strange that I effectively have to outside the database and back in via a datasource.

Is there a way whereby I can issue the SQL query without the need of the datasource as this would reduce the admin overhead of datasources if I have to distribute the database. I feel that I have missed something obvious here.

Many Thanks

I show a subset of the code for reference

’ Set up ODC Connection

Dim nSession As New NotesSession

Dim nDatabase As NotesDatabase

Set nDatabase = nSession.CurrentDatabase

Dim odbcConn As New ODBCConnection

Dim odbcQry As New ODBCQuery

Dim odbcResult As New ODBCResultSet

Set odbcQry.Connection = odbcConn

Set odbcResult.Query = odbcQry

odbcConn.ConnectTo(“THISDB”)

'** Loop for each MainData doc and if not total form for key not already created , create new form with following

Set totalDoc = nDatabase.CreateDocument 

totalDoc.Form = "TotalData"

totalDoc.TotalKey1 = "keyval1"

totalDoc.TotalKey2 = "keyval1"

totalDoc.TotalKey3 = "keyval1"

totalDoc.Subgroup = subgroup

odbcQry.SQL = "SELECT SUM(OrderValue) FROM MainData WHERE Key1 = 'A' & " AND Key2 = 'B' and Key3 = 'C'" 

odbcResult.Execute

If odbcResult.IsResultSetAvailable Then

	totalDoc.TotalOrderValue = odbcResult.GetValue (1)

	odbcResult.Close(DB_CLOSE)

End If

totalDoc.Save True, False

'** loop

2

Subject: SQL Lookup to Current Notes DB

That’s actually a pretty clever approach. I always just build a doc collection based on my key, then loop through the collection and create whatever statistic I need. Using SQL’s ‘sum’ is kind of neat.

Unfortunately, I think what you see is what you get - if you’re going to use an ODBC source, you have to define the ODBC source.

I hope someone else has a clever way that this isn’t required. If you get something working, be sure to post it back here.

Doug

3

Subject: SQL Lookup to Current Notes DB

Wouldn’t it be simpler to just create a getMainDataByKey view with four sorted columns and traverse through this view with an agent, creating/updating the totals document for each?

4

Subject: RE: SQL Lookup to Current Notes DB

Yes in theory I could have built a view sequenced on the keys, but I was trying to reduce the number of views due to the size of the DB in terms of number of documents. The database is used to analyse transitory data and will have data totally refreshed every month or so again I was trying to reduce repeated rebuilds of large views etc

In addition it may not always be a straightforward key1-4 relationship and so SQL gives me the greater flexiblity without setting up views and code for each permutation.

Also thought I would make of the traditional DB functions provided with SQL to do the hard work (and possibly more efficiently) rather than manually coding the detail, less prone to coding error!!

Thanks for your comments