Sunday, January 9, 2011

How to use some of the lesser known methods of the SysQuery class

When working with ranges for a query, you may have used the SysQuery class already.
With some of the methods in this blog post you can live without, but they sure can make your life as a programmer a bit easier.

Some examples. We gonna use a querybuildrange, setting it like this:

QueryBuildRange myQBR;
myQBR=this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency));

Now we set the actual value for the range, by using this format:

myQBR.value('SetValue');

This method takes a string as argument.
But if you are not sure which type your argument is gonna be, you might wanna do a conversion to be on the safe side. It doesn't hurt to make a habit of writing your code like this:

myQBR.value(SysQuery::value('EUR'));

Hey and what about that other method from the Global class, QueryValue? No use of that?
Maybe you are used to writing something like this:

myQBR.value(queryValue('EUR'));

This converts the argument (anytype) to a string as well. Actually, this is equally good, as this method calls SysQuery::Value().


Logical NOT

If we wanna list all the customers that do NOT have the currency EUR set up, we can do something like this:

myQBR.value('!EUR');

The SysQuery class comes to the rescue here as well, by providing the exact same functionality

myQBR.value(SysQuery::valueNot('EUR'));

Check it out, this method converts the argument to a string AND adds an exclamation point in front of it.


No range value

If you range has been set up, but you don't want to apply anything yet, you can provide an empty string as argument. This way the query will retrieve all records.

myQBR.value('');

Again, SysQuery has something in its toolbox for us as well:

myQBR.value(SysQuery::valueUnlimited());

Coming from the documentation in the code:

Used when you want to have a completely open range, no limitations; "a blank range"
use this method for future compability


Don't mix this one up with the method valueEmptyString. The method valueEmptyString will retrieve records with no value for the specific field.

myQBR.value(SysQuery::valueEmptyString());

And this one of course has an opposite, valueNotEmptyString

myQBR.value(SysQuery::valueNotEmptyString());

This will retrieve records where there is a value set up, so no null values.


To end this post, maybe the method that saves you from writing code the most: Range.
We can set ranges from/to with SysQuery class as well.

myQBR.value(SysQuery::range('EUR','USD');

The method will add the dots ('..') when appropriate, so something like this:

myQBR.value('EUR..USD');

Like stated at the intro of this post: Nothing lifesaving, but nice-to-haves.

4 comments:

  1. Hi,
    i need filter by typre stringcolletion

    can you help me??
    example:

    System.Collections.Specialized.StringCollection l_ColCodici;

    ;

    query = new Query();
    qbds1 = query.addDataSource(tablenum(ALD_SFT_LISTINI_DETTAGLIO));
    qbr1 = qbds1.addRange(fieldnum(ALD_SFT_LISTINI_DETTAGLIO,CodiceListino));
    qbr1.value(I NEED USE STRINGCOLLECTION DATA TYPE);
    queryRun = new QueryRun(query);

    ReplyDelete
  2. Hi Gieole,

    I'm sure there is a better way than the following, but it works:

    public void init()
    { QueryBuildRange myQBR;
    System.Collections.Specialized.StringCollection myStringCollection;

    int counter;
    str myquerystr;
    int totalcounter;

    super();

    myStringCollection = new System.Collections.Specialized.StringCollection();
    myStringCollection.Add('xyz');
    myStringCollection.Add('abc');

    totalcounter=myStringCollection.get_Count();
    for(counter=1;counter<=totalcounter;counter++)
    {
    myquerystr+=myStringCollection.get_Item(counter-1);
    myquerystr+=',';
    }

    myQBR=this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,AccountNum));
    myQBR.value(myquerystr);
    }

    Don't use Query::Value(myquerystr) at the end, as this will mess up your OR range.

    ReplyDelete
  3. thanks for your help, maybe not the most elegant solution but it works :-))), thanks again.
    your blog is very useful to me, keep it up;-)
    bye
    G.

    ReplyDelete
  4. Hi
    How about myQBR.value('Like E*');
    Will that work?

    ReplyDelete