1

I'm using LibreOffice Base 5.3.4.2 as a front-end for a postgres database. In postgres, I have the following tables:

lu_ss_unit_types (lookup table that stores different unit types)

ss_unit_mix (uses fkey from lu_ss_unit_types)

ss_rent_surveys (uses fkey from ss_unit_mix)

In LO, I have a subform for ss_rent_surveys under a form for ss_unit_mix. Within the ss_rent_surveys subform table control there is a listbox column for ss_unit_mix_id which I've labeled "Unit Type" and concatenates information from the ss_unit_mix and ss_unit_types tables.

Here's the problem: the following code in the List Content for the listbox control I get the error "missing-FROM clause entry for table 'm'." However, when I run the SQL straight from PGAdmin, it works fine. What gives?

SELECT t.unit_type||' ('||
CASE WHEN m.interior IS TRUE THEN 'Int' Else 'Ext' END||'/'||
CASE WHEN m.upper_level IS TRUE THEN 'Upper' Else '1st' END||'/'||
CASE WHEN m.climate_controlled IS TRUE THEN 'CC' Else 'Standard' END||')', 
m.ss_unit_mix_id 
FROM properties.ss_unit_mix m
LEFT JOIN lookup.lu_ss_unit_types t ON t.ss_unit_type_id = m.ss_unit_type_id;
ZiGaelle
  • 744
  • 1
  • 9
  • 21
NDogg
  • 11
  • 4
  • It seemed to work OK when I tried running it from the Queries section in LO Base. Is the `properties` schema owned by the same user that the LO Base file uses to connect? Perhaps it's a permissions problem, or LO Base is starting from a different schema. Try simplifying the query to see if it works. – Jim K Jun 27 '18 at 07:57
  • @Jim I solved the problem. Apparently I needed to add a space between `m` and `LEFT JOIN` in the List Content box of the listbox control. How do I mark this question as solved? As an aside, I was also having a problem that the SQL in the List Content field was being partially deleted when the form was re-opened after saving. The fix was to create a saved query in LO Base and then point the listbox to the query rather than trying to type the SQL directly into the List Content field. – NDogg Jun 27 '18 at 19:26
  • Glad to hear you figured it out. To close the question, add your own answer and then accept it. You may need to wait a few days first. https://stackoverflow.blog/2009/01/06/accept-your-own-answers/ – Jim K Jun 28 '18 at 09:07

0 Answers0