ax.ExecuteStmt syntax: How to find records where [fieldname] is blank/empty

(0) Share

Report

Posted on by JMelquist

120

I'm trying to execute a query (Microsoft.Dynamics.BusinessConnectorNet.ExecuteStmt) in C# via the BusinessConnector (ax2012) to find records where a particular field has a blank/empty string value. Currently i'm trying to pass:

select forupdate * from axTbl_0 where axTbl_0.QuotationId == ('QUOTE00019358') && axTbl_0.SectionID == ('');

...But I am getting an "Unable To Compile Statement" error due to the "== ('')" part of the query. I've also tried "is null" with the same results. I cannot find the syntax anywhere (these forums or via google) as how to filter to only records where a particular field value is blank. I know i can pull back all the records and iterate thru them in C# to find the records with blank values, but this isn't efficient.

Any help would be appreciated, Thank in advance!

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Community Member on at

Like (0)

Report

The statement must be a valid Microsoft Axapta X++ statement.

try writing your statement like this

For example: select * from %1 where %2.Name=='Axapta' AND %3.JobID=='1234'. The %1, %2 and %3 placeholders are automatically substituted with the record names from param1, param2 and param3, respectively.

so may be your statement will be

select forupdate * from %1 where %2.QuotationId == 'QUOTE00019358' AND %3.SectionID == ''

axTbl_0,axTbl_0, axTbl_0

Please verify.

Was this reply helpful? Yes No
JMelquist 120 on at

Like (0)

Report

thanks Nitesh,

Good call on the [use parameters] suggestion, this is a best practice and I probably should be using them. However, this alone doesn't address my specific issue. When I use parameters and pass in a C# empty string as one of the parameters, I still get the compile error.

As a workaround, I'm pulling all the records with my Quote filter into a C# dataset and then iterating thru them evaluating the SectionID field in C#. not the most efficient, but do-able until I can figure out how to tell AX to only return records where the SectionID field value is blank/empty.

I wonder if I can apply any inline statements in the query (I'm thinking a string.length function). if so, I could use the following query syntax: "... where axTbl_0.QuotationID == 'QUOTE00012345' AND LEN(axTbl_0.SectionID) == 0"

-Jas

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

Like (0)

Report

select forupdate * from %1 where %1.QuotationId == ('QUOTE00019358') && %1.SectionID == ('');

msdn.microsoft.com/.../aa548206.aspx

Was this reply helpful? Yes No
JMelquist 120 on at

Like (0)

Report

Thanks Dick,

I accidentally typed "AND" instead of "&&" in my response to Nitesh, but I do have the "&&" syntax correct in my statement that I'm executing (shown in my orig post). So my inability to select records where a particular field is blank/empty...remains a problem.

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

Like (0)

Report

you really should use %1 for the table name it is a place holder, not the name.

the AxaptaRecord is an object that is the placeholder of the table

public void ExecuteStmt(

string statement,

AxaptaRecord param1

)

Was this reply helpful? Yes No
JMelquist 120 on at

Like (0)

Report

hm. OK. Well, rather than fight it, I'll just go along with it. Incorporating everyone's suggestions, I have the following C# statement:

axRecord.ExecuteStmt("select forupdate * from %1 where %1.QuotationId == ('QUOTE00019358') && %1.SectionID == ('');", axTbl_0);

I am getting a compile error saying %1.SectionID == ('') is not valid syntax. Forgiving me for any other issues which may be in here (spelling, grammar, etc) can anyone tell me the syntax I should be using to return records that have a blank value in the SectionID field? That's all I'm looking for. Just the syntax for that specific part of the where clause. thanks much.

-Jas

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

Like (0)

Report

this a line from my old code that works

ExecuteStmt("Select firstonly from %1 where %1.journalType == 0");

Was this reply helpful? Yes No
JMelquist 120 on at

Like (0)

Report

Thanks Dick, for enums (such as the journalType field you reference) you can compare to the enum value (e.g. Zero). Similarly, for RecId fields I could search for a Zero. But in this case, I'm trying to find a blank/empty string in a varchar (i.e. text) field - quite a different situation.

-Jas

Was this reply helpful? Yes No
Suggested answer

Dick Wenning 8,705 Moderator on at

Like (0)

Report

try this

AxaptaRecord axTbl_0 = ax.CreateAxaptaRecord("InventJournalTable");

axTbl_0.ExecuteStmt("select forupdate * from %1 where %1.QuotationId == 'QUOTE00019358' && %1.SectionID == ''");

I also found this

msdn.microsoft.com/.../bb931177.aspx

Was this reply helpful? Yes No

Community site session details

ax.ExecuteStmt syntax: How to find records where [fieldname] is blank/empty

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

ax.ExecuteStmt syntax: How to find records where [fieldname] is blank/empty

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