Unable to use Views and Joins

[shearerg]

Hi again,

You can tell I'm really just getting into DAI ... as I've only just started investigating the use of dynamic joins and dynamic SQL views ... and unfortunately I'm having problems with both.

Symptom in both cases is that all goes fine during the design phase, but the ODBC driver returns errors when any other use is attempted. It isn't yet clear to me whether my problems lie with DAI or the Ingres driver ... although I suspect a bit of both ... as symptoms differ with target database. Anyway, I'll restrict the problem outline here to just the latest database release.

Dynamic Join
Symptom is that all goes fine during the design phase, but the ODBC driver returns an error when any other use is attempted. The first attachment (1 - Join design) shows a join description and data retrieved when it is executed. The second image (2 - Join error) shows the error returned when a browse is attempted using the join. On the face of it this looks like a deficiency with the database ... but in that case I'm puzzled at why the designed join executes OK?

1 - Join design.jpg (94.99 KiB) Viewed 19376 times

2 - Join error.jpg (28.01 KiB) Viewed 19376 times

Dynamic SQL view
The problem here appears to be with the database server ODBC interface, as when targeting the latest release, a dynamic SQL view works OK in both design and general usage. I'll investigate further before requesting help on this issue.

This means that, at least on any late release databases (unfortunately only 1 out of 4 ), I can use a view in place of a join ... but the others may have to be left with very basic designs until I can get them upgraded ... which could take some time!! Not very happy ...

So the main question from above is should a dynamic join behave identically when in design mode and general use ... or is there a subtle difference. If there is a difference ... then I expect it has hit upon a database deficiency ... otherwise there could be a problem in the DAI coding.

Any advice would be appreciated.

Regards,
Greg Shearer

[Carsten Sørensen]

Hi Greg,

If you make a show sql on the working join-builder definition and copy that into a Dynamic AI SQL View - then I assume you will be able to execute it.

E.g.: join-builder would generate:

SELECT *
FROM (Transactions DAI_
LEFT JOIN ChartOfAccounts J2 ON DAI_.Account = J2.AccNumber)

(in my case I will then get column naming conflicts so I modify the select to:

SELECT DAI_.Company, J2.AccNumber
FROM (Transactions DAI_
LEFT JOIN ChartOfAccounts J2 ON DAI_.Account = J2.AccNumber)

That would be equal to the join you see executed in the join-builder.

In a Dynamic AI SQL view please then try to surround it in:

select * from (

SELECT DAI_.Company, J2.AccNumber
FROM (Transactions DAI_
LEFT JOIN ChartOfAccounts J2 ON DAI_.Account = J2.AccNumber)

) XX

to see whether the Ingres driver supports the use of sub-selects.

That would be important and could be a limitation of the ODBC driver - maybe.

Another possibility is that the prefix table names that you are using is causing some problems in this particular use of it. Can you eventually try (don't know whether that's possible with Ingres) to set-up the connection with an user/dbo/owner/catalog or whatever that might be called to get clean database object names?

Best regards,
Carsten

[shearerg]

Hi again Carsten,

I was about to update my post with some additional information when I saw your reply.

New information is that the query also works fine through Excel, which uses the same ODBC driver as DAI.

I've also tried your suggestions and all tests were ok. That is:
1. The sql copied from the join builder definition works fine within Dynamic SQL.
2. Select * from ( subselect ) XX also works fine within Dynamic SQL.

In fact, the only thing that doesn't work is when I try to browse or use the join in a report. All of the preceding steps behave as expected.

Greg

[Bob Cergol]

Hi Greg,

PMJI...

It sounds like only Dynamic Joins are giving you a problem and that you can run the same SQL script as a Dynamic view just fine.

I found dynamic joins to be a "down-and-dirty" short-cut -- very useful -- and very instructive for me -- but I've based the vast majority of my reports on Dynamic SQL views -- where I can control the SQL, clean up bad legacy naming, create specialized calculated columns (as an alternative to DAI's global vars) and pick just the sub-set of columns I know will be frequently used in most reports. Also, in cases where you join a lot of tables each with lots of columns, the J2.fieldname, J3.fieldname, etc can be hard to look at. (I had a join where 500 fields were showing up in report design -- many with duplicate names like J2.Idno, J3.Idno, etc.!)

I use dynamic joins, but I have to say I prefer creating my own dynamic views.

Maybe this is a workaround for you. Don't let temporary problems with dyn.joins on Ingres slow you down!

Regards,
Bob

[Carsten Sørensen]

Hi Greg,

Working on the Ingres sample db I can create joins and also browse them / use in reports.

WMXPDB.jpg (186.15 KiB) Viewed 19368 times

and

WMXPDB-1.jpg (98.95 KiB) Viewed 19368 times

Can't you do the same? where do you get the problem?

Best regards,
Carsten

[shearerg]

Hi Bob and Carsten,

Bob,
I understand your preference for VIEWS, and I'm also happy with that approach, particularly if there are no other real advantages with using JOINS. And you're correct that VIEWS are working ok for me ... but only against my latest database release . I have other related databases with earlier Ingres releases against which VIEWS also fail. For this reason I'm also asking questions on the Ingres driver forum.

Unfortunately from long experience, I know how subtle some software problems can be. In the case of VIEWS against my earlier database releases, they behave similarly to the JOIN problem outlined in this post in relation to the latest release. That is, all behave fine in design, and also when the sql is tested through Excel using the same ODBC data source. However, when I try to use them in a browse or report, only the most recent server responds correctly. The others result in an error.

I'm hoping you can see why I might be confused ... as only the very last step ever fails . Until I try to use a JOIN or VIEW in a report or browse, everything looks fine ... but the only working option I have right now is the use of VIEWS against my most recent database release In the case of my earlier releases I can only use reports based on single tables or actual database views.

Carsten,
Your reply is a real surprise, as I haven't had any success using a JOIN in a browse or report. As mentioned above, JOINS against all of my Ingres releases appear to work fine through the design phase ... but all fail when nominated for a browse or report. The error is shown in an attachment to the first post, but in essence is 'ODBC driver does not support the requested properties.'.

I've tested that in all cases the generated sql is ok, so it looks like the problem lies in associated packaging of the sql and the interface with the Ingres ODBC driver and/or server?

However .... the fact that all works ok for you would definitely seem to indicate an issue with the Ingres server version ... so I'm really confused. Can you tell me what Ingres release you're using? Also, the Ingres ODBC driver version from Control Panel->Administrative Tools->Data Sources->Drivers. I assume you're using a PC server installation.

Right now I'm actually using an Ingres 10.0 ODBC driver (3.50.1000.120), although nothing changes with the 9.3 driver. My servers are on Linux/SCO machines and are all running Ingres 9.3 servers and earlier. I would expect that you have an Ingres 10.0 server, which may turn out to be the key. This may be bad news for me though ... as the chance of upgrading my database servers in the short term isn't very great. That's why I was sort of hoping that the problem could be resolved at the DAI end. I won't give up hope yet!

Does the that fact that the JOIN sql always runs ok but the JOINS fail when used seem odd to you? I'll try an example similar to yours on my PC server, as I hadn't thought to do that yet. I expect it will work ok, but will let you know shortly. I'll also post an internal Ingres error log of a failed JOIN usage on the Ingres forum. It may provide some useful guidance.

Thanks to both of you for your interest in this issue.

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

It sounds to me like the older Ingres database/drivers are not supporting the use of SUBSQL (which is the way the Dynamic AI SQL Views are implemented).

Like:

Select * FROM
(select a,b,c from abc table) XX

Maybe you can test this straight from a Dynamic AI SQL View source / Execute. If it works in the latest release but not across your older Ingres databases - then I think that might be a limitation that we can't do anything about from within Dynamic AI.

The strange thing is that the joinbuilder actually do not use SUBSQL - that was one of the reasons for creating it originally....

I'm on PC Ingres ODBC driver 3.50.1000.120 dated 30-6-2010 (using cursors and no other options selected on the Advanced tab) - I'm running on Ingres 10.0.0

Best regards,
Carsten

[shearerg]

Hi Carsten,

Are you up particularly late?
Anyway, you are right in that the earlier releases don't support the SUBSQL structure you've outlined. Which is a real pity, as the JOIN sql structure works fine within the joinbuilder.

The 9.3 release does support SUBSQL ... but there is clearly some other subtle problem.
Looks like I might be out of luck ....

Greg

[Carsten Sørensen]

Hi Greg,

Late ... yeah it is 03.15 here in Denmark - but nice and peaceful for creating new functionality

Let's investigate whether we can get the JOIN-BUILDER to work on the older releases - would be great if it could serve it's purpose!

Question is how I can debug the earlier releases. Do you know whether I can get access to download any of those - to see whether I can see what is going wrong...?

Best regards,
Carsten

[shearerg]

Carsten,

First advice .... GO TO BED!
Answering your question ... I don't think so. I do have a bit of a collection of Ingres downloads ... but I doubt I have usable Windows versions of those releases. Also, I'm not sure my historical problems are where you should be focusing you're energies! If there was any chance of a generic change that would avoid using SUBSQL functionality that would be great ... but otherwise I don't think it would be worth your effort.

If the latest release were to function correctly, that would be good, as it is a stable release I was hoping to stay with for some time. My older releases will eventually be updated and gaining DAI functionality, for example, could provide an incentive. However, this won't happen in the immediate short term. By then I may move straight to a version 10.0 release ... which looks like it will avoid the problem entirely.

So ... please don't lose any more sleep I'll post an Ingres trace log on that forum and see if any clearer indication of the problem with the latest release shows up. I suspect it will still centre around the use of SUBSQL.

GO TO BED!

Greg

[shearerg]

Carsten,

This problem keeps getting weirder and weirder ....

As I'd mentioned, I decided to configure a local database and test the the Dynamic JOIN. So I created a local database (testdb) and unloaded the same couple of small tables I've been accessing from one of my servers. This is a standard thing to do. I then loaded this data into the local database and tested the JOIN, expecting it to work .... but I received the same error. This was not what I had expected.

So next I created a couple of tiny tables from scratch and tested the JOIN .... and it worked fine!!! At this point the obvious thing to look for is some difference in the underlying table structures etc, which I did. My original tables were btree, and the new tables were heap ... so I made all heap ... but still the same problem. I can honestly see no real differences now between these tables ... other than the actual data content and types. Of the differences, only INGRESDATE was likely to be problematic ... so I eliminated those fields ... but still the same problem!!!

As you have Ingres installed, I thought you might be interested to have a look at the data yourself. In the attached zip file is everything you need. Hopefully you can unzip this into a folder c:\tmp\testdb, as the load script is then correct. Then simply open the Ingres Command Prompt and:

cd c:\tmp\testdb
createdb testdb
sql testdb < copy.in

You will then have a database named testdb with 4 tables. I can get Dynamic JOIN to work between tables 'test1' and 'test2', but NOT between tables 'machines' and 'production'. I must be missing something really obvious .... so hopefully you can see it. Perhaps it will come to me this afternoon as I'm riding home! Right now I have no idea what is going on ....

Regards,
Greg

[Carsten Sørensen]

Hi Greg,

Got it - tested it - and I think I found the reason!

[Ingres][Ingres 10.0 ODBC Driver][Ingres 10.0]line 9, Column 'machine' found in more than one FROM list table.

on this SQL:

SELECT
machine , status, efficiency, poll, log, address, mill, checked, eprom, version, site, host,
J2.machine AS F12DAI12, J2.material AS F13DAI13, J2.description AS F14DAI14, J2.confirmation AS F15DAI15, J2.prod_order AS F16DAI16, J2.diam_spec AS F17DAI17, J2.speed_spec AS F18DAI18, J2.length_spec AS F19DAI19, J2.weight_spec AS F20DAI20, J2.diam AS F21DAI21, J2.length AS F22DAI22, J2.weight AS F23DAI23, J2.unit AS F24DAI24, J2.snarl AS F25DAI25, J2.break AS F26DAI26, J2.minrun AS F27DAI27, J2.mindown AS F28DAI28, J2.minnoplan AS F29DAI29, J2.minrepair AS F30DAI30, J2.shift AS F31DAI31
FROM cintac.machines DAI_
LEFT JOIN cintac.production J2
ON DAI_.machine = J2.machine

If I prefix machine with DAI_ then it seems to work.

like:
SELECT
DAI_.machine , status, ....

The reason we only see the problem sometimes is that it is only when the column name in the join becomes ambiguous.

I can remember that we removed the DAI_ prefix because of another database engine for ODBC.
I guess I will create a specific Ingres one and include the prefix.
Lets see whether that will fix it.
Thanks for providing test data and easy to use instructions!!

I'll be back!

Best regards,
Carsten

[shearerg]

Carsten,

You beat me to it, as I'd just uncovered the same information by running an internal Ingres ODBC trace on the problem JOIN.

Not that its any of my concern really ... but I hope you haven't been up all night working? I've been at work, but will be leaving shortly to cycle home. A relaxing Friday evening ahead gathering at a friends place. I hope you have a relaxing weekend planned also!

If this can be corrected for the Ingres driver, I expect Dynamic JOIN may then work for all my releases, as the sql shown in the ODBC trace looks quite standard ... just missing the table alias prefix. It appears that the Dynamic JOIN doesn't use SUBSQL, but from what you said earlier I assume that the Dynamic VIEW does? Is that correct? If so, I may be able to use Dynamic JOINS on my older releases, but not Dynamic VIEWS.

Have a good weekend!

Greg Shearer

[Bob Cergol]

Hi Greg,

I figured Carsten MUST be asleep by now -- so wanted to comment.

I think as soon as you use a dynamic join as a data source in a report -- a subselect comes into play, i.e. while you can browse data from the dynamic join itself, when used as a data source in the report dyanamic will: Select top 1000 * from (your dynamic join SQL script) DAI_
This allows it to append where clause for filters and/or conditions in the report design, etc.

Bob

[shearerg]

Understood Bob,

Thanks for that. So looks like I'll have JOINS and VIEWS limited to my latest release database. On the up side, it is my main database of interest and the others will eventually be upgraded. Also, if I decide a view is absolutely essential ... I can always build it in the database.

I suspect this means that Dynamic Relations are also not going to work against the older releases, as I expect they would also involve the use of subselects. At least this will be another incentive to get the systems upgraded. One of them really just involves a database upgrade ... but the other involves some hardware related issues which really slows things down. As they are both production systems, finding the right time can also be a problem.

It's good to have the causes of my problems all fully understood, but I was very confused for a while. I wish I'd tried the Ingres trace earlier, as it could have saved a few hours work!

Thanks again for the extra comments.

Greg