?
Solved

How to make Crystal using ANSI outer joins?

Posted on 2009-12-22
8
Medium Priority
?
480 Views
Last Modified: 2012-05-08
Hi,

I'm working on some reports with Crystal in conjunction with a Sybase Database server.

I realized that Crystal generates the Outer Joins using the crystal specific  syntax ( *= instead of LEFT OUTER JOIN). With this it is not possible to filter on the values of the second table which is added by the outer join.

Using the ANSI syntax with table1 OUTER JOIN table2 on table1.field = table2.field solves this problem when I use a direct sql query.

Is there a possibility to setup crystal in a way that it uses the ANSI syntax instead of the proprietary sybase syntax?


For example:

Select *
From table1 LEFT OUTER JOIN table2 on table1.UserID = table2.UserID
WHERE table2.location = 'Test'

will only return the entries that have 'Test' in the field of the second table.

Select *
From table1, table2
WHERE table1.UserID *= table2.UserID AND
table2.location = 'Test'

will return all entries for the table1 because the filter on the second table is ignored by sybase.

Any help will be appreciated.

0
Comment
Question by:HugoHiasl
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 668 total points
ID: 26104796
No.  You cannot influence the style of sql automatically generated by CR.
Your only option is to use the Add Command feature - which I assume is what you are referring to by 'use a direct sql query.'
0
 
LVL 13

Assisted Solution

by:PCIIain
PCIIain earned 668 total points
ID: 26104819
And, if you are filtering on the field, it's not an outer join. It's an inner join.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 664 total points
ID: 26105474
Agree.  Consider from the view point of Crystal.  NULL doesn't equal 'Test' so all records from Table1 with no table2 are thrown out.

You could use the COMMAND as

Select *
From table1 LEFT OUTER JOIN table2 on table1.UserID = table2.UserID
AND (IsNull(table2.location) OR table2.location = 'Test')

mlmcc
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 12

Author Comment

by:HugoHiasl
ID: 26110532
Thank you for the replies.

@ mlmcc: I would expect Crystal to throw out all entries of table1 that do not have related entries in table2 but it is not doing this. Crystal brings all entries of table1 regardless if they have entries in table2 due to the outerjoin.

I found an interesting description in the Sybase manual.

If I use

SELECT *
FROM table1. table2
WHERE table1 *= table2 AND
table1.UserID = table2.UserID AND
table2.Location = 'Test'


then Sybase first filters both tables. This leads to all rows of table1 and teh rows with Location = 'Test' of table2. After this it does the outer join which leads to a resultset that is returning ALL rows of table1.


I would have expected to get only the entries in table1 that have a related entry in table2 that have Location='Test'. But this is only done by Sybase if the ANSI outer join syntax is used (table1 LEFT OUTER JOIN table2 on table1.UserID = table2.UserID)

For ODBC connections you can set up the SQLJoinBuilder by a registry key. But I did not an information how to do this for native connections. http://www.sdn.sap.com/irj/boc/go/portal/prtroot/docs/library/uuid/80ab7adc-971e-2b10-0cad-a17dd0393d93?QuickLink=index&overridelayout=true


0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 668 total points
ID: 26110805
I think the way CR works with outer joins and selection is pretty typical.

I had understood from your original Q that you were able to get your sql to work correctly if you used the Add command feature in CR (as far as I know CR only submits the sql and waits for a response from the 'server').
If so why is that not an acceptable approach?



0
 
LVL 13

Assisted Solution

by:PCIIain
PCIIain earned 668 total points
ID: 26110966
In the database expert, have you checked that the link type between the two tables is set to inner join rather than left outer?

You don't want a left outer....
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 664 total points
ID: 26116058
An outer join is defined as
Bring in all records from table1 and the records from table 2 that match the join field.

If you only want the table1 records with a related record in table2 then you need to use an INNER JOIN

mlmcc
0
 
LVL 12

Author Comment

by:HugoHiasl
ID: 26208902
There was no suitable solution for this. We made a support call to SAP.

I'll accept all solutions and share the points
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question