Crystal Reports Full Outer Joins

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Full Outer Joins

Author	Message
Tupacmoche Groupie Joined: 04 Apr 2007 Online Status: Offline Posts: 52	Topic: Full Outer Joins Posted: 09 Apr 2007 at 11:53am
	Is it true that you cannot use a full outer join when using Oracle 10g in Crystal Reports VI?
	Rob
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 10 Apr 2007 at 10:34am
	Do you mean Crystal XI? If so, then no, it is not true. I just tested it with Oracle 10g and Crystal XI and had no problems. -Dell Edited by hilfy - 10 Apr 2007 at 10:35am
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

Tupacmoche Groupie Joined: 04 Apr 2007 Online Status: Offline Posts: 52	Posted: 10 Apr 2007 at 11:05am
	I got the following msg: Failed to retrieve data from database ORA-03113 end of file on communications channel DB Vendor Code 3113 OCI Call: OCI StmtExecute
	Rob
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 10 Apr 2007 at 11:14am
	Do you have a very large data set being returned? Do you have more than one full outer joins? (NOT a good idea!) Have you tested the SQL that Crystal generated to see if it's correct? Go to the Database menu and select Show SQL Query - you can copy the SQL and paste it into whatever tool you use for querying the database (we use Toad or PL/SQL Developer). You can then see where Oracle might give you the error. If you need to 'tweak' the SQL by adding hints, you can always set the SQL in Crystal yourself by using the CommandText instead of linking the individual tables in Crystal. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

Tupacmoche Groupie Joined: 04 Apr 2007 Online Status: Offline Posts: 52	Posted: 10 Apr 2007 at 11:39am
	This is the SQL that Crystal generates: SELECT "BUDGET_COMMITMENTS"."ACCOUNT_DESC", "CODE_B"."CODE_B", "BUDGET_COMMITMENTS"."ACCOUNT", "BUDGET_COMMITMENTS"."CODE_B" FROM "TEMP1APP"."CODE_B" "CODE_B" RIGHT OUTER JOIN "TEMP1APP"."BUDGET_COMMITMENTS" "BUDGET_COMMITMENTS" ON "CODE_B"."CODE_B"="BUDGET_COMMITMENTS"."CODE_B" The only change is to make it a "Full Outer Join" not just a "Right Outer Join". When I change "Right" to "Full" I get the same error message. I used Universal ISQL to run it.
	Rob
	IP Logged

hilfy Admin Group Joined: 20 Nov 2006 Online Status: Offline Posts: 3702	Posted: 10 Apr 2007 at 12:02pm
	So something is happening in Oracle that's causing this. The SQL looks ok to me.... From your SQL, I think what you're trying to do is get all records from both tables, regardless of whether the CODE_B value exists in one or the other. Correct? Or is there some other reason you need a full outer join? Not that it should make any difference, but you could try changing the direction of the link in Crystal - from BUDGET_COMMITMENTS to CODE_B instead of the other way around. If that doesn't help, you may want to look at an explain plan for the SQL and see what you need to do to tune it. That's not something I can help you with, so if you're not sure how to do that you'll need to find someone in your organization who has experience tuning Oracle queries. -Dell
	Proviti, Data & Analytics Practice www.protiviti.com/US-en/data-management-advanced-analytics
	IP Logged

Tupacmoche Groupie Joined: 04 Apr 2007 Online Status: Offline Posts: 52	Posted: 10 Apr 2007 at 12:17pm
	I took your advice and used the command text box to code it manually. But I used a UNION and it worked. See Select TEMP1APP.code_b.CODE_B, TEMP1APP.BUDGET_COMMITMENTS.CODE_B, TEMP1APP.BUDGET_COMMITMENTS.ACCOUNT_DESC, TEMP1APP.BUDGET_COMMITMENTS.ACCOUNT From TEMP1APP.BUDGET_COMMITMENTS Left Outer Join TEMP1APP.code_b On Code_b.code_b = budget_commitments.code_b Union Select TEMP1APP.code_b.CODE_B, TEMP1APP.BUDGET_COMMITMENTS.CODE_B, TEMP1APP.BUDGET_COMMITMENTS.ACCOUNT_DESC, TEMP1APP.BUDGET_COMMITMENTS.ACCOUNT From TEMP1APP.BUDGET_COMMITMENTS Right Outer Join TEMP1APP.code_b On Code_b.code_b = budget_commitments.code_b Order By 1 Desc, 4 I will try tracing it as well.
	Rob
	IP Logged

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.016 seconds.