Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Outer join, detail section Post Reply Post New Topic
Author Message
lls8000
Newbie
Newbie


Joined: 01 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote lls8000 Replybullet Topic: Outer join, detail section
     Posted: 01 Dec 2009 at 1:23pm

I'm having issues with an outer join in Crystal XI. This is what I'm looking at:

    Table A left outer joins to table B

                     table B inner joins to table C

                                    table C inner joins to table D

 

The outerjoin from A to B is working just fine, until I add a field from table C or D to the detail section of the report for printing purposes. When I do this, the outerjoin between A and B functions like an innerjoin.

    - I've tried adding table C and D fields to the Sort. No changes.

    - There is no selection criteria on Tables B, C or D. But in desperation, I've tried to add this, but it didn't change my results:

and (isnull({B.CHARGE_ITEM_ID}) or {B.CHARGE_ITEM_ID} >= 0)
and (isnull({C.INTERFACE_CHARGE_ID}) or {C.INTERFACE_CHARGE_ID} >= 0)
and (isnull({D.CODE_VALUE}) or {D.CODE_VALUE}>=0)

 

Any other ideas? Thanks, lls8000

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Dec 2009 at 2:12pm
Hmm...
I woul get rid of the select statement. It will only more likely keep it an inner join.
Unlike SQL, In crystal the joins are not enforced until you use a field from that table or unless you explicitly mark them as enforced in the join set up. That is why you are seeing it act up on addition of the fields.
 
Do you have any select statement in the report or a grouping that might be impacting this?
IP IP Logged
lls8000
Newbie
Newbie


Joined: 01 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote lls8000 Replybullet Posted: 02 Dec 2009 at 10:17am

Thanks for the input. I've removed the isnull lines from the select statement. I do have other components in the select, but nothing that touches these tables. There is no grouping on the report.

The thing that I found odd is that if I pull in a field from the outer joined table (Charges), it leaves it as an outer join, but once I pull in a field that is on the table that is inner joined to the Charges table (Interface_Charges), it treats Charges as an inner join too. Here's the sql. Thanks!

  SELECT "TASK_ACTIVITY"."CATALOG_TYPE_CD",
"TASK_ACTIVITY"."TASK_STATUS_CD",
"TASK_ACTIVITY"."UPDT_DT_TM",
"PERSON"."NAME_LAST_KEY",
"ENCOUNTER"."LOC_FACILITY_CD",
"CLINICAL_EVENT"."PUBLISH_FLAG",
"CLINICAL_EVENT"."VIEW_LEVEL",
"ENCNTR_ALIAS"."ALIAS_POOL_CD",
"CODE_VALUE_cattype"."DISPLAY",
"CODE_VALUE_loc"."DISPLAY",
"ENCNTR_ALIAS"."ALIAS",
"PERSON"."NAME_FULL_FORMATTED",
"CODE_VALUE_catcd"."DISPLAY",
"CODE_VALUE_taskreason"."DISPLAY",
"PRSNL"."NAME_FULL_FORMATTED",
"TASK_ACTIVITY"."SCHEDULED_DT_TM",
"TASK_ACTIVITY"."ORDER_ID",
"CHARGE"."CHARGE_ITEM_ID",
"INTERFACE_CHARGE"."INTERFACE_CHARGE_ID"   //**Removing this, the outer join to Charges is honored.  
 FROM   (((((((((("V500"."TASK_ACTIVITY" "TASK_ACTIVITY"
INNER JOIN "V500"."PERSON" "PERSON" ON "TASK_ACTIVITY"."PERSON_ID"="PERSON"."PERSON_ID")
INNER JOIN "V500"."CODE_VALUE" "CODE_VALUE_cattype" ON "TASK_ACTIVITY"."CATALOG_TYPE_CD"="CODE_VALUE_cattype"."CODE_VALUE") INNER JOIN "V500"."ENCOUNTER" "ENCOUNTER" ON "TASK_ACTIVITY"."ENCNTR_ID"="ENCOUNTER"."ENCNTR_ID")
INNER JOIN "V500"."CLINICAL_EVENT" "CLINICAL_EVENT" ON "TASK_ACTIVITY"."EVENT_ID"="CLINICAL_EVENT"."EVENT_ID")
INNER JOIN "V500"."CODE_VALUE" "CODE_VALUE_loc" ON "TASK_ACTIVITY"."LOCATION_CD"="CODE_VALUE_loc"."CODE_VALUE")
INNER JOIN "V500"."CODE_VALUE" "CODE_VALUE_taskreason" ON "TASK_ACTIVITY"."TASK_STATUS_REASON_CD"="CODE_VALUE_taskreason"."CODE_VALUE") INNER JOIN "V500"."CODE_VALUE" "CODE_VALUE_catcd" ON "TASK_ACTIVITY"."CATALOG_CD"="CODE_VALUE_catcd"."CODE_VALUE")
INNER JOIN "V500"."PRSNL" "PRSNL" ON "TASK_ACTIVITY"."PERFORMED_PRSNL_ID"="PRSNL"."PERSON_ID")
INNER JOIN "V500"."ENCNTR_ALIAS" "ENCNTR_ALIAS" ON "ENCOUNTER"."ENCNTR_ID"="ENCNTR_ALIAS"."ENCNTR_ID")
LEFT OUTER JOIN "V500"."CHARGE" "CHARGE" ON "CLINICAL_EVENT"."ORDER_ID"="CHARGE"."ORDER_ID")
INNER JOIN "V500"."INTERFACE_CHARGE" "INTERFACE_CHARGE" ON "CHARGE"."CHARGE_ITEM_ID"="INTERFACE_CHARGE"."CHARGE_ITEM_ID"
 WHERE  ("TASK_ACTIVITY"."CATALOG_TYPE_CD"=636078 OR "TASK_ACTIVITY"."CATALOG_TYPE_CD"=3422508)
AND ("TASK_ACTIVITY"."TASK_STATUS_CD"=418 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=419 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=420 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=422 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=423 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=424 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=614379 OR "TASK_ACTIVITY"."TASK_STATUS_CD"=24099635)
AND ("ENCOUNTER"."LOC_FACILITY_CD"=633867 OR "ENCOUNTER"."LOC_FACILITY_CD"=3186521 OR "ENCOUNTER"."LOC_FACILITY_CD"=3196534)
AND "ENCNTR_ALIAS"."ALIAS_POOL_CD"=683992
AND "PERSON"."NAME_LAST_KEY"<>'TESTPATIENT'
AND "CLINICAL_EVENT"."PUBLISH_FLAG">0
AND "CLINICAL_EVENT"."VIEW_LEVEL">0
 ORDER BY "ENCNTR_ALIAS"."ALIAS", "TASK_ACTIVITY"."SCHEDULED_DT_TM"
 
IP IP Logged
lls8000
Newbie
Newbie


Joined: 01 Dec 2009
Location: United States
Online Status: Offline
Posts: 7
Quote lls8000 Replybullet Posted: 02 Dec 2009 at 11:17am
I've also tried both 'not enforced' and 'enforced both' on the outer join, and the table that is inner joined to the outer joined table. No luck. Thanks.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 02 Dec 2009 at 2:58pm
Reading SQL is not my strong suite but basically it looks like you have criteria on tables in your WHERE clause that are converting the outer join to an inner join.
Crystal allways applies the select criteria after the join so you cannot limit the data of one table and then left join it into another table (unless you write a command in crystal).
If your source is SQL and you have rights to create a view or stored proc the easiest solution is to create these with your exclusionary critiera there then left join the views togther in crystal.
Does that help? 
Maybe lockwelle or someone else better versed in sql might take a peek and comment on the process as well.


Edited by DBlank - 02 Dec 2009 at 7:15pm
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.