AddCriteriaField on joined query

rbuser

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);

nicocizik

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

rbuser

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

nicocizik

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.

rbuser

Brilliant, thanks. WOrks a treat.

I was making a second tbl var and doing.

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

Andy