Show/Hide Toolbars

Navigation: Using ASPRunnerPro > SQL query screen

PHPrunner32x32     ASPRunnerPro manual


SQL tab

Scroll Prev Next More

About SQL tab

The SQL tab allows you to modify the SQL queries manually. The changes in this tab automatically appear in the Query Designer.

 

yellowbulbNote: if you modify the default SQL query, make sure that the key column(s) are included in the field list. This is required to provide the edit/delete functionality. If a table participates in a Master-Details relationship, make sure the link fields (primary/foreign key) stay on the list of fields.

 

sql_query_SQL1

 

To switch between the tables, use the Tables list panel on the left. The Find and Replace buttons let you quickly find the code within your query and modify it.

Limit data to first "N" rows

This feature allows you to limit the number of records to be displayed on the List, Print, Export, Details pages. It can be useful if you need to speed up the loading of the webpage; or when your chart has too many items in it, and you need to show only the most significant ones. It also works with the List pages added to the Dashboard.

 

The search and filters are applied first, and then the results are limited to first "N" rows.

 
This option works with all ASPRunnerPro project items except with the reports that have group fields selected.

What is supported

Aliases

When you connect to databases like DB2, Oracle or PostgreSQL, and your SQL query contains aliases, we recommend to enclose them in double-quotes. Here is an example:

 

select FieldName as "FieldAlias"
from TableName

 

If the field was assigned an alias in the SQL query, then the values array gets the alias instead of the field name from the database. E.g., if you have an SQL query SELECT salesrep_id AS Inv_Salesrep ..., you should use values("Inv_Salesrep").

 

yellowbulbNote: we do not recommend using aliases to give a field another name. If you have long or complicated field names, you can assign a label to the field on the Choose fields page or in the Label editor instead of using aliases.

Inner joins, outer joins

SQL query:

 

SELECT
carsmodels.id,
carsmodels.model,
carsmodels.make
FROM carsmodels
INNER JOIN carsmake ON carsmodels.make = carsmake.make

 

yellowbulbNote: it's recommended to use aliases for fields from joined tables to avoid confusion when two fields from different tables have the same name.

Calculated fields

SQL query:

 

SELECT
category,
color,
Date Listed,
descr,
EPACity,
EPAHighway,
features,
UserID,
YearOfMake,
zipcode,
Price*0.1 AS Discount
FROM carscars

 

In the example above, the alias Discount is assigned to the calculated field Price*0.1.

 

yellowbulbNote: if the field was assigned an alias in the SQL query, then the values array gets the alias instead of the field name from the database. So you should use values["Discount"] instead of values["Price*0.1"] in your events. For more information about events, see Events.

WHERE clause

SQL query:

 

SELECT *
FROM carscars
WHERE YearOfMake =2004

 

For more complicated queries, wrap the condition with parentheses:

 

SELECT *
FROM carscars
WHERE ( YearOfMake =2004 OR YearOfMake =2005 )

ORDER BY/GROUP BY clauses

SQL query:

 

SELECT
Make,
Model,
AVG (YearOfMake)
FROM carscars
GROUP BY Make, Model
ORDER BY Make

What is not supported

Stored procedure calls

 

Update/Delete/Insert/Create queries

 

Unions

 

DISTINCT keyword

 

 

yellowbulbNote: If your query doesn't work for some reason, create a view/query in your database and use this query as a datasource in ASPRunnerPro.

 

Here is how you can create a query in MS Access (other database types provide similar options):

 

1. Run MS Access and create a new query. Switch to SQL view.

 

2. Insert your SQL query there.

 

3. Save this query as qryNewQuery.

 

4. Run ASPRunnerPro and use qryNewQuery as a datasource table.