Use of a dynamic join view in the dynamic join builder

[jar]

I am using the PersonalEdition with an SQLserver 2008 database.
A Dynamic Join View (DJV) that I created is joining 3 tables and when I browse it it displays correctly. It is an accountnumber with 1st en second level grouping. I made a seperate DJV because I will be using the level and grouping many times. So al working OK.

When I make a new DJV in which I have the measures table as the main table and the earlier created DJV as the second the result is an error. Even when I make a new DJV with only the earlier DJV as the main table it is giving this error. The error is : "-2147217900 : Incorrect syntax near :.

The generated SQL is : SELECT *
FROM (@DYNJOIN:,dbo.Grootboekrekeningen;GrbBd;GrbCode;;;;;;;;;; ,dbo.Groependetails;EgrBd;EgrGrbCode;EgrBd;EgrOvzNr;EgrGrpCode;;;;;;L; ,dbo.Groepen;;;GrpBd;GrpOvzNr;GrpCode;GrpBd;GrpOvzNr;GrpHgrCode;;;L; ,dbo.Hoofdgroepen;;;;;;HgrBd;HgrOvzNr;HgrCode;;;L; ,,,@) DAI_

I have no clue why it is not working. I am correct to assume that you can use DJV's created earlier in a new DJV? I have tried switching language (dutch and English) but to no results.

Any help on this would be very much appreciated.

Kind regards,
Jeroen

[Carsten Sørensen]

Hi Jeroen,

Thanks for your question!

It isn't possible to use a DJV as the source in other join's. A bit embarrassing actually - because when we don't support it - we shouldn't allow that you can select it. In principle there is no reason why we shouldn't allow you to do so - apart from very confusing automatic prefixing etc. I.e. if you imagine that the generated join SQL will become a sub-join of a new join etc.. and a little bit caused by the history of the join-builder where we initially used it to join objects without the use of sub-selects (as some older database engines doesn't support that).

The work-around is to copy the join-builder view SQL (that you can see in the Join Builder editor) and create a normal Dynamic AI SQL View.

We will investigate the possibility to allow the use of previously defined Join-Builder views in new joins.

Best regards,
Carsten

[jar]

Hello Carsten,

Thanks for your response.
I managed to get the result using a normal SQL view in the master DJV. I was indeed a little confused because the option was there to select a DJV.

As for the confusing automatic prefixing, would it be possible to use the DJV name for that. This would also make it very easy to trace errorr back to the DJV in question when you have multiple DJV merged in one DJV.

Kind regards,
Jeroen