Add a filter in where clause

(0) Share

Report

Posted on by mimi51340

Hello,

I would like to create a filter like below but I think substr method is not possible in the where clause.

How can I manage it ?

while select * from vendTrans
        join tri_NettingCorrelationAP
           where
              vendTrans.AccountNum == tri_NettingCorrelationAP.TRI_PurchAccount
              && (substr(vendTrans.Voucher,1,3) == "AFG" || substr(vendTrans.Voucher,1,2) == "FF" || substr(vendTrans.Voucher,1,3) == "AF")
              && vendTrans.createdDateTime >= this.dateCompare(SystemDateGet()-365)

Thanks

All responses (4)

Answers (2)

Martin Dráb 231,837 Most Valuable Professional on at

Like (1)

Report

RE: Add a filter in where clause

If you have difficulties to query your data, it usually means that your data model isn't designed correctly. It doesn't support well what you need from it.

In this case, you seem to be using a part of the voucher ID to categorize transactions, but this categorization should be ideally stored in a separate field. Filtering by such a field would be trivial.
mimi51340 50 on at

Like (0)

Report

RE: Add a filter in where clause

Thanks what do you think by Nevertheless it seems that the root cause is in the data model ?
Verified answer

Martin Dráb 231,837 Most Valuable Professional on at

Like (0)

Report
RE: Add a filter in where clause

Using 'like' sounds like the right approach to me:

while select vendTrans join tri_NettingCorrelationAP where tri_NettingCorrelationAP.TRI_PurchAccount == vendTrans.AccountNum && ( vendTrans.Voucher like 'AF*' || vendTrans.Voucher like 'FF*')

Nevertheless it seems that the root cause is in the data model.
Verified answer

Gunjan Bhattachayya 35,421 on at

Like (0)

Report

RE: Add a filter in where clause

Hi mimi,

If you want to use subStr, you can check into using if condition. Otherwise, you can try using like operator