Form - subform linked on SUBSTR()

[FJCC]

I have encountered a problem creating a simple form-subform relationship when the link is based on a query field derived with a SUBSTR() function and the DB is not an HSQLDB. I have a base file connected to an Oracle DB via ODBC. My main form has a table control populated with a query like

Code: Select all

SELECT DISTINCT SUBSTR(b.COLUMN_B,1,5) FROM TEST_DATA_VIEW a INNER JOIN  SERIAL_NUM_TABLE b ON a.COLUMN_A = b.COLUMN_A WHERE a.TEST_DATE > '01-NOV-2010'

The subform table control is populated with a query like

Code: Select all

SELECT SUBSTR(b.COLUMN_B,1,5), a.TEST, AVG(a.TEST_VALUE) FROM TEST_DATA_VIEW a INNER JOIN  SERIAL_NUM_TABLE b ON a.COLUMN_A = b.COLUMN_A WHERE a.TEST_DATE > '01-NOV-2010' GROUP BY SUBSTR(b.COLUMN_B,1,5), a.TEST

Each query works fine on its own, so any typos in the above queries are just a mistake in this post and not the cause of my problem.
I want the subform to display the average TEST_VALUE of each TEST for the SUBSTR(b.COLUMN_B,1,5) selected in the main form. What I actually get is the complete data set of the subform query with no filtering effect.
I have tried several things to characterize the problem. Among them are:
1. I changed the query in the main form to display distinct TEST and changed the Master and Slave links to TEST and then the subform works as expected. I get the average TEST_VALUE for each SUBSTR(b.COLUMN_B,1,5) for the TEST selected in the main form.
2. I tried changing the Analyzed SQL Command setting in the form Properties. This does have an effect despite the fact that both queries (run as a standalone query) run fine in interpreted mode or direct SQL. In the forms, at least one of the queries needs to be set to Analyze SQL Command = No.
3. I tried running a similar set of queries in a MySQL DB. The View I use in the Oracle DB isn't available there, so the arrangement was a little different, but I had the same problem.
4. I dumped a subset of the data into an HSQL DB that I made with Base and the forms work as expected.

I think the above shows that I only have a problem when I'm linking the form and subform on a field created with a SUBSTR() on an external (or at least Oracle or MySQL) DB. I am now out of ideas on how to address this. Maybe this "problem" is expected because of something I don't understand - I vast area w.r.t databases. I am new to using forms, so maybe I'm doing something silly. Any suggestions for things to try, explanations of why it will never work, or a confirmation of the problem would be very much appreciated.

[Villeroy]

Alternative approach for the subform query:

Code: Select all

... WHERE b.COLUMN LIKE :param ||'*'

Bind the subform from the mainform's calculated substring field to param. Enter the param name without colon instead of a linked field name.

[FJCC]

Thank you Villeroy! That method works. I did have to change the wild card to % instead of *. It is late here now, so I'll wait until tomorrow to see what other trouble I can get into.

[Villeroy]

Thank you Villeroy! That method works. I did have to change the wild card to % instead of *. It is late here now, so I'll wait until tomorrow to see what other trouble I can get into.

Yes, I'm always confused by the GUI syntax vs. SQL syntax.
One thing: When inserting a new record into the subform the subform inherits the linked field(s) value(s) from the main form. This did not work with parameter links when I tried some weeks ago.

[Arineckaig]

Villeroy wrote:
One thing: When inserting a new record into the subform the subform inherits the linked field(s) value(s) from the main form. This did not work with parameter links when I tried some weeks ago.

I may well be misunderstanding the issue and way off beam, but my limited experience has not had this problem. For example, in the attached database the two "Power Filtering" form documents owe much to your education. Together with other form documents in the file they make use of parameter queries to link data sub-forms with parent data forms. When adding a new record to the lowest sub-form the linked field values are inherited. In short I have found your system most effective in terms of speed and accuracy for pre-loading linked fields when creating a new filtered record.

[Villeroy]

Arineckaig, your excellent demo proves that I lost the clear view over all the bugs and limitations in Base forms.
Thank you for the compliments too.