Microsoft Dynamics AX (Archived)

how to handle null value string column of a View in X++ ?

(1) Share

Report

Posted on by San Artham

3,175

i have created a View InventTransWithBranchDept with following records for the Item "TV":

now here is my code, the while loop on asIsTable is just not picking the row with null or empty Department

LF_InventTransWithBranchDeptDimView grpTable;
LF_InventTransWithBranchDeptDimView asIsTable;

while select grpTable
group by grpTable.Branch , grpTable.Department, grpTable.ItemId
where grpTable.ItemId == "TV" //"10059"
{
while select asIsTable
where asIsTable.Branch == grpTable.Branch
&& asIsTable.Department == grpTable.Department
&& asIsTable.ItemId == grpTable.ItemId
{
info( strFmt( "%1 - %2", asIsTable.Qty , asIsTable.Department) );
}
}

i have tried this too but no luck -

( ( asIsTable.Department == "" && grpTable.Department == "")

|| && asIsTable.Department == grpTable.Department )

any help here would be appreciated

- San

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Srinath Sundaresan 510 on at

Like (0)

Report

Here is how ax treats null

msdn.microsoft.com/.../aa846236.aspx

Was this reply helpful? Yes No
San Artham 3,175 on at

Like (0)

Report

Hi Srinath, thank you for the response. I knew that null for string field is "", in this case it is not working.

Was this reply helpful? Yes No
Verified answer

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

Like (2)

Report

No, null for string field isn't empty string! Null is null and empty string is a non-null value. These are two completely different things. That AX (normally) doesn't use null values and always set some value is a completely different statement.

If your view returns null values, change it to return default values instead (e.g. the default value for string fields is empty string).

Was this reply helpful? Yes No
San Artham 3,175 on at

Like (0)

Report

thank you Martin, as per your suggestion i have resolved this issue by creating computed column for Department in this pattern :

SysComputedColumn::if(SysComputedColumn::isNullExpression(fieldName), SysComputedColumn::returnLiteral(""), fieldName);

In SQL this would translate to:

(CAST ((CASE WHEN T2.DEPARTMENT IS NULL THEN N'empty' ELSE T2.DEPARTMENT END) AS NVARCHAR(30))) AS MyDEPT

For me, it should not be this complicated so this may be considered as flaw in AX X++.

i don't think this would be the case in C#.net

Regards,

Santosh.

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

Like (0)

Report

SQL and C# are two different things and it all depends on how the database access framework deals with null values. For example, null values in database can be translated to instances of DBNull class (which is something else then null reference...).

Nevertheless the absence a value (= null) and an empty string souldn't ever be considered the same.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I came across the same issue.

I was using existing Views as examples. Do I miss understand the code, or is it a Microsoft mistake?

Two examples: HcmPersonProjectRoleEntity::dataAreaValue() and WHSFixedProductWarehouseLocationEntity::configurationIdDefinition()

Is it significant that the examples I mentioned are computed columns for Data Entities and not Views? I have the idea that for this purpose it is the same thing.

Was this reply helpful? Yes No