Well, I should maybe do this in the issue tracker, but for now the list will do.
I went ahead and moved your table and the queries into a Base file connected to a MySQL server using jdbc. The behavior is similar but not identical. First - in advanced options settings I insured that 'append table name alias on select statement' was checked. ( so much for my doubting it is now an optional setting ) [ I'm still at a loss for how to do this with an embedded database? ] Second - with table aliases on you can now run the inner join query from the designer view. The data is, however, still wrong, you get exactly the same thing as in the embedded database in SQL view. Third - turning the 'table name aliases' off and attempting to run the inner join query generates an error - "The data content could not be loaded. Unknown table 'mitarbieter'" Again this is the same as in the embedded database. Fourth - attempting to open the cross join query in designer mode still crashes the application - This was tested on XP for both 2.2 stable, and 2.3 m_207 Final - with 'table aliases' turned on again I recreated the inner join query by adding the personen table a new query designer twice. Then creating the inner join by dragging Personen.hatChef -> Personen_1.ID I then selected the fields Personen.ID, Personen.Name, Personen_1.Name I add NO column aliases. Here things get interesting again. The Select statement that is created is: SELECT `personen`.`ID`, `personen`.`Name`, `personen_1`.`Name` FROM `invoices_w_deliveries`.`personen` AS `personen_1`, `invoices_w_deliveries`.`personen` AS `personen` WHERE ( `personen_1`.`ID` = `personen`.`hatChef` ) Run this in the designer view, or SQL view with escape prcocessing and I get this result set ID | Name | Name1 1 | Müller | Kaiser 2 | Schmidt | Kaiser 3 | Klein | Kaiser 4 | Groß | Kaiser 5 | Schulte | Kaiser 6 | Becker | Kaiser Alright - switched to SQL view and escape processing off and get ID | Name | Name1 1 | Müller | Kaiser 2 | Schmidt | Kaiser 3 | Klein | Müller 4 | Groß | Schmidt 5 | Schulte | Müller 6 | Becker | Schmidt Here is the interesting part.. Remove all the selected columns in the designer view Remove the join in the designer window and recreate it, this time dragging Personen.ID -> Personen_1.hatChef. Select the fields Personen_1.ID, Personen_1.Name, Personen.Name Now in designer mode when I run the query I get what we have been after. ID | Name | Name1 1 | Müller | Kaiser 2 | Schmidt | Kaiser 3 | Klein | Müller 4 | Groß | Schmidt 5 | Schulte | Müller 6 | Becker | Schmidt 7 | Smith | Schmidt Drew ps - notice there is a seventh entry. At one point I changed that inner join to a left outer join and added the Personen.hatChef field..voila...an updateable query resultset. I entered the new data directly into the query data view window and yes it was posted to the database without an error...but before we got out the party favors...there where other issues with that result set...I'll look more and get back on those. Drew On 4/15/07, Regina Henschel <[EMAIL PROTECTED]> wrote:
Hello, Andrew Jensen schrieb: > Just to follow up for the list readers. It`s nice you do that. > > In my first test I had set both of the SQL view queries to 'Run SQL > Direct' = TRUE, or in other words turning escape processing off. This > allowed either SELECT statement to run correctly. The problem is only > manifest when escape processing is on. > > Also, when escape processing is on and the inner join query is run not > only is the resultset incorrect, but Base makes the returned resultset > updateable. Of course an attempt to actually edit the records fails. > > I was wondering if this might have something to do with the change > made to table aliases being or not being created. I believe it was > turned off by default now, and that it can be re-instated with an > option. I just have no clue how to set that option for an embedded > database connection. ( besides, I can't quote where I read that so > perhaps I am mistaken that it is an option now, and perhaps it has > nothing at all to do with this problem ) > I have wrote issue 76377 now. kind regards Regina --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
