Author |
Message |
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Topic: Outer Join Subreport Link Posted: 10 Feb 2011 at 8:05pm |
I have a main report table mainA linked with a subreport table subA where mainA.key1 = subA.key1
In the subreport I have a subA joined with subB on subA.field1 = subB.field1
I need to return all rows in subA but only rows in subB where subB.field2 = mainB.field2
If create an additional link between the main report and subreport mainB.field2 = subB.field 2 I no longer get all rows from subA.
I know how to accomplish it in straight SQL but how do I do it in Crystal?
Thanks!
Edited by marz - 10 Feb 2011 at 8:05pm
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 11 Feb 2011 at 3:07am |
In CR, you are only allowed 1 level of subreport, ie a subreport cannot call a subreport.
Usually, I create a stored proc, and each row is a 'super' row. By this, I mean that each row contains ALL the information that would ever be displayed for that value. Then I tailor the display to only show what is needed, when/where it is supposed to.
The down side, is that sometimes the aggregate functions will overreport the value (as there are more rows in the data than on the screen), but there are ways to get around that using either/both SQL and CR.
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 11 Feb 2011 at 4:02am |
How are you linking between SubA and SubB? There may be a way to do this, but I need more info.
-Dell
|
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 11 Feb 2011 at 4:15am |
left outer join on subA.field1 = subB.field1
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 11 Feb 2011 at 5:04am |
Ok, here's what I would do. Keep both links to the subreport. Edit the selection formula in the subreport to look something like this:
{subA.Key1} = {?pm-mainA.Key1} and
(IsNull({subB.Field2}) or
{subB.Field2} = {?pm-mainB.Field2})
Note the parentheses that I've highlighted in red - you have to check for null first and you MUST have the parens where I've put them or else this won't work correctly.
-Dell
|
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 11 Feb 2011 at 6:36am |
Hmmm I agree with that thought but it still isn't giving me all rows in subB and I'm positive I got it right. And just to make sure I tried all the different join options between subA and subB. Any thoughts?
Edited by marz - 11 Feb 2011 at 6:36am
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Feb 2011 at 6:40am |
this will still exclude rows where there was a joined subB.Field2 that is <> ?pm-mainB.Field2
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 11 Feb 2011 at 6:57am |
Yeah I just look at the SQL and it wouldn't work that way.
|
IP Logged |
|
marz
Newbie
Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
|
Posted: 11 Feb 2011 at 7:03am |
Here's the SQL from the subreport. I need to change what's underlined
SELECT .....
FROM ((("sysdba"."vABS_CUSTOMER" "CUSTOMER"
INNER JOIN "sysdba"."vABS_CUSTOMERPREPCHARGE" "CUSTOMERPREPCHARGE" ON "CUSTOMER"."CUSTOMER_ID"="CUSTOMERPREPCHARGE"."CUSTOMER_ID")
INNER JOIN "sysdba"."vABS_REASONCODE_DESC" "REASONCODE_DESC" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE_DESC"."REASONCODE_ID")
INNER JOIN "sysdba"."vABS_REASONCODE" "REASONCODE" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE"."REASONCODE_ID")
LEFT OUTER JOIN "sysdba"."CONTRACT_RATES" "CONTRACT_RATES_slx" ON "REASONCODE"."CODE"="CONTRACT_RATES_slx"."CODE"
WHERE "CUSTOMERPREPCHARGE"."STARTDATE"<CONVERT(DATETIME, '2011-02-12 00:00:00', 120)
AND "CUSTOMER"."CUSTOMER_ID"={?Pm-CUSTOMER_DelTo.CUSTOMER_ID}
AND "CONTRACT_RATES_slx"."ACCOUNTID"={?Pm-ACCOUNT.ACCOUNTID}
If I could just move that underlined section to be part of the Join things would be resolved. Is that possible?....
SELECT ...
FROM ((("sysdba"."vABS_CUSTOMER" "CUSTOMER"
INNER JOIN "sysdba"."vABS_CUSTOMERPREPCHARGE" "CUSTOMERPREPCHARGE" ON "CUSTOMER"."CUSTOMER_ID"="CUSTOMERPREPCHARGE"."CUSTOMER_ID")
INNER JOIN "sysdba"."vABS_REASONCODE_DESC" "REASONCODE_DESC" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE_DESC"."REASONCODE_ID")
INNER JOIN "sysdba"."vABS_REASONCODE" "REASONCODE" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE"."REASONCODE_ID")
LEFT OUTER JOIN "sysdba"."CONTRACT_RATES" "CONTRACT_RATES_slx" ON "REASONCODE"."CODE"="CONTRACT_RATES_slx"."CODE"
AND "CONTRACT_RATES_slx"."ACCOUNTID"={?Pm-ACCOUNT.ACCOUNTID}
WHERE "CUSTOMERPREPCHARGE"."STARTDATE"<CONVERT(DATETIME, '2011-02-12 00:00:00', 120)
AND "CUSTOMER"."CUSTOMER_ID"={?Pm-CUSTOMER_DelTo.CUSTOMER_ID}
Edited by marz - 11 Feb 2011 at 7:04am
|
IP Logged |
|
|