Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Left outer Join with condition Post Reply Post New Topic
Author Message
azzan
Newbie
Newbie


Joined: 19 Oct 2009
Location: Jordan
Online Status: Offline
Posts: 2
Quote azzan Replybullet Topic: Left outer Join with condition
    Posted: 19 Oct 2009 at 2:28am

I am using SQL2000 with Crystal report 9

Is there a way to add condition in the left outer join sth like:

Select tbl1.number , tbl1.desc, sum(tbl2.amount)   from tbl1 left outer join tbl2 on tbl1.number=tbl2.number and tbl2.ldate <= ‘2009/12/31’

Tbl1

1                     aaa

2                     bbb               

3                     ccc

Tbl2

1              5000      2009/06/06

1              1000       2008/12/31

1              6000       2008/11/11

Result must be

1          aaa 7000

2         Bbb 0

3         Ccc 0

I cannot add the condition in the Database expert (tbl2.ldate <= ‘2009/12/31’) is there a way to do that?

thanks :)
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 19 Oct 2009 at 7:06am

I would add a filter 'Report/Selection Formulas/Record' and place it there.

Personally, I get all the data from a stored proc, pass in the parameters get only the data you want back. No need filter in the report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 19 Oct 2009 at 7:27am

My two cents....

I think the select expert will not work here. It will exclude all the joined records (in both tables) that have a row > 12-31-09 and no record < that date.
If version 9 allows for a Command use it instead of the tables. Write your Command SQL statement exactly like you did in your post with the left join and an AND statement on the data filter.


Edited by DBlank - 19 Oct 2009 at 7:29am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 19 Oct 2009 at 10:55am
Command is easiest, but I have never used CR9, but you should be able to code a formula...
In the example, I am assuming you meant 12/31/08, not 09 as the total should be 12000 as written...just making sure.
IP IP Logged
azzan
Newbie
Newbie


Joined: 19 Oct 2009
Location: Jordan
Online Status: Offline
Posts: 2
Quote azzan Replybullet Posted: 19 Oct 2009 at 11:41pm
thanks all :)
yes i meant 08 :D
i added a formula field with the amount i need if row meets my criteria else 0 then sum for the field :S worked
 
i tried the Commad but very slow :( but like to know new things i never new i can do this :)
thanks again
IP 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.031 seconds.