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)

Verified answer

Gunjan Bhattachayya 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
Verified answer

Martin Dráb 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.
mimi51340 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 ?
Martin Dráb 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.

Join the conversation

Community site session details

Add a filter in where clause

Helpful resources

Quick Links

Responsible AI policies

Pallavi Phade – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates