View previous topic :: View next topic |
Author |
Message |
PRR Blue Pill
Joined: Apr 14, 2009 Posts: 1
|
Posted: Tue Apr 14, 2009 6:56 pm Post subject: 1. BASE table random access? 2. multiple tables in one rpt? |
|
First time user with lots of DB experience on home and commercial machines DB's looking for replacement for MAC / Borland Reflex DB's. Have been unable to find the answers to the following (may not be looking in the right docs):
1. Can BASE in a report randomly retrieve a record from one table using a key retrieved from a different table in the same DB? Example: Expenses table record contains (among other fields) an expenses code & date. Expense code table records contain expense code and description. I wish to see total expenses for each code but print the report with the expense code desc along with the code. Sort/retrieve expense records and total by expense code and retrieve code desc from Expense code table?? I do not want to link to 2 tables.
2. Can table retrieval variables be feed to a report to be used to retrieve data? Example: (Using the DB from # 1. above) I wish to see all expenses for a certain code and date range and for each execution of the report use different date range and codes without rebuilding the report. Am willing to create a single record table for a control record if that would work. But how to make the report retrieve Expense records using date range and code from the control record / table??? Available examples / doc??
These processes were pretty simple to do in Reflex but I have been unable to figure out if they are possible in BASE. If these 2 processes are not possible I will have to look for a different DB system.
Thanks in advance to anyone who can help / point me to examples or docs or can confirm that these are not possible in BASE.
PRR |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Tue Apr 14, 2009 7:19 pm Post subject: |
|
I am not very familiar with the finer details of setting up Base reports, but I am familiar with how Base's report tool interacts with the database. In general, most reports like to iterate through the records of a single SQL query. In your case, what this means is that if you probably need to do a join (link) between the two tables.
I am not sure why you don't want to link tables, but Base's underlying HSQLDB is a database designed to work like other RDBMS such as MySQL and PostgreSQL so that if you create an index on the joining field (e.g. the expense code) in both tables, the join is rather speedy.
If you are looking for more programmatic access to iterate through records in queries, then I don't think Base is the tool for you and you may want to look at MySQL or PostgreSQL and use a scripting language like PHP or compile language like Java to iterate through one or more query's records.
Patrick |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Tue Apr 14, 2009 8:01 pm Post subject: |
|
pluby wrote: | I am not sure why you don't want to link tables, but Base's underlying HSQLDB is a database designed to work like other RDBMS such as MySQL and PostgreSQL so that if you create an index on the joining field (e.g. the expense code) in both tables, the join is rather speedy. |
OK. I think I now know why you don't want to link tables: Base does not provide any way for you to create indexes in the underlying HSQLDB tables. You can only set a primary key for each table.
The only way around this limitation that I can see is to use a separate RDBMS like MySQL or PostgreSQL and have Base connect to that. Then you can create tables with indexes directly in the RDBMS in order to make the join queries work reasonably fast in Base.
Hope that helps,
Patrick |
|
Back to top |
|
|
Lorinda Captain Mifune
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
|
Posted: Fri Apr 17, 2009 12:46 pm Post subject: |
|
pluby wrote: |
OK. I think I now know why you don't want to link tables: Base does not provide any way for you to create indexes in the underlying HSQLDB tables. You can only set a primary key for each table.
|
There is a way to create what Base (at least) calls indexes on fields that are not primary keys, but maybe that's not the same thing as what pluby refers to here? Or maybe it changed in 3.0?
You should be able to do #1 using a query, although I think you would have to create links (relationships) between the tables, at least in the query design.
My grasp of reports is very rudimentary, and I think we only have one other regular with much Base experience. If more answers aren't forthcoming here, I would recommend that you ask OpenOffice.org Community Forums. (I've given you the link to the Base Reporting subform). There are several volunteers there with far more extensive experience with Base than myself. If you do so, please post a link to the thread you create there in the thread here. That way I can follow the discussion, and it's available for others who might have the same question later.
Lorinda |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Fri Apr 17, 2009 1:15 pm Post subject: |
|
Lorinda wrote: | There is a way to create what Base (at least) calls indexes on fields that are not primary keys, but maybe that's not the same thing as what pluby refers to here? Or maybe it changed in 3.0? |
When I was talking about indexes, I was referring to marking a column (or set of columns) in a table and then the database keeps a list of the order of the rows in the table based on the selected column. This list is the index and the index is used to make joining tables very fast as when the database is looking for records in table B where column 5 equals some value, it can find the rows that match in the index instead of having to iterate through each row in table B.
Is something like that what you found a way to create or are we talking about different things?
Patrick |
|
Back to top |
|
|
Lorinda Captain Mifune
Joined: Jun 20, 2006 Posts: 2051 Location: Midwest, USA
|
Posted: Fri Apr 17, 2009 2:21 pm Post subject: |
|
I think so, although I'm a little out of my depth. The instructions for the indexes I know about can be found here.
Base makes indexes for primary and foreign keys automatically.
Lorinda |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Fri Apr 17, 2009 4:42 pm Post subject: |
|
Lorinda wrote: | I think so, although I'm a little out of my depth. The instructions for the indexes I know about can be found here |
Thanks Lorinda. That is exactly what I was looking for and could not find when I was trying to create indexes for fast table joins.
Patrick |
|
Back to top |
|
|
|