Home DADE
New Blog Posts: Merging Reports - Part 1 and Part 2

AddCriteriaField on joined query

edited August 2012 in DADE
Hi
We are using a JOIN query on 2 tables INVHEAD andINVITEMS
field S_Code is in INVITEMS.

As soon as we try to add S_CODE in the code below we get an access
violation. If we just add an extra where clause the it can access the joined
table ok but would rather use AddCriteriaField. We can do this by adding a
second DataView but would rather avoid this is possible.

Is it possible to acheive what we are tying ?
Thanks
Andy Dyble

lTbl := lQueryDataView.SQL.GetTableForSQLAlias('INVHEAD');
//works fine
lQueryDataView.SQL.AddCriteriaField(lTbl, 'sales_acc',
dacoGreaterThanOrEqualTo, cbAccFrom.Text);
lQueryDataView.SQL.AddCriteriaField(lTbl, 'sales_acc',
dacoLessThanOrEqualTo, cbAccTo.Text);
// gives Access Violation
lQueryDataView.SQL.AddCriteriaField(lTbl, 'S_CODE',
dacoGreaterThanOrEqualTo, edtS_CodeFrom.Text);
lQueryDataView.SQL.AddCriteriaField(lTbl, 'S_CODE',
dacoLessThanOrEqualTo, edtS_CodeTo.Text);

Comments

  • edited August 2012
    Hi Andy,

    Which version of ReportBuilder and Delphi are you using? What error are
    you receiving? The AddCriteriaField routine is not necessarily designed
    to be used in this way. SelectCriteria would be a better option.

    Rather than accessing the SQL object directly, I recommend using the
    TdaSQLBuilder class to perform runtime tasks on your datasets. The
    SQLBuilder class gives easy-to-use routines to access and manipulate the
    SQL object without the need to worry about timing or using the correct
    TdaSQL routines.

    In your case you would want to use the TdaSQLBuilder.SearchCriteria.Add
    routine to add your search criteria in code.

    See the help topic on the TdaSQLBuilder class in the RB help as well as
    the articles below.


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


    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2012
    D7 rb 9.
    We have hundreds of systems using the same method with an end user report
    desgner, it would be a masive change.
    The way we have works beautifuly.
    I'll get someone to look into it though. But can we access the joined table
    using our method ?

    Andy

  • edited August 2012
    Hi Andy,

    I believe your method will still work, you just need to access the
    proper table. Currently you are looking in the INVHEAD table for a
    field that does not exist.

    lTbl := lQueryDataView.SQL.GetTableForSQLAlias('INVHEAD');

    lQueryDataView.SQL.AddCriteriaField(lTbl, 'sales_acc',
    dacoGreaterThanOrEqualTo, cbAccFrom.Text);
    lQueryDataView.SQL.AddCriteriaField(lTbl, 'sales_acc',
    dacoLessThanOrEqualTo, cbAccTo.Text);

    lTbl := lQueryDataView.SQL.GetTableForSQLAlias('INVITEMS');

    lQueryDataView.SQL.AddCriteriaField(lTbl, 'S_CODE',
    dacoGreaterThanOrEqualTo, edtS_CodeFrom.Text);
    lQueryDataView.SQL.AddCriteriaField(lTbl, 'S_CODE',
    dacoLessThanOrEqualTo, edtS_CodeTo.Text);

    Moving forward however, I recommend taking a look at the SQLBuilder as
    the above code is fairly confusing.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited August 2012
    Brilliant, thanks. WOrks a treat.

    I was making a second tbl var and doing.

    lTbl2 := lQueryDataView.SQL.GetTableForSQLAlias('INVITEMS');

    Andy

This discussion has been closed.