Support Forum - SQL in a report

08/26/2020 13:12 Gordon Flint wrote:

I have a product using a MSSQL database. I'm wondering if there is a way of
embedding a SQL select to act as a filter, creating and modifying the select at
runtime.

So for example I have a basic report on a client table, the table relates to
many tables, one of which is notes. Is there a way to embed a SQL command at
runtime so I only include clients that have at least one entry in the notes
table. I'm not asking for the SQL commands themselves, just whether there is a
mechanism to embed a SQL select on another table as part of a record filter. The
query table may or may not be included in the list of tables added to the
RunTimeReport procedure.

Any help would be greatly appreciated.

08/26/2020 13:56 Oleg Fomin wrote:

Yes, Clarion has built-in capability documented as follows:

SQL(sql expression)

SQL
 Specifies a proper SQL expression to be used to affect the records in a VIEW.
 
sql expression
 A string expression containing valid SQL code.

SQL specifies a valid SQL statement which is applied to a FILTER. Anything
specified in the sql expression is treated as pure SQL code.
 
When you are using the SQL statement with a view you need to be aware of how the
SELECT statement is generated. By default, the view engine puts an SQL alias on
each file in a VIEW. The SQL driver generates an SQL statement which uses an
Alias of "A" for the first file in the View, "B" for the second etc. If you wish
to use SQL, your filter has to be compatible with the previously generated SQL
statement  i.e., you need use A/B/etc as the file prefixes. If necessary, you
can use PROP:Alias to change this behavior. Using the default configuration the
following SQL() commands in a filter are illegal, and must be aliased prior to
opening the VIEW:

Name:
E-Mail: (will not be published)
Subject:SQL in a report


Message body:

Verification code:

Please, do not include any URLs into the message.
Otherwise, your message will be rejected and IP blacklisted automatically.
Off topic messages will be deleted by moderator.