Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jess
Starting Member
17 Posts |
 Posted - 2007-09-24 : 17:16:07
|
| Hello, I have a question that is specific to Crystal Reports. If anyone can tell me a more appropriate forum to post this in, please let me know. I figured there were so many SQL gurus here that somebody could help...I am doing a very simple query using 2 tables that need to be joined. I always store all of my queries in Enterprise Manager as stored procedures and just load them into Crystal Reports for generating reports.For this particular report, I need to do everything from within' Crystal instead of loading a stored procedure. I'm not terribly familiar with designing the query in Crystal and I'm having a little trouble.The problem is that I need to do a LEFT join so that results show up even if there's no record in the second of the two tables. That alone is OK, I see how to do LEFT JOINs in Crystal. But I need a condition placed on both tables. Using Crystal reports to generate the query, both of these conditions are being place in the WHERE clause, but I need one of the conditions to be in the LEFT JOIN clause instead, otherwise it's canceling out the effects of using a LEFT JOIN instead of a regular JOIN statement. So it will only return results if there are entries in BOTH tables, but I want it to return results for everything even if there's no entry in table 2.So here's a more generic form of the SQL query that Crystal is generating:SELECT table1.empno, table2.jobcodeFROM table1LEFT OUTER JOIN table2ON table1.empno = table2.empnoWHERE table1.effdate >= CONVERT(DATETIME, '08-01-2007', 110)AND table2.effdate >= CONVERT(DATETIME, '08-01-2007', 110) and here's a generic form of what I want it to generate:SELECT table1.empno, table2.jobcodeFROM table1LEFT OUTER JOIN table2ON table1.empno = table2.empnoAND table2.effdate >= CONVERT(DATETIME, '08-01-2007', 110) WHERE table1.effdate >= CONVERT(DATETIME, '08-01-2007', 110)Note that the only difference is that one of the where clauses was moved into the join statement so that it will return results even if there's nothing in table 2.Can somebody tell me how to accomplish this using on Crystal Reports? Is there a way I can just tell it the SQL query to use instead of having Crystal "build" the query for me?Any help would be greatly appreciated!Thanks,Jess |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-25 : 04:46:17
|
| Why dont you use stored procedure having your preferred code and design CR based on that?You dont need to edit sql in CRMadhivananFailing to plan is Planning to fail |
 |
|
|
jess
Starting Member
17 Posts |
Posted - 2007-09-25 : 08:53:53
|
| Well I would just use a stored procedure like you said, but this project is for our HR department. They handle creating their own simple reports out of the staff database program that they use. They obviously don't know anything about programming but they can build the queries on their own through Crystal, and we're hoping they can continue to do that.I can't believe that there wouldn't be a way to do this in Crystal.It looks like the only place it will place a condition/filter on Table2 is in the WHERE clause which completely cancels out the whole effect of using a LEFT JOIN instead of a JOIN statement.I wonder if I just remove that condition from TABLE2 and have it return all of the results when it runs the query, and then put a formula on the display to suppress the results that don't meet the condition? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-09-25 : 09:09:02
|
| <<I wonder if I just remove that condition from TABLE2 and have it return all of the results when it runs the query, and then put a formula on the display to suppress the results that don't meet the condition?>>Instead, cant you try using parameter to filter the records you want?MadhivananFailing to plan is Planning to fail |
|
|
|
jess
Starting Member
17 Posts |
Posted - 2007-09-25 : 10:11:15
|
| "<<I wonder if I just remove that condition from TABLE2 and have it return all of the results when it runs the query, and then put a formula on the display to suppress the results that don't meet the condition?>>Instead, cant you try using parameter to filter the records you want?"I'm not sure how that helps because it would put that filter condition in the where clause.That means that if there are no records found in table2 that meet the condition, no results will be displayed.So if there are 100 results returned from Table1 that meet the condition, and only 10 of those also have data that is found in Table2 which meets the condition, then it will only display those 10 records. I want it to display all 100 records from Table1 and if there is corresponding data in Table 2, show that with it, otherwise leave that part empty. That's exactly what a left join is for, but it doesn't do any good when the condition gets put in the where clause. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
tmoney
Starting Member
1 Post |
Posted - 2008-03-18 : 19:56:06
|
| I use crystal 9 and all the crytal generated SQL joins are placed in the where clause.Options:1.)Type your select statement directly into a crystal sql command. This is similar to creating a veiw. 2.)Create a Veiw defined by your select statement on the database that can be accessed by crystal reports users (granted rights to read the veiw on the database.)3.)Place "or isnull(table2.column)" condition in your where clause using the select expert . . . Crystal considers no entries in table2 to be null values and not a 0 date value. |
|
|
|
|
|
|
|
|
Crystal Reports specific help