Can the SELECT SQL statement be used for a table stored in a notes doc?

HCL Domino Domino Forum
1

Hi,

I have a table (HTML) in a document, and I would like to select a cell based on user input, which determines the row and the column.

This is quite a simple task in relational dbs through select SQL statement, but it seems less so in notes.

I read somewhere an explanation that in notes a view corresponds to a SELECT statement and the rows representing documents correspond to records.

Yet in my case the table is within a notes doc.

Is there a simple solution? I can think of writing a super long if and else if statement with boolean ANDs for the row and the column but this is not very elegant. Any better ideas?

Gyorgy

2

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

This table you speak (in the notes document) of is not a relational table like in the relational world, think of it as a table in a word document. Notes views are more like views and tables in the relational db world where you can exectue a select (more like a dynamic view in the relational world) to specify your selection criteria for the view.

To equate the analogy, you have a blog (notes table) stored in a record within a relational database.

What are you trying to accomplish? It sounds like all you are trying to do is a simple database search.

3

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

Hi Andre and Tom,

Thanks for responding. Your questons and explanations help me focus the problem. Basically, I just want to accmplish a simple database search.

Let me describe it here in a boiled down versoin.

I have such tabular data

X A B C

A X Y N

B N X N

C Y N X

I would collect user input for the required row and column through fields in a form, and then would look in the table what is the corresponding value. Based on that I would create a new form.

For example, if I get from the user A (for row) and B (for column), the result would be Y, if B (for row) and C (for column) the result is N. Based on the result, different kind of forms would be actioned.

Most of the app is form processing, only a tiny part requires this table. I think I could solve the problem in notes by putting together if…else if statements, but in reality the table is much bigger and I hope there is a more practical way.

György

4

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

Some things are still unclear. You say you have a table. Is this a reference table which is the same for all documents, or is it associated with the document, so each document could have a different one?

Do you get to choose how the table is stored? Why did you say it’s an HTML table?

Of course it’s possible to write general-purpose code that uses a stored table of data and locates a cell based on a row and column number (or name). Of course it’s possible to write code that does the same with a table stored in a rich text field on the document you’re editing. But exactly what code will do this, depends on the details of what you’re trying to do.

It seems the part you’re having trouble with, is retrieving the value from the table. Let’s concentrate on that – please don’t get distracted into telling us what you’re going to do with the value once you’ve got it.

5

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

Hi Andre,

Thank you for your post! Please see my answers after the dash lines (–).

Some things are still unclear. You say you have a table. Is this a reference table which is the same for all documents, or is it associated with the document, so each document could have a different one?

– This would be a reference table which is the same for all documents

Do you get to choose how the table is stored? Why did you say it’s an HTML table?

– I get to choose how it is stored. At the moment it is in HTML, but I am happy to place it in a rich text field or in any other format if a solution is easier that way

Even the arrangement of the table can change

At the moment it is like this

X A B C

A X Y N

B N X N

C Y N X

But it can be like this as well

A B Y

A C N

B A N

B C N

C A Y

C B N

In this arrangement the first two columns would be checked against user input, and as a result the third column would be the output.

Of course it’s possible to write code that does the same with a table stored in a rich text field on the document you’re editing. But exactly what code will do this, depends on the details of what you’re trying to do. It seems the part you’re having trouble with, is retrieving the value from the table. Let’s concentrate on that – please don’t get distracted into telling us what you’re going to do with the value once you’ve got it.

– Yes, my problem lies with retreiving data from the table. Would I need to create documents for each row, and then build a view for these docs, so I can do a lookup on this view?

For this to work I would need to assign a key to the columns, but is there a simpler way? Like a table lookup in excel?

6

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

Hi Andre,

Meanwhile I have been progressing with a possible solution.

I arranged the table as such

A B Y

A C N

B A N

B C N

C A Y

and I put the rows in a documents, and I built a nice view in Lotus to get this table.

I am exploring how to perform a dblookup now so that the system returns the third row (Y or N based on the two previous columns) . Since the key is composed of two columns, I think I will have to perform some kind of concatenation but I will also try the logcial AND in dblookup maybe it can do so with two columns, since in my case the key is composed of two columns. If dblookup cannot use logical AND for two columns, I think I will have to concatenate.

I will keep you updated, maybe others can benefit form the solution, as I benefitted from your advice. :slight_smile:

7

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

You can’t read from multiple columns of the view with a single @DbLookup. You could do this with LotusScript code, since when you use GetDocumentByKey, you can then use the document’s Columnvalues property to look at all the columns.

You could have the view contain all the values in a single multivalued column. Then you could use the macro language array notation (result[n]) to loop and extract out those elements you want – assuming they are identified by number rather than by name. If by name, and if the names are the same in all cases and in the same order, you could use @Replace. If the names are not always the same for each lookup, you would need a list containng names and values in the view column.

8

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

Hi Andre,

Thanks for the reply, I will play around with the proposed solution to arrive at the solution. I will come back if I would get stuck.

:slight_smile:

9

Subject: RE: Can the SELECT SQL statement be used for a table stored in a notes doc?

I think you need to better describe the situation and what you want to accomplish.

You have some HTML data stored in a single field in Notes? A text field? Rich text?

You’re accessing the Notes data through NotesSQL? Not the most efficient way, BTW. Do you have an alternative?

You expect SQL – or whatever API you end up using – to parse the HTML data for you and figure out what’s in a particular cell? Does SQL contain HTML parsing capability for data stored in the relational databases you’re used to using? Of course not. The designer of the Domino application chose to store the tabular data in a way that’s not compatible with a relational model. There probably was a good reason for this.