Using OR in QueryBuildRange value

(0) Share

Report

Posted on by Community Member

I have made q query and I want to use OR in the querybuildRange value. I need to use OR instead of AND.

The Querystring:

SELECT * FROM NCEmailMatrixTmp(NCEmailMatrixTmp_1) WHERE ((JobCode = '')) AND ((ItemId = N'FV47001-00'))

 while select documentTypeEmailMatrix
        order by Priority
        where   documentTypeEmailMatrix.DocumentType == _docType
        &&      documentTypeEmailMatrix.IsDefault    == false       
    {
        query = new Query();
        qbd = query.addDataSource(TableNum(NCEmailMatrixTmp));

   
        qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, JobCode));
        qbr.value(queryValue(documentTypeEmailMatrix.JobCode)); 
        
        qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, ItemId));
        qbr.value(queryValue(documentTypeEmailMatrix.ItemId));

                
        qr = new QueryRun(query);      
        
        while (qr.next())
        {
           
           qr.setRecord(emailMatrixTmp);
           return documentTypeEmailMatrix;
        }

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

You could use the advanced query expressions as explained here:

msdn.microsoft.com/.../aa893981.aspx

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

You code looks very strange. Why do you have two loops there, if you return from the function on the very first record, therefore no loop is needed? Why do you bother building the query if you already have a select statement there?

It seems that you can simply write single select statement, where you can easily use || operator, therefore your problem will disappear (together with most of your code).

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Hi Martin

I did it this way. And it solved the problem.

while select documentTypeEmailMatrix order by Priority where documentTypeEmailMatrix.DocumentType == _docType && documentTypeEmailMatrix.IsDefault == false { query = new Query(); qbd = query.addDataSource(TableNum(NCEmailMatrixTmp)); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, PostalCode)); qbr.value(queryValue(documentTypeEmailMatrix.PostalCode)); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, PostalCode)); qbr.value(queryValue('')); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, Id)); qbr.value(queryValue(documentTypeEmailMatrix.Id)); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, Id)); qbr.value(queryValue('')); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, JobCode)); qbr.value(queryValue(documentTypeEmailMatrix.JobCode)); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, JobCode)); qbr.value(queryValue('')); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, ItemId)); qbr.value(queryValue(documentTypeEmailMatrix.ItemIdMatrix)); qbr = qbd.addRange(FieldNum(NCEmailMatrixTmp, ItemId)); qbr.value(queryValue('')); qr = new QueryRun(query); qr.prompt(); while (qr.next()) { qr.setRecord(emailMatrixTmp); return documentTypeEmailMatrix; } }

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
If I didn't make any mistake, this is the same code refactored to a single select. As I explained, the loops are useless in your code. Also, using a single DB query is more efficient. And less code is easier to write, read, maintain and so on.

NCEDocumentTypeEmailMatrix emailMatrix; NCEmailMatrixTmp matrixTmp; select firstOnly emailMatrix where emailMatrix.DocumentType == _docType && emailMatrix.IsDefault == false exists join matrixTmp where matrixTmp.PostalCode == '' || matrixTmp.PostalCode == emailMatrix.PostalCode && matrixTmp.Id == '' || matrixTmp.Id == emailMatrix.Id && matrixTmp.JobCode == '' || matrixTmp.JobCode == emailMatrix.JobCode && matrixTmp.ItemId == '' || matrixTmp.ItemId == emailMatrix.ItemIdMatrix; if (emailMatrix) { return emailMatrix; }

You could still benefit from using the Query framework, because you could get rid of all the duplicate code by extracting it to a separate method, but without doing that, the select statement is much more readable.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Martin

You are right. But the reason why I use query franework is for entering wildcards such as * and ?.

I will try it. Thanks

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

Hi Mahyar,

You can use * in select statements as well, please refer for example

daxdude.blogspot.co.nz/.../use-x-wildcard-like-and-not-like-in-x.html

Was this reply helpful? Yes No