Home DADE

Modify SQL at runtime when EditSQLAsText is TRUE

If, for optimization reasons, a report needs its SQL handcrafted (EditSQLAsText = True) then I can still pass single values to it's WHERE clause at runtime with parameters. However, this falls down with INLIST (IN) WHERE clauses where effectively I need to pass a comma separated list of values; in fact I do this with TdaSQLBuilder already in non tampered with SQL report. However, in this scenario I can't use TdaSQLBuilder to do this as SearchCriteria.Count will be 0 by virtual of EditSQLAsText = True; so I have been trying to got a bit deeper along the lines of:

lDataModule := daGetDataModule(aReport);

if assigned(lDataModule) then
begin
lDataView := TdanxQueryDataView(lDataModule.DataViews[0]);
ldaSQL := lDataView.SQL;
ldaSQLText := TdaSQLText(ldaSQL.SQLText);

if assigned(ldaSQLText) then
begin
ldaSQLText.QueryExpression.WhereClause.SearchItems[0].Expression2 := aFilterList.CommaText;
TdaSQLTextAccess(ldaSQLText).Changed;
end;
end;

However, the edits I make to Expression2 are not recognised when TdaSQLTextAccess(ldaSQLText).Changed is called. I've been trying to get me head around the token parsing code but I think I'm better cutting to the chase and asking the experts here if there is a way to achieve what I want. Thanks, Paul.

Comments

  • edited March 2020
    Hi Paul,

    Define a Report.Parameters[ ] item and set the DataType. Configure AutoSearchSettings SearchOperator to poInList and SearchExpression to a comma delimited list.

    Example:

    ppReport1.Parameters['paCustNos'].AutoSearchSettings.SearchExpression := '1221, 1551';


    SELECT customer.Company,
    customer.CustNo
    FROM customer
    WHERE ( customer.CustNo IN :paCustNos )


    RB resolved the above to this..

    SELECT customer.Company,
    customer.CustNo
    FROM customer
    WHERE ( customer.CustNo IN (1221,1551) )



    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • Wow, even old hands can learn something new! Or is this a recent enhancement as I'm sure I've been around this loop a few years ago.
Sign In or Register to comment.