[libreoffice-users] Re: Base/Firebird Queries broken
Le 16/01/2019 à 00:19, Paul D. Mirowsky a écrit : > Invalid usage of boolean expression This bug perhaps ? http://tracker.firebirdsql.org/browse/CORE-5367?page=com.atlassian.jira.plugin.ext.subversion%3Asubversion-commits-tabpanel Not sure which version of Firebird we are using in LO - 3.0.1 or 3.0.2 ? Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Re: Base/Firebird Queries broken
I have replaced blanks with underscores in field names in tables. I have removed alias named fields from queries. It did not appear to make a difference. I should note that tables Amps, Volts-High and Volts-In have the same or more field names. As each table name is prefixed to a field, I would not think this a possible error. Am I correct in this assumption? Thanks Paul On 1/15/2019 11:27 AM, Alexander Thurgood wrote: Le 15/01/2019 à 16:14, Paul D. Mirowsky a écrit : Hi Paul, SELECT "Equipment"."ID", "Equipment"."P_Model", "Equipment"."P_Model_No", "Equipment"."Power Meter", Check that the space in the field "Power Meter" isn't causing an issue. There was a known bug about spaces being misinterpreted by the parser. "Equipment"."Series", "Equipment"."Power Meter 2", "Equipment"."Power_No 2", "Equipment"."Power Meter 3", "Equipment"."Power_No 3", Same here for "Power Meter 2" and "Power Meter 3" "Ions-IM"."Ions", "Ions-IM"."Select", "Ions-IM"."Series", Also check that the field name "Select" in Ions-IM.Select isn't being misinterpeted. "Volts-High"."Time", "Volts-High"."Reading" "Vo-Hi-Reading", What is "Vo-Hi-Reading" - is this supposed to be an ALIAS ? Maybe check whether that is correctly supported. "Volts-High"."Type" "Vo-Hi-Type", "Volts-High"."Series", Same here with "Vo-Hi-Type" "Volts-High"."Setting", "Amps"."Reading" "Am-Reading", "Amps"."Type" Same here with "Am-Reading" "Am-Type", "Amps"."Series", "Amps"."Setting" FROM "Ions-IM", "Equipment", "Volts-High", "Amps" WHERE "Ions-IM"."Series" = "Equipment"."Series" AND "Volts-High"."Series" = "Ions-IM"."Series" AND "Volts-High"."Date" = "Ions-IM"."Date" AND "Volts-High"."Time" = "Ions-IM"."Time" AND "Amps"."Series" = "Volts-High"."Series" AND "Amps"."Date" = "Volts-High"."Date" AND "Amps"."Time" = "Volts-High"."Time" AND "Equipment"."Series" = 134 AND "Ions-IM"."Select" = TRUE ORDER BY "Ions-IM"."Date" ASC, "Ions-IM"."Time" ASC, "Ions-IM"."Ions" ASC Not sure about how many left join conditions you can currently have. That would probably need checking by removing them one-by-one until you get something that works. As Robert has mentioned, perhaps the migration didn't convert the data types correctly, so your comparisons in the join conditions are no longer working. Good luck ! Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Base/Firebird Queries broken
I am receiving the following error when opening a query. firebird_sdbc error: *Dynamic SQL Error *SQL error code = -104 *Invalid usage of boolean expression caused by 'isc_dsql_prepare' The table names are: Amps Volts-High The two tables involved both have one boolean field each, so I removed them from the query. The error still occurs. I am taking this to mean that there is a boolean comparison happening that is not in the SQL generated by Base. Is this possible? Thanks Paul -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Re: Base/Firebird Queries broken
See below... On 1/15/2019 10:37 AM, Robert Großkopf wrote: Hi Paul, Have a look at "Ions-IM"."Select" = TRUE Did the migrationtool set the field "Select" to a BOOLEAN-field? Yes "Equipment"."Series" = 134 Did the migrationtool import the field as a INTEGER field? Not quite. Series is set to Number [Numeric] There is no function in the query, only 4 tables connected ... The only possibility to find the reason why Firebird couldn't execute the query at this moment is to delete some parts of the query and try if it will work without this parts. By the way: There are only tables connected in theis query, not a view connected with tables? Not sure what you mean. These queries are developed to be linked to a Writer document as test data documentation. It is used to check the quantity of readings per unit at two each. Incorrect quantities of records prove data missing or inaccurate on import to Base. Regards Robert Thanks Paul -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Re: Base/Firebird Queries broken
Le 15/01/2019 à 16:14, Paul D. Mirowsky a écrit : Hi Paul, > SELECT "Equipment"."ID", "Equipment"."P_Model", > "Equipment"."P_Model_No", "Equipment"."Power Meter", Check that the space in the field "Power Meter" isn't causing an issue. There was a known bug about spaces being misinterpreted by the parser. > "Equipment"."Series", "Equipment"."Power Meter 2", "Equipment"."Power_No > 2", "Equipment"."Power Meter 3", "Equipment"."Power_No 3", Same here for "Power Meter 2" and "Power Meter 3" > "Ions-IM"."Ions", "Ions-IM"."Select", "Ions-IM"."Series", Also check that the field name "Select" in Ions-IM.Select isn't being misinterpeted. > "Volts-High"."Time", "Volts-High"."Reading" "Vo-Hi-Reading", What is "Vo-Hi-Reading" - is this supposed to be an ALIAS ? Maybe check whether that is correctly supported. > "Volts-High"."Type" "Vo-Hi-Type", "Volts-High"."Series", Same here with "Vo-Hi-Type" > "Volts-High"."Setting", "Amps"."Reading" "Am-Reading", "Amps"."Type" Same here with "Am-Reading" > "Am-Type", "Amps"."Series", "Amps"."Setting" FROM "Ions-IM", > "Equipment", "Volts-High", "Amps" WHERE "Ions-IM"."Series" = > "Equipment"."Series" AND "Volts-High"."Series" = "Ions-IM"."Series" AND > "Volts-High"."Date" = "Ions-IM"."Date" AND "Volts-High"."Time" = > "Ions-IM"."Time" AND "Amps"."Series" = "Volts-High"."Series" AND > "Amps"."Date" = "Volts-High"."Date" AND "Amps"."Time" = > "Volts-High"."Time" AND "Equipment"."Series" = 134 AND > "Ions-IM"."Select" = TRUE ORDER BY "Ions-IM"."Date" ASC, > "Ions-IM"."Time" ASC, "Ions-IM"."Ions" ASC Not sure about how many left join conditions you can currently have. That would probably need checking by removing them one-by-one until you get something that works. As Robert has mentioned, perhaps the migration didn't convert the data types correctly, so your comparisons in the join conditions are no longer working. Good luck ! Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Re: Base/Firebird Queries broken
Hi Paul, Have a look at > "Ions-IM"."Select" = TRUE Did the migrationtool set the field "Select" to a BOOLEAN-field? > "Equipment"."Series" = 134 Did the migrationtool import the field as a INTEGER field? There is no function in the query, only 4 tables connected ... The only possibility to find the reason why Firebird couldn't execute the query at this moment is to delete some parts of the query and try if it will work without this parts. By the way: There are only tables connected in theis query, not a view connected with tables? Regards Robert -- Homepage: http://robert.familiegrosskopf.de LibreOffice Community: http://robert.familiegrosskopf.de/map_3 -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Re: Base/Firebird Queries broken
Here is the query... SELECT "Equipment"."ID", "Equipment"."P_Model", "Equipment"."P_Model_No", "Equipment"."Power Meter", "Equipment"."Power_No", "Equipment"."Ion Meter", "Equipment"."Ion_No", "Equipment"."Series", "Equipment"."Power Meter 2", "Equipment"."Power_No 2", "Equipment"."Power Meter 3", "Equipment"."Power_No 3", "Equipment"."Vendor_PO_No", "Ions-IM"."Model", "Ions-IM"."Tube", "Ions-IM"."Position", "Ions-IM"."Date", "Ions-IM"."Time", "Ions-IM"."Ions", "Ions-IM"."Select", "Ions-IM"."Series", "Volts-High"."Time", "Volts-High"."Reading" "Vo-Hi-Reading", "Volts-High"."Type" "Vo-Hi-Type", "Volts-High"."Series", "Volts-High"."Setting", "Amps"."Reading" "Am-Reading", "Amps"."Type" "Am-Type", "Amps"."Series", "Amps"."Setting" FROM "Ions-IM", "Equipment", "Volts-High", "Amps" WHERE "Ions-IM"."Series" = "Equipment"."Series" AND "Volts-High"."Series" = "Ions-IM"."Series" AND "Volts-High"."Date" = "Ions-IM"."Date" AND "Volts-High"."Time" = "Ions-IM"."Time" AND "Amps"."Series" = "Volts-High"."Series" AND "Amps"."Date" = "Volts-High"."Date" AND "Amps"."Time" = "Volts-High"."Time" AND "Equipment"."Series" = 134 AND "Ions-IM"."Select" = TRUE ORDER BY "Ions-IM"."Date" ASC, "Ions-IM"."Time" ASC, "Ions-IM"."Ions" ASC I have several others that continue to work correctly. I am going to try to see if the failure applies to a particular table(s). Thanks Paul On 1/15/2019 3:06 AM, Alexander Thurgood wrote: Le 15/01/2019 à 00:23, Paul D. Mirowsky a écrit : Any hint as to where I'm going wrong. Without the query definition, it is going to be like gazing into a crystal ball...all sorts of things might not work in the same way, the time values, the date values (especially if there are calculations), subselects, unions, joins, etc... Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Re: Base/Firebird Queries broken
Le 15/01/2019 à 00:23, Paul D. Mirowsky a écrit : > Any hint as to where I'm going wrong. Without the query definition, it is going to be like gazing into a crystal ball...all sorts of things might not work in the same way, the time values, the date values (especially if there are calculations), subselects, unions, joins, etc... Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy