On the SQL tab you can
modify SQL query manually. All changes on this tab automatically
transfer to the Query Designer.
Note: if you
modify default SQL query, make sure that key column(s) are
included. This is required to provide edit/delete functionality. If
table participates in Master-Details relationship make sure link
fields (primary/foreign key) stay on the list of fields.
To switch between tables use Tables list panel on the left.
Find and Replace buttons let you quickly search
within your SQL code and modify it.
Inner joins, outer joins
INNER JOIN carsmake ON carsmodels.make = carsmake.make
recommended to use aliases for fields from joined tables to avoid
confusion when two fields from different tables have the same
Price*0.1 AS Discount
In the example above the alias Discount is assigned to the calculated
field Price*0.1. Note that
if the field was assigned an alias in the SQL query, then the
$values array will get the
alias instead of field name from the database. So you should use
instead of $values["Price*0.1"] in your events.
For more information about events, see Events.
We do not recommend using aliases to give a field another name.
If you have very long or complex field names, you can assign a
label to the field on Choose fields
page or in the Label editor instead
of using aliases.
WHERE YearOfMake =2004
For more complicated queries wrap the condition by
WHERE ( YearOfMake =2004 OR YearOfMake =2005 )
ORDER BY and GROUP BY
GROUP BY Make, Model
ORDER BY Make
When you connect to databases like DB2, Oracle or Postgre and
your SQL query contains aliases, we recommend to enclose them in
double quotes. Here is an example:
select FieldName as "FieldAlias"
What is not