Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Can I change Join in Database Expert-generated SQL Post Reply Post New Topic
Author Message
stonewall63
Newbie
Newbie
Avatar

Joined: 23 May 2012
Online Status: Offline
Posts: 11
Quote stonewall63 Replybullet Topic: Can I change Join in Database Expert-generated SQL
     Posted: 24 Jun 2014 at 3:38pm

I have a report in CR 2011 where the SQL for the columns/rows was generated using the Database Expert. Overall, works fine, until I found I needed to have one of the selection criteria attached to the OUTER JOIN between two tables instead of in the WHERE clause (where the selection criteria normally appear). I need to have the data from the left table regardless of whether there are matches in the right table.

In the “old days” (Crystal version 8.5) you could edit the .RPT file with Notepad and just change what was generated. Not anymore.

Is there a way within Crystal Reports 2011 to edit somehow the generated SQL statement to do this?

Or, do I have to copy the Database Expert-generated SQL into a SQL Command so I can edit it? (Thus reworking the report.)

TIA.

Brad
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 25 Jun 2014 at 5:05am
What I would try would be to go into the Linking for the tables, right click on the link between the tables and change the link type to an outer join (I don't know which way is left vs right) and then check the SQL again and see if it is what you are looking for.

HTH
IP IP Logged
stonewall63
Newbie
Newbie
Avatar

Joined: 23 May 2012
Online Status: Offline
Posts: 11
Quote stonewall63 Replybullet Posted: 25 Jun 2014 at 6:37am
Yes, that is what I have currently, a LEFT OUTER JOIN. What I need is to have a way to add another condition to the join, such that:
 
LEFT JOIN EMPLOYEE ON EMPLOYEE.EMP_ID = PAYROLL.EMP_ID
 
can now have
 
LEFT JOIN EMPLOYEE ON EMPLOYEE.EMP_ID = PAYROLL.EMP_ID AND
EMPLOYEE.STATE = "MI"
 
This way, I would get all the PAYROLL records regardless of if their state is Michigan, which is what I want.
 
Is there a way to add that extra clause within Crystal or with the Database Expert and I stuck?
 
Thanks,
Brad
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 25 Jun 2014 at 6:46am
sorry, I misunderstood, though your description makes sense.

just so I am clear, you want to add the 'And employee.state = 'MI'' to the join.

Since you said that you want to get records regardless of state, that statement could be in either place, the join or the where, though I would think the where would be the simpler location.

if CR has already coded the statement with the state into the sql...that seems odd, and i probably am not much help as all of my reports are either commands (other people wrote I just support) or are stored procedures.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jun 2014 at 8:08am
I don't know of anyway to alter it other than to replace it with the command (or a stored proc). However, I assume you are doing this due to some calculation reason which you may just aas easily figure out using a conditon (EMPLOYEE.STATE = "MI") in a running total or shared variable


Edited by DBlank - 25 Jun 2014 at 8:08am
IP IP Logged
stonewall63
Newbie
Newbie
Avatar

Joined: 23 May 2012
Online Status: Offline
Posts: 11
Quote stonewall63 Replybullet Posted: 25 Jun 2014 at 8:11am
That is correct. The volume of data (not payroll related, this was just example names for a public forum), is in the millions of rows. Thus, reading it ALL in and then using a formula to control which ones I use was cumbersome and heavy on resources.
 
I went ahead and changed the SQL to one I put in myself, instead of the Database Expert. Now it flies!
 
Thanks, all.
 
Brad
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.