希望各位可以提供更多的资料来完善本帖.
在开始使用QueryBuildRange进行与、或、等于等关系时,并不是想象中那么简单,本文章结合网络上的资料和我自己的实践总结了一些queryBuildRange值及表达式的使用方法。
一. 单一值作为过滤条件,如
Select inventtable where itemid = “aa”
可以使用如下代码
static void Job55(Args _args)
{
Query query;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryRun qr;
str qs;
str s;
InventTable inventTable;
;
qs = '"aa"';
query = new Query();
qbds = query.addDataSource(tablenum(InventTable));
qbr = qbds.addRange(fieldnum(InventTable,ItemId));
qbr.value(qs);
qr = new QueryRun(query);
while(qr.next())
{
inventTable = qr.get(tablenum(InventTable));
s = s + inventTable.ItemId + ' ';
}
print s;
pause;
}
这里需要注意一点,如果是字符串型的值,一般用(“)将值括起来,如果是数,则不需要,对于枚举需要转换成整数。
二. 使用两个不同字段的值以并的关系过滤,如
Select inventDim where color=”Gold” and size = “30”
只需要建立两个Range并分别添加过滤条件,如下:
static void Job55(Args _args)
{
Query query;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryRun qr;
str qscolor;
str qssize;
str s;
InventDim inventDim;
;
qscolor = '"Gold"';
qssize = '"30"';
query = new Query();
qbds = query.addDataSource(tablenum(InventDim));
qbr = qbds.addRange(fieldnum(InventDim,InventColorId));
qbr.value(qscolor);
qbr = qbds.addRange(fieldnum(InventDim,InventSizeId));
qbr.value(qssize);
qr = new QueryRun(query);
while(qr.next())
{
inventDim = qr.get(tablenum(InventDim));
s = s + InventDim.InventDimId + ' ';
}
print s;
pause;
}
三、最简单也是最方便的或,如
Select inventTable where itemId = “aa” or itemId = “11”
可以使用如下代码:
static void Job55(Args _args)
{
Query query;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryRun qr;
str qs;
str s;
InventTable inventTable;
;
qs = '"aa","11"';
query = new Query();
qbds = query.addDataSource(tablenum(InventTable));
qbr = qbds.addRange(fieldnum(InventTable,ItemId));
qbr.value(qs);
qr = new QueryRun(query);
while(qr.next())
{
inventTable = qr.get(tablenum(InventTable));
s = s + inventTable.ItemId + ' ';
}
print s;
pause;
}
四、实现字段间的或、与及组合过滤,如实现:
Select inventTable where itemId = “aa” or itemName = “admin”
首先需要给QueryBuildDataSource添加一个Rrange,具体添加那一个字段都可以,然后方法有三:
方法一:
Range的value需要满足以下条件:
1.整个表达式必须要' '引起来而不是" ";
2.这个表达式必须用()括起来;
3.每个子表达式必须用各自的()括起来;
4.对于当前表中的字段,用字段名直接引用即可;
5.对于其他表中的字段,引用时需要添加DataSource Name作为前缀;
6.string类型的值需要用" "引起来,也可包含在queryValue()中;
7.枚举类型的值需要用对应的int类型指定;
8.Date类型的值需要用Date2StrXpp()转化
代码如下:
static void Job55(Args _args)
{
Query query;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryRun qr;
str qs;
str s;
InventTable inventTable;
;
qs = strfmt('((%1== "%2") || (%3=="%4"))',
fieldStr(inventTable,ItemId),"aa",
fieldStr(inventTable,ItemName),"admin");
query = new Query();
qbds = query.addDataSource(tablenum(InventTable));
qbr = qbds.addRange(fieldnum(InventTable,ItemId));
qbr.value(qs);
qr = new QueryRun(query);
while(qr.next())
{
inventTable = qr.get(tablenum(InventTable));
s = s + inventTable.ItemId + ' ';
}
print s;
pause;
}
方法二:
字符串还是需要用 “ “ 括起来,其它符号用 ‘ ‘ 括起来,注意事项如方法一
static void Job55(Args _args)
{
Query query;
QueryBuildRange qbr;
QueryBuildDataSource qbds;
QueryRun qr;
str qs;
str s;
InventTable inventTable;
;
qs= '(' + fieldid2name(tablenum(inventTable),fieldnum(inventTable,Itemid))+ ' = "'+ '11'//queryvalue('aa')
+'/") || ('
+fieldid2name(tablenum(inventTable),fieldnum(inventTable,itemId))+ '=='+'"aa"'+')'
;
query = new Query();
qbds = query.addDataSource(tablenum(InventTable));
qbr = qbds.addRange(fieldnum(InventTable,ItemId));
qbr.value(qs);
qr = new QueryRun(query);
while(qr.next())
{
inventTable = qr.get(tablenum(InventTable));
s = s + inventTable.ItemId + ' ';
}
print s;
pause;
}
方法三:
来自系统的标准方法,另一种表达方式,系统路径:Forms-->Adress-->DataSource Method addQuerySalesQuotationTable
void addQuerySalesQuotationTable(SalesQuotationTable _salesQuotationTable)
{
QueryBuildDataSource queryBuildDataSource;
CustTable custTable;
str queryExpression;
;
queryBuildDataSource = this.query().dataSourceTable(tablenum(Address));
custTable = CustTable::Find(_salesQuotationTable.CustAccount);
queryExpression = '(((' +
tableid2name(tablenum(Address)) + '.' + fieldid2name(tablenum(Address), fieldnum(Address, AddrTableId)) +
' == ' + queryValue(_salesQuotationTable.TableId) +
') && (' +
tableid2name(tablenum(Address)) + '.' + fieldid2name(tablenum(Address), fieldnum(Address, AddrRecId)) +
' == ' + queryValue(_salesQuotationTable.RecId) +
')) || ((' +
tableid2name(tablenum(Address)) + '.' + fieldid2name(tablenum(Address), fieldnum(Address, AddrTableId)) +
' == ' + queryValue(custTable.TableId) +
') && (' +
tableid2name(tablenum(Address)) + '.' + fieldid2name(tablenum(Address), fieldnum(Address, AddrRecId)) +
' == ' + queryValue(custTable.RecId) +
')))';
queryBuildDataSource.addRange(fieldnum(Address, Address)).value(queryExpression);
}