0

I have the following query in Base:

    SELECT field_name1, field_name2, .,.,.
FROM table_a
    INNER JOIN table_b
            ON table_a.field_name1 = table_b.field_name1
    INNER JOIN table_c
            ON table_a.field_name2 = table_c.field_name2
    INNER JOIN table_d
            ON table_d.field_name1 = table_b.field_name2
    LEFT JOIN table_e
            ON table_e.field_name1 = table_c.field_name2 AND
               table_e.field_name2 = table_b.field_name1

When I delete a row from table_a the Base parser also deletes the row from table_e. Adding or modifying a row is ok it is only a problem when it comes to deleting a row. Here is the actual code:

FROM "Futures_Orders"
  INNER JOIN "Contract_Details"
         ON "Contract_Details"."Symbol" = "Futures_Orders"."Symbol"
  INNER JOIN "Broker"
         ON "Broker"."Broker" = "Futures_Orders"."Broker"
  INNER JOIN "FX_Rates"
         ON "FX_Rates"."Code" = "Contract_Details"."Currency"
  LEFT JOIN "Broker_Commissions" 
         ON "Broker_Commissions"."Broker" = "Broker"."Broker" AND
              "Broker_Commissions"."Symbol" = "Contract_Details"."Symbol"

In addition to the row from futures_orders a row from Broker_Commissions is deleted.

tohuwawohu
  • 13,268
  • 4
  • 42
  • 61
user1897830
  • 443
  • 1
  • 3
  • 10
  • I suppose it's a matter of defining the relationships; since information about table definition is missing, i can't say for sure in your case. Generally, you have four options on delete: no action, delete cascade (seems to be the case in your example - will delete the referenced entry in foreign table), set null, set default. You may have to change the foreign key constraint to "set null" or "set default". – tohuwawohu Oct 29 '15 at 10:34
  • I changed the foreign key constraints on "Broker_Commissions" from "delete cascade" to "null" but it had no effect. I think it is to do with the left join as the other tables with an inner join are not effected. – user1897830 Oct 29 '15 at 11:51
  • 1
    Could you please add the definition of all tables and foreign keys / constraints and so on? If it's a matter of LEFT vs INNER JOIN, it's a bug, which needs further investigation. Otherwise, the question has to be closed. – tohuwawohu Oct 29 '15 at 12:54
  • How are you deleting the rows? From the query window? Editable queries are relatively buggy and I recommend never working with them in the query window, only in forms where the exact interactions by the user are tightly controlled by form design. I suspect if your relationship constraints are set up correctly and you delete from the table window or by SQL you will not see this. – Lyrl Oct 29 '15 at 19:40
  • I changed the left join to an inner join however the row from "Broker_Commissions" was still deleted. I created a form (List type) from the query but here again the row was deleted. – user1897830 Oct 30 '15 at 13:35
  • 1
    Deleting from the table works fine. However the query is needed in a practical sense. – user1897830 Oct 30 '15 at 14:08
  • I would set up the form document with the mainform displaying the query results as read-only. Have the table be the source for a subform; set up the subform with linked fields to the mainform. Now the user can delete the selected record from the table/subform. Some additional information on form/subform field linking: [Filter/Search with Forms (leveraging SubForms)](https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=42845) – Lyrl Oct 30 '15 at 19:05
  • Something else I have noticed which may shed some light. I am unable to delete a Futures_Order row which does not have a Broker_commissions row. An error message pops up as follows: error deleting the specified records. Invalid argument in JDBC call: parameter index out of range: 1. This occurs when deleting from the query. I am also unable to delete it using a form. Although there is no error message it simply does nothing. This occurs on the list type form and a detail form which displays just 1 row. – user1897830 Oct 31 '15 at 11:56
  • Table Broker_Commissions has 4 indexes. The 1st is a unique autovalue field. The 2nd is also unique comprised of two fields - broker and symbol. The 3rd and forth are foreign fields - Symbol and Broker. I am wondering if these indexes have something to do with what is happening? – user1897830 Oct 31 '15 at 12:21

0 Answers0