using SQL functions in QueryBuildRange

Finance and Operations/AX Developer Forum
1

Is there a way to use SQL functions (like LEFT() and RIGHT()) in a QueryBuildRange?

(I’m pretty sure the answer to this question is ‘no’, but I thought I’d ask anyway.)

I want to filter a query for a form that’s used to populate a drop-down, so expired credit cards don’t show. And the credit card expiration dates are stored as a string, in form “MMYY”.

So I want to do something like this:

ds = custCreditCard_ds.query().dataSourceTable(tableNum(CreditCardCust));
qbr = ds.addRange(fieldNum(CreditCardCust, ExpiryDate));
qbr.value(’(RIGHT(EXPIRYDATE, 2) > “14”) || ((RIGHT(EXPIRYDATE, 2) == “14”) && (LEFT(EXPIRYDATE, 2) >= “12”))’);

…but of course that doesn’t work.

I’m trying to think up a creative way to do this, and I’m not coming up with one, short of adding a new field to the table, storing the expiration date as an actual date, or at least reversed to “YYMM”, so I can do a simple >= on it.

Any interesting ideas would be welcome!

2

It’s not possible, as far as I know.

The root cause is in your data model. You’re trying to treat your data as two integer values, but you don’t store them as two integer values. Instead of that, you put both value to a single field (violation of the first normal form) and save them as string (usually a bad idea for any non-textual data). An alternative would be using a date field (ignoring the “date” part and working with month and year only), as you already mentioned.

A workaround would be using a view with computed columns, nevertheless I wouldn’t do that. The data model clearly needs fixing.

3

Thanks Martin. Yeah, fixing the data model would clearly be best. The table isn’t really one I can easily change, without breaking a bunch of other stuff, unfortunately. But maybe I can come up with a workaround of some sort.