Microsoft Dynamics AX (Archived)

How to use AX built in functions in select query

(0) Share

Report

Posted on by Community Member

Hi All,

Consider a table customer as below

AccNo Name

--------------------------------

1-101-1111 Sam

2-101-1112 Tom

3-201-1113 Tim

4-202-1114 Jim

5-203-1012 Jack

--------------------------------

I want a select query to get Names of customer having "101" from 3rd to 5th position.

I can achieve the above requirement as below

customer cus;

;

while select cus

{

if(subStr(cus.AccNo,3,3) == "101")

{

// do something

}

But i need something as below so that i can reduce the iteration of records.

customer cus;

;

while select cus

where subStr(cus.AccNo,3,3) == "101"

{

// do something

}

Thanks in advance,

Sai

*This post is locked for comments

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

sshaheen 127 on at

Like (0)

Report

You could use the 'like' keyword and wildcards to put a range on your query. It may not be the perfect solution, but it will limit your selection to only those records with have the value '-101-' in them somewhere.

while select cus

where cus.AccNo like "*-101-*"

{

if(subStr(cus.AccNo,3,3) == "101")

{

// do something

}

}

Was this reply helpful? Yes No
Suggested answer

Dick Wenning 8,705 Moderator on at

Like (0)

Report

use SQL ejection with examples like

stackoverflow.com/.../select-statement-with-substr-in-where-clause

sql injection

msdn.microsoft.com/.../statement.executeupdate.aspx

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

while select cus

where cus.AccNo LIKE '?-101-*'

? represents any ONE character, * zero or more characters.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi All,

Thanks for ur answers...!!!

I want to use a function on a table column in where condition. Suppose I have some date of birth column in the same table if a need to use a function year on dob column.

Is it possible to apply some built in ax functions on table column in select statement?

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

Like (1)

Report
You can't expect SQL Server to call some X++ methods. If you wrote direct SQL code, you would be able to to call builtin functions of SQL Server, but that's also not possible from AX, with two exceptions:

Using a computed column in a view

Calling a "direct" SQL query (e.g. through Statement class in AX or a framework in .NET, such as Entity Framework)

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

No, in X++ SQL query it is not possible.

You can have this kind of behaviour using native SQL statement execution

Create a connection object

Create SQL string

execute SQL command, which will give you the ResultSet

Loop through the ResultSet and implement the logic

Here in this SQL string, again you will not be able to use X++ functions you need to use SQL functions to implement the logic.

Please verify and let me know if you need further technical support in implementing above solution

Was this reply helpful? Yes No
Dick Wenning 8,705 Moderator on at

Like (0)

Report

Sai.peddinti

could you please verify the used answer, there are a lot of good solution above, they all are different.

just verify the one, you have used.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks all for your time...

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi

I am trying to insert into temp table for generating report through data provider using class but getting syntax error

//

insert_recordset WorkflowTrackingStatusTableTemp (WORKFLOWTRACKINGSTATUSTABLERECID,Purchreqid1)

select RECID, subStr(document,22,13) from WORKFLOWTRACKINGSTATUSTABLE

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

Like (0)

Report

Avinash, have you read the discussion above? If so, you should already know that you can't use substr() there and why and which workaround you have available.

By the way, are you sure that copying all data from WorkflowTrackingStatusTable (without any filter) every time is a good idea? And if you sufficiently filter the data, maybe you'll be able to copy the data between buffers in X++, when you can use X++ functions suchas subStr().

Was this reply helpful? Yes No