Hi If I want to get the intersection of ‘AB’ and ‘PQ’, how should I set the range or what should I write in queryBuildRange.value(…). Thanks
Hi Krish, There is an article in technet on Query Ranges. Here is an excerpt - … Expressions in Query Ranges This article shows an example on how it is possible to use expressions as query range values. Expressions in query range values makes it possible to express more complex queries, than it is possible to express with the basic relations and the dot-dot notation. If you want to create a query that selects the records from MyTable where field A equals x or field B equals y, you have to use an query range value expression. If you could use an select statement it would look like this: select myTable where myTable.A == x || myTable.B == y; In a query, you do it like this: Add a range on field A. Set the value of that range to the expression (if x = 10 and y = 20), as a string: ((A == 10) || (B == 20)) The rules for such an the expression are as follows: • Enclose the whole expression in parentheses. • Enclose all sub expressions in parentheses. • Use the relational and logical operators you know from x++. • Use field name only for fields in the ranges data source. • Use the dataSource.field notation for fields from other data sources in the query. Query range value expressions can be used in any query, so you can use it in the query box when filtering records on a form, from x++ or anywhere else. Values have to be constants in the expression, so any function or outside variable has to be calculated before the expression are evaluated by the query. This is normally done using strFmt. The example above will then look like: strFmt(’((A == %1) || (B == %2))’,x,y) To get complete compile time stability you should use intrinsic functions to get the correct field names, like this: strFmt(’((%1 == %2) || (%3 == %4))’, fieldStr(MyTable,A), x, fieldStr(MyTable,B), y) For example can you in a query in a customer form specify all the customers that has a search name different from the name: (nameAlias != name) As the contents of the Query range value expressions should look like x++, you should be aware that you format the different data types correctly. This expression list the records that are modified or created since September, 18: ((modifiedDate >= 18\09\2000) || (createdDate >= 18\09\2000)) In x++ you can use the global method date2StrXpp to format at date to the correct x++ format, and use for example int2str to convert an enumerated value to the suitable numeric format. Be aware that query range value expressions only are evaluated at runtime (so they aren’t checked at compile time). If the expression isn’t understandable to the system, a modal box will appear at runtime stating “Unable to parse the value”. … Regards, Harish Mohanbabu
Thanks Harish I am OK with your example. But if I have the following requirement; How can this be handled? Query q; QueryBuildDataSource qds; ; q = new query(); qds = q.addDataSource(tableid); qds.addrange(fieldid).value(‘ab,pq’); This range will give the following data set Select * from tablename where (fieldname like ab or fieldname like pq) But my requirement is to get the data set Select * from tablename where (fieldname like ab and fieldname like pq) i.e all the records where fieldname will contain both ab & pq; that too using QueryBuildDataSource; not by writing sql statement. Krish
Hello, Try this, qds.addrange(fieldid).value(‘abpq*,pqab*’); /Peter Karlsson
I have used like this
qbr.value(strFmt(’(%1)’,FinalValue);
FinalValue is a string which contains the whole expression.
I am getting the query in the debugger like this.
SELECT * FROM SalesTable WHERE ((((CustAccount == 1101) && (DeliveryDate == 30\11\2009) && (DeliveryDate >= 29\10\2007) && (DeliveryDate <= 30\11\2009))))
But I am not getting the output.
When I execute the same in a job I am getting the output. Please tell me how to proceed.