Right Outer Join
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17989
Printed Date: 20 Apr 2025 at 10:56pm
Topic: Right Outer Join
Posted By: Michael_PMC
Subject: Right Outer Join
Date Posted: 12 Nov 2012 at 2:29am
I have two similar reports, the first one in CR 8 and the other in CR XI. They both address the same kind of Oracle DBases (in a one to many relationship), but in two different office locations.
Here's the SQL Query for the first report:
SELECT
"NCAANGL1"."CONTAINERNUMMER",
"NCAANGH0"."INTERNNUMMER"
FROM
"NCTS"."NCAANGL1" "NCAANGL1",
"NCTS"."NCAANGH0" "NCAANGH0"
WHERE
"NCAANGL1"."INTERN_VOLGNUMMER" = "NCAANGH0"."INTERNNUMMER" AND
"NCAANGH0"."DATUM_AANVAARDING_25_28" >= '20121001' AND
"NCAANGH0"."DATUM_AANVAARDING_25_28" <= '20121031'
The second report is made with CR XI and shows following SQL Query:
SELECT
"NCAANGL1"."CONTAINERNUMMER",
"NCAANGH0"."INTERNNUMMER"
FROM "NCTS"."NCAANGL1" "NCAANGL1" RIGHT OUTER JOIN "NCTS"."NCAANGH0" "NCAANGH0" ON "NCAANGL1"."INTERN_VOLGNUMMER"="NCAANGH0"."INTERNNUMMER"
WHERE ("NCAANGH0"."DATUM_AANVAARDING_25_28">='20121001' AND "NCAANGH0"."DATUM_AANVAARDING_25_28"<='20121031')
Both reports have parameterfields for selection of the perioddates.
Now, the first report (in CR 8) does not show records in the main table that have no related record, while the second report (in CR XI) does!
Anybody any idea how to have the first report act as the second one?
|
Replies:
Posted By: hilfy
Date Posted: 12 Nov 2012 at 10:39am
|
Right-click on the join between NCAANGL1 and NCAANGH0. If the link is FROM NCAANGL1 to NCAANGH0, make it a right outer join. If it's the other direction, make it a left outer join. -Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|