Modifying SQL in RAP

rbuser

I am using Report Builder Enterprise 11.05. I am converting my existing
reports to use Report Explorer, End User Report Designer and RAP.

I wish to create a report based on a query similar to the one below.

SELECT
Client.ID,
ClientEpisode.EpisodeDate,
(SELECT Result FROM TestResult AS TR1
WHERE TR1.ClientEpisodeID = ClientEpisode.ID
AND TR1.TestNo = 1) AS Test1

FROM
Client
INNER JOIN ClientEpisode
ON Client.ID = ClientEpisode.ClientID

WHERE ClientEpisode.ID = :ClientEpisodeID;

It appears that I cannot create the query below in the report designer
because of the SELECT clause in the 3rd column. I can enter the SQL
statement directly into the SQL tab of the Query Designer. However, it
disapproves of the parameter in the WHERE clause.

I wish to use the report's parameter to assign a value to the
ClientEpisodeID parameter for the above query.

I thought it would be possible to use the OnInitializeParameters event
to build the required SQL.

procedure ReportOnInitializeParameters(var aCancel: Boolean);
var
aParameter: TppParameter;
S: string;

begin
if Report.Parameters.Count = 1 then
begin
aParameter := Report.Parameters.Items['ClientEpisodeID'];
// Assign the required SQL text to something

end;

aCancel := true;
end;

Doe you have any suggestions on how I can create a report based on the
above query?

Richard Harding.

nicocizik

Hi Richard,

You should be able to construct the entire SQL query you have below using
the TdaSQLBuilder object.

1. For the sub-select, simply use the TdaSQLCalcFields.AddExpression
routine and enter the select statement directly as the "expression"
parameter.

2. For the Join, use the TdaSQLSelectTables.AddJoin routine.

3. For the parameterized search, you can use the
TdaSQLCriteriaList.AddAutoSearchWithParam or AddParamSearch routines.

See the RB help on the above objects for more information and example code
and take a look at the following articles...

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thanks Nico.

I did make some progress but come unstuck a bit further along.
Here is a portion of the query - a little changed from last time.

SELECT
Client.ID, Client.LastName, Client_2.LastName LastName_2,
(SELECT Result FROM TestResult
WHERE TestResult.ClientEpisodeID = ClientEpisode.ID
AND TestResult.TestNo = 1) AS Test1
FROM
ClientPractitioner ClientPractitioner
RIGHT OUTER JOIN Client ON
(Client.ID = ClientPractitioner.ClientID)
LEFT OUTER JOIN Client Client_2 ON
(Client_2.ID = ClientPractitioner.PractitionerID)
INNER JOIN ClientEpisode ON
(ClientEpisode.ClientID = Client.ID)


Changing Table Aliases
----------------------
Is it possible to change the Table Aliases "Client" and "Client_2"? It
does not appear to be possible in the "Tables" tab of Query Designer.

CROSS JOINS
-----------
If a Table is added to the query, it appears that a JOIN operator is
required. Is it possible to have a FROM clause with 2 tables without an
operator? For example, FROM TableA, TableB.


I am converting existing reports to use RAP and DADE. Most of the time
it is very easy. I am only having trouble with a few reports. Is it
possible to directly assign the SQL (to something) without using
TdaSQLBuilder to construct it?

In the Query Designer, it is possible to directly enter the SQL. Can I
do the same in RAP?


----------
Richard Harding

nicocizik

Hi Richard,

In some cases (as the queries become more advanced) it may be easier to edit
the SQL as text like you mention.

You can access the SQL object using the TdaSQLBuilder as well. Setting the
TdaSQLBuilder.SQL.SQLText property will directly edit the SQL text sent to
the DB. This is also possible from RAP.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Thank you Nico.

It is working now.

Below is the code in case anyone is interested for future reference.

procedure ReportOnInitializeParameters(var aCancel: Boolean);
var
aSQLBuilder: TdaSQLBuilder;
aParameter: TppParameter;

begin
{ Append WHERE clause to existing SQL }
if Report.Parameters.Count = 1 then
begin
aParameter := Report.Parameters.Items['ClientEpisodeID'];
aSQLBuilder := TdaSQLBuilder.Create(ClientEpisode);
aSQLBuilder.SQL.SQLText.Text :=
aSQLBuilder.SQL.SQLText.Text +
' WHERE ClientID = ' +
IntToStr(aParameter);
aSQLBuilder.ApplyUpdates;
end;

aCancel := false;
end;


Richard Harding