Simple Join with two conditions

[JohnBurmester]

Hello.

First of all sorry for the bad english and poor CR knowledge.

I want to do a quite easy thing but do not know how to do it. I have several tables connected through ids. One of them is connected through 2 ids. SQL would look like the following:

SELECT FROM tab1, tab2, tab3, tab4, tab5
where tab1.id1=tab2.id1
AND tab2.id2=tab3.id2
AND tab3.id3=tab4.id3
AND tab4.id4=tab5.id4
AND tab1.id1=tab5.id1

How can I use these Tables in CR (8.5) ?

If I just connect it through the visual connection wizard (is it called like that in english?) I get a message (translated, so its something like that): "Several path for one table existent. Try reversing a path."

Reversing is not possible because that would create a cycle.

If I just ignore the message, CR does a join with the table without acknowledging the existence of the restriction. It joins my data in Tab1-Tab4 with every record from tab5 where id1=id1.

Hope this is uderstandable and someone has an easy solution?

Another question: Is there a way to use simple SQL within CR? Like this I could (well I imagine it being posible) use something like:

select from tab5 where tab5.id1 = {tab1.id1} AND tab5.id4 = {tab4.id4}.

However the table should be unconnected which would create the undesired join, too?

Any help would be greatly appreciated. Thanks in advance.

John Burmester

 

[DanENW]

John,

I think you are out of luck with the CR Report Designer in the first case. The join is too complex for it to handle. In the first case tables 1, 4, and 5 each link to two other tables; this is not a legal situation in the report writer.

Using the Visual Connection Wizard you may only join a table on the right from only one table on the left; however you may join a table on the left to "N" tables on the right.

The second case should work.

I hope this is helpful.

We are using Seagate Info 7.5 (CR 8.0) at our site. This is different in more thatn just a version number than your version. But if you have the "SQL Designer" component (file name cqw32.exe) you can develop significantly more complex queries, save them without data, and base reports on these queries. The SQL Designer has a GUI, SQL Expert (limited abilities); and an advanced mode, Enter SQL statement directly which requires knowlege of SQL and your database. We use the advanced mode almost exclusively for the capablities, we edit in Word or Wordpad and paste into the edit box which is too small.

 

[JohnBurmester]

Hi.

Thank you very much for the answer. I will try the SQL solution. However I am extremely surprised that CR is not able to support such a simple join with two conditions. That's poor.

Thanks,
John.

 

[DanENW]

John,

The join condition may seem to be simple, but it is generally considered to be poor design to need to join tables that way. Having said that, we have the same problem with our vendor databases and I couldn't agree with you more!

 

[JohnBurmester]

Hi.

I have found another solution to the problem. I now use a subreport which I connect through the two IDs. This works fine!!

Cheers,
John.

 

[lbass]

Hi John,

I know you have a solution, but I don't think the subreport is necessary (and subreports can slow your report), and that maybe you should fool around with the links a little more. I think you just need to decide how to link Table 5 with the other tables. Choose either:

tab4.id4 = tab5.id4 //or
tab1.id1 = tab5.id1

Leave your other links as you already have them. In either case, Table 5 is connected to Table 1, but in the first case it is via Table 2 ->Table 3->Table 4->Table->5. If your tables have one-to-many relationships, then I think that using the tab1.id1 = tab5.id1 link would give you the least number of records returned (a faster report).

-LB