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

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

Gunjan Bhattachayya 35,423 on at

Like (0)

Report

Hi mimi,

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

Was this reply helpful? Yes No
Verified answer

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

Like (0)

Report
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.

Was this reply helpful? Yes No
mimi51340 50 on at

Like (0)

Report

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

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

Like (1)

Report

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.

Was this reply helpful? Yes No

Join the conversation

Community site session details

Add a filter in where clause

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

Add a filter in where clause

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates