Print Page | Close Window

Left outer Join with condition

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8054
Printed Date: 29 Apr 2024 at 2:39am


Topic: Left outer Join with condition
Posted By: azzan
Subject: Left outer Join with condition
Date 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 :)


Replies:
Posted By: lockwelle
Date 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.

Posted By: DBlank
Date 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.

Posted By: lockwelle
Date 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.

Posted By: azzan
Date 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


Print Page | Close Window