Apologies - this is not an easy problem to describe. Happy to elaborate & add code if necessary.
I am importing data from an Oracle database. Three Oracle tables are defined in the Dct and have long been used in Clarion joined views. Now, a new spec requires a join using some similar aggregate subqueries.
My first thought was to add a new SubQuery table in the Dct and use PROP:Name to define the select statements for the subqueries as needed in different procedures. This would be flexible and make the best use of what is already defined and working elsewhere.
Unfortunately, when the Clarion view is opened, I get an Oracle 37000 error (dynamic SQL syntax error or access rule violation). However, from a trace file, I can copy the generated query into my Oracle SQL Manager and the query will run without error IF the following bracket text for outer join is removed â{oj ⌠}â.
Obviously, the ODBC driver adds this text for other left outer joins, but I am wondering why it is a problem here or even whether it is necessary?
Using Clarion ODBC. What is interesting about the {oj } formatting is that a Clarion view INNER join does not have something similar and in fact does not use the word JOIN. Instead, the tables are listed and the link fields appear in the WHERE clause. More difficult to read & understand when several other conditions are included.
I agree with Sean, in that the oj{} stuff should get interpreted by the ODBC layer. I think I would go a step further and say that if the oj{} stuff is actually reaching Oracle, then the ODBC layer has not done its job. I canât help much since I almost exclusively use the Oracle driver. The Clarion ODBC driver will be talking to an ODBC layer on your machine. Maybe you are using the Oracle supplied one. There is also a Microsoft one. Maybe using a different ODBC layer will help. The few times I have used ODBC I have found the Microsoft version less problematic than the Oracle one. Itâs ancient, but maybe thatâs what Clarion likes about it!
The Clarion ODBC driver is set up to deal with a wide range different servers of different vintages. For that reason it avoids ANSI joins because it doesnât know that all backends can support them. I have a vague memory that the ODBC driver has trouble with more than one outer join in a query.
Let us assume that happens. Interesting however, is that when using PROP:Sql, the {oj } stuff (now deemed the proper technical term) is not included in a trace string of the query. The jury is still out on whether this will be the difference in getting the subquery to run.
I spent a few minutes looking at this, and learned nothing about the OJ{} business yet.
What I did learn is that, faced with two outer joins in a view, clarion will do a client-side join. I had a browse like this:
Jobs
->> activities
->> tools
where jobs to activities was an outer join and activities to tools was too (and there were lookups to get names from all three levels). Clarion queried each of the tables involved separately.
I also learned that, although on the database side, having tools link back to activities only requires that there be a primary key on activity_id, in Clarion the template will give you a warning message and not add tools to the view unless you have a have a key on tools, like (activity_id,tool_id). I guess that makes sense from an ISAM point of view â you would want to start at the top, take your activity_ID from the middle layer and then use an index on tools that gave you all the tools associated to the activity. Not really necessary in SQL, though â the key on activities is required so that you can check that you are adding tools to existing activities, but a key on tools with activity_id as the first component is not required.
Once I had a view that Clarion decided Oracle could handle, both Clarionâs trace and the ODBC trace show the OJ{} syntax. Clarionâs looks like this (reformatted):
SELECT A.âCLIENT_IDâ, A.âHEADER_IDâ, A.MEANING, B.âHEADER_IDâ, B.âACTIVITY_IDâ, B.DT, C.âACTIVITY_IDâ, C.âACTIVITY_NAMEâ
FROM {oj TESTJON.HEADER A
LEFT OUTER JOIN TESTJON.ACTIVITIES B ON A.âHEADER_IDâ= B.âHEADER_IDâ } , TESTJON.ACTIVITY C
WHERE B.âACTIVITY_IDâ= C.âACTIVITY_IDâ
ORDER BY A.âHEADER_IDâ ;
I couldnât see what actually reached the server because I donât have access to the location where the log file would end up from my work computer.
Itâs all a bit weird syntax-wise: the outer join gets ANSI syntax, but the regular joins are done with the old-style comma separation and join condition in the WHERE clause.
Clarion also seems a little unsure about what to do if you check the Inner Join box; when it did the client side join it did an outer join at the top level even though it was specified as an inner join.
I have to say that the decision I made several years ago to set up views on the server and then import them as clarion tables rather than let clarion try to create views is not filling me with regret.
You may also need /NESTING = TRUE
Otherwise there is actually an OS ODBC driver limit on the number of joins. I think itâs a limitation of the spec, or at least one or more versions of the spec.
and I think when all I had was the three main tables, and specified the two joins as inner joins, it was still doing the client-side matching. I was using the Microsoft ODBC for Oracle circa 2019, and Oracle 11. It seems unlikely it doesnât support nesting, but who knows?
With a little bit of further testing, which possibly gets back to the original problem:
If I specify ODBC 3.0 joins and nesting = true, and leave the tools<<-tool lookup as an outer join then clarion produces the following statement:
SELECT
FROM {oj TESTJON.HEADER A
LEFT OUTER JOIN TESTJON.ACTIVITIES B ON A.âHEADER_IDâ= B.âHEADER_IDâ }
, TESTJON.ACTIVITY C
, {oj TESTJON.TOOLS D
LEFT OUTER JOIN TESTJON.TOOL E ON D.âTOOL_IDâ= E.âTOOL_IDâ }
WHERE B.âACTIVITY_IDâ= C.âACTIVITY_IDâ AND B.âACTIVITY_IDâ= D.âACTIVITY_IDâ ORDER BY A.âHEADER_IDâ
That statement fails with an Oracle 903: invalid table name error. I suspect what is happening there is that the ODBC layer only converts the first {oj } it comes across, and then the second one gets passed to Oracle, which Oracle then tries to translate to a table name of {oj with an alias of testjon.tools.
If I turn the tools-tool relationship into an inner join the query works, but doesnât give the right result because the WHERE clause specifies tools.tool_id = tool.tool_id and that wonât be true for any activity that uses no tools, so no-tool activities donât show on the browse.
If you google ODBC multiple outer joins you will see that the problem is not restricted to Clarion, although in a quick look there seemed to be only problems and no viable solutions.
While that Oracle error number has not come up in my testing, invalid table name error certainly has.
Using PROP:Sql so as to avoid the {oj } stuff, I now have my query working - parent with aggregate subquery join and two left joins. However, some unexpected steps were needed and several questions remain. For startersâŚ
(Using an ABC Browse or Process template)
a) Does field order in a Select statement need to match field order in the declared Clarion view?
b) When should PROP:Sql be set?
c) MyView{PROP:Sql} can be compiled but gives a table error when run.
Instead MyTable{PROP:Sql} must be set.
How does this mix when the Clarion view includes more than a single table?
a) Yes. One of the things the driver normally manages is matching up the columns on the server with the columns declared in Clarion, both in tables and in views. When you cut the driver out using PROP:SQL you become responsible for that matching.
b) at the point where SET(View) or reset(view) would normally happen
c) Sort of related to a). When you set up a view in clarion and do a set/next, the values in the view are plonked into the correct columns in the buffers of all the tables involved in the view. When you do a prop:SQL, the results are going to go in the buffer of the file that is the host of the prop:SQL in the order in which the columns are specified in the prop:sql query. SoâŚyou have to declare a new, probably turboSQL, table as a temporary buffer, and then you would have to copy from there to the places in the normal file buffers you want them to be (if necessary).
My assumption also, however, does not seem to be true from the test I just ran.
The ABC Process template has an odd difference from ABC Browse. In a browse, hot fields generate code into the Clarion view in the order they appear in the template. In a process, the hot fields are (mistakenly?) alphabetically generated in the Clarion view. Not realizing this, I of course wrote my PROP:Sql select to match the order of hot fields in the template. Nevertheless, even though the view and select fields did NOT have matching orders, the data from listed hot fields does appear in the table buffer as I expect.
Still trying to figure this one out.
I agree, Iâve done the same with MS SQL server views, and even stored procedures in MS SQL server for performance gains. Iâd even got so far as having clarion creating tables, views & stored procedures from the app.
Spent a lot of time attempting to get PROP:Sql to work in a simple ABC Process procedure. My assumption that View{PROP:Sql} just prior to the first NEXT could be substituted for the template/view generated select proved not to be true when joined tables are involved. One problem for instance, trace shows confusion with additional select statements related to the view beyond that sent by PROP:Sql even with /TURBOSQL.
Unfortunately, this afternoon, I had to give up and move on to another solution for the aggregate subquery data.
