Query in a form with multiple joins to one table through aliases is not working correctly

agerber85 · February 14, 2022, 3:00pm

I’m trying to set up a query form in Base that should have 2 joins to the same table as described in This thread.
I tested the SQL in a Query in the .odb file, and it works. If I open the query, the names from the joined personnel table show up correctly. However, in my query form I set the data source to “Query” and select that query, those two fields are blank for all rows. Seems like a bug to me. Why would it work as an independent Query and not as a query form in a form?
Addendum:
Version: 7.0.6.2 (x64)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Addendum2 (The SQL of the Query):

SELECT `w`.`id`, `e`.`login` `order_received_by`, `r`.`login` `order_reviewed_by`, `w`.`DATERECEIVED`, `w`.`REVDATERECEIVED`, `w`.`CUSTOMERPO`, `w`.`REVCUSTOMEREPO`, `w`.`CONTRACT`, `w`.`REVCONTRACT`, `w`.`QUANTITY`, `w`.`REVQUANTITY`, `w`.`PARTNO`, `w`.`SPECIALINSTRUCTIONS`, `w`.`REVSPECIALINST`, `w`.`NOTES`, `w`.`CUSTOMER_OVERRIDE`, `w`.`REVCUSTOMER_OVERRIDE`, `w`.`SHIPPINGADDRESS_OVERRIDE`, `w`.`REVSHIPPINGADDRESS_OVERRIDE`, `w`.`SHIPPINGADDRESS2_OVERRIDE`, `w`.`REVSHIPPINGADDRESS2_OVERRIDE` FROM `metrology`.`workorders` `w`, `metrology`.`personnel` `e`, `metrology`.`personnel` `r` WHERE `w`.`orderrecby` = `e`.`id` AND `w`.`reviewedby` = `r`.`id`

agerber85 · February 14, 2022, 4:03pm

Some additional info: I tried running the SQL for the query through Tools>SQL…, and the Status said “1: Command successfully executed,” but the Output was empty.

agerber85 · February 14, 2022, 4:14pm

Also, I pared down the fields in the query to just the ones using the join command, and those fields are still showing blank

RobertG · February 14, 2022, 4:15pm

Did you use the Query in Design view?

You are writing about only two blank fields. Which fields are this and which field types are this.

Note:
You could send unmasked queries in the editor by direct SQL. You could do this also through Tools → SQL by choosing “Execute directly” (Is a new function, could be it doesn’t exist in LO 7.0.6.2)

You could choose in forms the possibility to “Analyze SQL-code → no”

agerber85 · February 14, 2022, 5:41pm

Yes, I did. Unfortunately, I did not see your comment until I had already realized that’s what caused the problem.

agerber85 · February 14, 2022, 5:42pm

You’re right. I’m not seeing it here. Fortunately, I have other means of directly executing SQL that I use for troubleshooting.

agerber85 · February 14, 2022, 4:55pm

Aha! I was able to make it work right. Somewhere along the lines, I edited the query and switched between Design Mode and SQL Mode, and Base was kind enough to replace my join statements with where statements. I got them put back, and now the form is doing what I want.

That’s one of two very irritating things Base does to queries when switching between those two modes, the other being the addition of a bunch of unnecessary quote marks that make the statement more difficult to read.