Show SQL Query V9+

[wolverinec7]

Hi, i have a very complex report saved in v11 and i need to alter the SQL query directly rather than using the select expert.

The reason being i cant link 2 tables directly as one is a date, the other a date time field. if i do this in the select statement it brings through every record which times out when i publish to enterprise so users cant run this ad hoc.

i've noticed that i can edit the SQL query in v8.5 but not in anything above and i really dont fancy rewriting this in 8.5. any ideas?

 

[lbass]

You can copy the show sql query into "add command" area in database->database expert->your datasource->add command. Then manipulate it as you wish. However, I believe you then have to change all references in your report to fields in the command.

-LB

 

[wolverinec7]

lbass thanks for the reply but i can't find the 'add command' area when i go into database expert. i'm in version 11 and when i bring up the expert it just gives me the options to add tables then link those on the other tab

 

[GJParker]

i would check again if I were you,

add command is the first option in the list directly below the connection before the db tables and views

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[wolverinec7]

thanks gary found it, it totally crashed crystal so i think its just a re-write in 8.5

 

[lbass]

What do you mean by "crashed"? You should be able to copy a query into the add command area of a new report, although you might have to adjust it. If you copy it into an existing report, you would have to be in design mode and then systematically replace all existing fields, formulas, etc. to use the command fields, and then remove the now unused tables from the report.

If you want help troubleshooting, you should copy the query into the thread and also identify your datasource.

-LB