SQL String Functions in X++ (RIGHT, LEFT)

(0) Share

Report

Posted on by Mark88

Hi,

I am working on a RDP report that needs to give a count of the number of bottles by size. The size of the bottle is noted in the itemID field which has 010A, 010B, etc...where the last letter (A/B) specifies the size. However, I can't seem to find a way to use the RIGHT function within X++ to pull just the last letter of that itemID field. I know there is a way to execute SQL directly from AX, but I'm not sure if this would be viable.

For example, I have: SELECT Count(itemID), Right(itemID, 1) From TableName....(in SQL)

So I'd get

Count Size

10 A

15 B

5 C

Eventually, I need to push the count qty into fields in my temp table, which has fields such as SizeA, SizeB, SizeC, all containing the corresponding quantities.

Any help would be excellent and much appreciated!

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

PA-22040759-0 6,194 on at

Like (0)

Report

I think you'd have to make repetitive selects to obtain something like what you ask for. I'm thinking something like:

select count(RecId) from TableName where TableName.ItemID LIKE '*A';

select count(RecId) from TableName where TableName.ItemID LIKE '*B';

etc.

The RecId field will hold the count for each statement.

If you want a prettier solution you could reuse a query object to fire these select statements. Here is a good example of a query using LIKE: www.dynamics-blog.com/.../using-like-in-axapta-x-query.html

I'd not recommend fiddling with direct SQL statements from AX. The most important reason being that you'd bypass all AX security.

Interesting question.

Was this reply helpful? Yes No
Mark88 on at

Like (0)

Report

Hi Palle,

Thanks for the reply.

I've tried your suggestion using:

select count(RecId) from inventSum where inventSum.ItemID LIKE '*A';

and also I've tried using count(itemID) instead of the RecId; however, when I try to assign this count value ot the temp table, I get the error that "assignment loses precision"

So,

select count(RecId) from inventSum where inventSum.ItemID LIKE '*A';

inventItemByBottleSizeTmp.SizeA = inventSum.RecId;

I'm assuming it's something to do with the actual count value being stored...when I used itemID, the error said "operand type incompatible"

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

Like (0)

Report

The problem is that you're trying to assign int64 value to int field (I assume that SizeA is int); you can explicitly convert it by int642int(). By the way, I thought that "assignment loses precision" is a warning, not an error.

Back to your original question - you can extract thelast character by subStr() (with a negative third parameter).

Nevertheless the root of your problem is that you violated one of basic rules of database normalization (First normal form) - the attribute is not atomic. If you defined size as a separate attribute, the aggregation would be trivial.

Was this reply helpful? Yes No

Community site session details

SQL String Functions in X++ (RIGHT, LEFT)

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

SQL String Functions in X++ (RIGHT, LEFT)

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 > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates