Not able to edit sql query??

[jhein]

I am using Crystal 9.0 and I have been building the query through Crystal by using the linking expert and select expert. The problem I am having is that I need to manipulate the sql a bit, and when I go through the menu options Database, Show SQL, I am not able to edit the query. Basically what I need to do is add an "and" clause to a left outer join that I set up. It doesn't seem like I am able to do this. Does anyone have any suggestions?

 

[kenhamady]

Copy the SQL statement, open the database expert and use the ADD COMMAND option above the list of tables. Past in your statement. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[jhein]

Ken,

I am having a situation where I can have the user entering a single job id and multiple names of people or multiple jobs and a single person's name. I like how Crystal handles the multiple entries for a single parameter. Will I still be able to accomplish this by entering my query into the add command tool.

 

[kenhamady]

You can adds parameters to either the report or the SQL command, but I don't think that command parameters support multiple values, so you are probably going to have those be report parameters, which don't get into the SQL.

What is the AND clause that you are trying to add in the command? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[jhein]

The join I want to perform will look something like this:

...
FROM table t1
LEFT OUTER JOIN table2 t2 ON t2.job_id = t1.job_id
AND t2.job_typ_id = 99

 

[kenhamady]

so you are trying to maintain all table 1 records, even those that have no 99s. Yes, you have to do that in the SQL which means using a command, which means that your multi parameters have to be used in the local report. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com