SOLVED - Join command out of memory error

[dr_nuke]

Hi!

I have two tables with similar information that I want to compare. Normally I would create and populate each table and then do an outerjoin so I can see what data is similar and what is different in the two tables.

Each table has 3 fields. I am joining on Field 1 in both tables and want to show the other 2 fields from each table beside the joined info.

Table 1 has 6214 records x 3 fields. Table 2 has 15,159 records x 3 fields.

I get an "out of memory" error when I try to view a cross join (outer) of table1.field1 to table2.field2. For the most part, the info in table 1 should appear in table 2. I expect only a few hundred exceptions. So probably less than 15,500 rows total.

I've tried a left outer join to just test but this also get an out of memory error. I've run this type of project often in other db products. It isn't very complicated and shouldn't need too much memory.

Why doesn't this work using Base? (I though HSQLDB was pretty robust?) How do I design the query to work with Base?

I understand that I can increase the memory in the preferences but how do I determine what memory size should be used and make sure it is available for this query?

Thanks in advance.

[r4zoli]

You reach the default Java heap size limit, increase it, under tools>Options>Java>Parameters add to Java startup parameters, depending on your memory size, if you have enough RAM: use Xmx512m, and Xms512m, add each value separately.
Use same values for each parameter.
I think the defaults are -Xms32m -Xmx128m.

[dr_nuke]

Thanks.

I don't understand what

[r4zoli]

[Villeroy]

On a Mac all application settings are accessible from a central place instead of Edit>Preferences or Tools>Options within the respective application. Don't know where to find the central place but it should be easy to find.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Sliderule]

dr_nuke:

You said:

[dr_nuke]

Thanks for your help.

Here is the sql for the query.

[Sliderule]

dr_nuke:

You asked:

[dr_nuke]

Thanks for the help and link, Sliderule.

I am trying to do an outer join.

I don't know if all the records in table update_sid are also in cos_sid. So I need to do the outer join. My gut says that there are about 100 records in cos_sid (6500+ records) that don't appear in update_sid (15600+ records). Of course there are many in update_sid that don't appear in cos_sid.

My understanding is that CROSS JOIN is the HSQLDB equivalent of outer join??? Or should I be using a LEFT OUTER/RIGHT OUTER (if these are allowed?) as opposed to LEFT JOIN?

I am attempting to join update_sid.sid_no to cos_sid.sid_no.

[Sliderule]

dr_nuke:

You asked:

[dr_nuke]

Thanks for the additional links.

Unfortunately, I'm still fighting this.

The LEFT JOIN yields all the records in table cos_sid but no additional records from update_sid.

The RIGHT JOIN yields all the records in table update_sid but no additional records from cos_sid.

I can't get the CROSS JOIN to work. I cleaned up the SQL a bit. This is the new error message:

[Sliderule]

dr_nuke:

You said:

[dr_nuke]

I checked each table again and the query.

I'm having a hard time believing this is a typo/spelling error.

I use the exact same query for the LEFT JOIN and RIGHT JOIN and both run OK without any error.

All I did was substitute the CROSS JOIN into the same query and then I get the error.

Error also has this: (SQL Status: S0022
Error code: - 28 ) I can't find a list of error codes.

Any other reason for the error message?

[Sliderule]

Per HSQL documentation found at:

http://www.hsqldb.org/doc/guide/ch09.html#select-section

[dr_nuke]

Hi Sliderule.

Thanks for your continued help. I ran the query: