Using an SQL Query to get field\table info

[shabbarankers]

Hi,

Am I correct in thinking that I should be able to either create an SQL Expression Fields "value" which contains my SQL code and then use this on a report? Also are the joins in my tables relevant to the SQL query if the statement contains the join criteria?

For example I have tried to create an SQL Expression Fields value as below:

 SELECT `PO_Detail`.`PO`, `Source`.`Description`
 FROM   `Source` `Source` INNER JOIN `PO_Detail` `PO_Detail` ON `Source`.`PO_Detail`=`PO_Detail`.`PO_Detail`
[\code]

I get an error in compiling SQL expression. This code was copied from a temp report which I created to get me the values I was after. Can someone tell me what Im doing wrong please?
Thanks

 

[briangriffin]

I don't use SQL expressions much, mainly because I usually write SQL commands to return the entire dataset, but I'm pretty sure that a SQL expression can only return a single value.

It sounds like you would want to use your query as a command object - in the Database Expert you'll see Add Command as the first entry for your data connection. You can paste in any SQL statement that returns a dataset, then it will behave exactly as a table. You can base the entire report on it, or join it to other tables/views/commands to generate the data you need. There may be performance issues with certain combinations of these depending on what you're trying to do (which is why I usually base the entire report on a single SQL statement using a command object).

 

[hilfy]

To add to what Brian said, if you link a command to table or other commands, Crystal will pull ALL of the data into memory and do the joins there because it cannot pass it all down to the database. This is what causes reports with this configuration to run slowly. If you have a Selection Formula, that will also mostly be handled after the data has been joined in memory.

So, it can make a significant difference in the runtime of a report to use a single command to bring back all of the fields for the report instead of combining a command with tables or other commands.

Something else you want to do if you use a command - put all of the filtering criteria in the Where clause of the command. DO NOT use the Select Expert when working with a command! Again, all of the data will be pulled into memory and filtered there. It's more efficient to put the filter into the where clause and let the database do the work.

If you're using parameters in your filter, you'll need to delete them from the main report and recreate them in the Command Editor. Params created in the Command Editor have some additional internal properties and commands can't "see" the params from the main report. There a limits to what you can do with the parameter configuration in the Command Editor, but once they're created there, you can edit them in the Field Explorer in the main report. To use the parameters in your query, it will look something like this:

and myNumberOrDateField = {?MyNumberOrDateParam}
and myStringField = '{?MyStringParam}'
and myFile in {?MyMulti-ValueParam}

Notice that you have to explicitly include the quotes with the string parameter.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[shabbarankers]

Thanks both - I managed to sort it by using the command function. I've only just learnt this feature\function so I don't want to try and run before I can walk but thank you hilfy for the extended explanation - I will investigate