saleslogix and crystal

[twestfall]

I have an interesting problem with the following SQL query used in a Crystal Report. The problem that I'm having is that even though I am putting in this specific SQL statement when the report is run from within SalesLogix the select statement is being adjusted. The problem is that some of the LEFT OUTER JOINS are being changed to INNER JOINS.

Here's an example

LEFT OUTER JOIN USERPROFILE CSSPROFILE ON
USERPROFILE."USERTEXT2" = CSSPROFILE."USERTEXT2" AND
CSSPROFILE."USERTEXT1" = 'Inside Sales'

is changed to

INNER JOIN USERPROFILE CSSPROFILE ON USERPROFILE."USERTEXT2" CSSPROFILE."USERTEXT2" AND CSSPROFILE."USERTEXT1" = 'Inside
Sales'

 

[satinsilhouette]

what version are you on? I would say that crystal is picking up the joins from the database expert that is originally there. Do you have smart linking turned on?

LEFT OUTER JOIN USERPROFILE CSSPROFILE ON
USERPROFILE."USERTEXT2" = CSSPROFILE."USERTEXT2" AND
CSSPROFILE."USERTEXT1" = 'Inside Sales'

Where are you inserting the above links?

Thanks so much!
satinsilhouette

 

[twestfall]

I am using Crystal version 8.5. I did not use smart linking and I made sure in the database expert that all joins were setup as left joins. Given the example that I'm showing here, I don't think you could even set this up in the expert. I made these changes directly in the Show SQL Query. I cannot figure out why this statement is being changed. FYI - I used a SLXProfiler to show me the SQL statement that was executed for this report.

 

[Turkbear]

Hi,
Is
CSSPROFILE

the right-hand table?

If so, by applying a criteria to it, your effectively turned the LO join to an Inner one..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[twestfall]

I guess that make sense. However, when there is no find on that right table, I lose the table on the left thus destroying the entire query. I am not an experiened SQL programmer and could not find any other way, other than a Left Outer Join, to be sure that the entire query didn't fail becuase there was no record found on the table on the right. Is there anything else I can do?

 

[lbass]

I don't think you can change the Select or From clause in 8.5 in the "show SQL query" area, so you should try changing this in database->linking expert.

-LB