Complex Query range with combined && clause

(0) Share

Report

Posted on by Community Member

Hello

I would like to add a range to a query in order to obtain the following equivalent result in SQL :

select * from CUSTTRANS where not((custtrans.TRANSTYPE = 15) and (CUSTTRANS.LASTSETTLEVOUCHER = ''))

Here is what I have done so far, but does not return the correct query :

str rangeStr;

    rangeStr = strFmt('!((%1 == %2) && (%3 == ""))',
        fieldStr(CustTrans, TransType),
        any2int(LedgerTransType::Payment),
        fieldStr(CustTrans, LastSettleVoucher));
    
    CustTrans_ds.query().dataSourceTable(tablenum(CustTrans)).addRange(fieldnum(CustTrans, TransType)).value(rangeStr);
    super();

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

First of all you need braces around the whole advanced query criteria. Then try negating the two brackets individually, see if that works. Something like: (!(A == B) && !(B == C))

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I get a different result, but still doesn't work. It also provokes the following error : Query extended range failure: The '!' operator can only be used with the 'like' keyword.

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report
Hi Pellandk,

Try this

rangeStr = strFmt('((%1 != %2) && (%3 != ""))', fieldStr(CustTrans, TransType), any2int(LedgerTransType::Payment), fieldStr(CustTrans, LastSettleVoucher));

Was this reply helpful? Yes No
Verified answer

Vilmos Kintera 46,149 on at

Like (0)

Report

Right, you can only negate a LIKE statement, but you can use the not equal like Ievgen suggested in advanced query expressions.

Was this reply helpful? Yes No
Cengiz UCAR on at

Like (0)

Report

Hi,

When you pass ! into brace && should become || (I mean AND becomes OR)

Regards,

Was this reply helpful? Yes No