DataColumn.Expression Evaluation Details RRS feed

  • Question

  • I have an expression for a newly added DataColumn.  Say it was something like "SALARY < 10000 AND GENDER = 'MALE'".  What if SALARY or GENDER is null? The column exists, but there is no value in the row.  It doesn't seem like "(SALARY < 10000 AND GENDER = 'MALE') IS NULL" is doing what I want either.  I previously had "ISNULL(SALARY < 10000 AND GENDER = 'MALE', FALSE)" but the problem is that if any field (SALARY or GENDER) is null, I want the value for the new DataColumn to be 0, if the expression evaluates to TRUE, the value should be 1, FALSE should evaluate to 2.  

    For those who do better seeing more of the code (strFilter is my logic expression):

    string expr = "IIF(ISNULL(" + strFilter + ", 0), 1, 2)";

    The problem with the above statement is that the second argument of ISNULL has to have the same return type as the expression.

    string expr = "IIF((" + strFilter + ") IS NULL, 0, IIF(" + strFilter + ", 1, 2))";

    The problem with this statement is that IS NULL does not evaluate the same as ISNULL.

    Any suggestions on how to get this working?

    Thank you in advance!

    Thursday, January 10, 2013 12:39 AM


  • Hi Chimiman,

    Welcome to the MSDN Forum.

    Please try this Expression:IIF(IIF(SALARY is null,0,1) + IIF(GENDER is null,1,3)<4,IIF(SALARY is null,0,1) + IIF(GENDER is null,1,3),SALARY < 10000 AND GENDER = 'MALE')

    When you got 1, it means both salary and gender are null, when you got 2, that means gender is null, when you got 3, that means, the salary is null, otherwise, you willl got true or false based on the valid data.

            Dim dateDt As New DataTable
            dateDt.Columns.Add(New DataColumn("SALARY", Type.GetType("System.Int32")))
            dateDt.Columns.Add(New DataColumn("GENDER", Type.GetType("System.String")))
            dateDt.Rows.Add(9000, "male")
            dateDt.Rows.Add(19000, "fmale")
            dateDt.Rows.Add(29000, "male")
            dateDt.Rows.Add(8000, "fmale")
            dateDt.Rows.Add(8000, "male")
            dateDt.Rows.Add(19000, "fmale")
            dateDt.Rows.Add(DBNull.Value, "male")
            dateDt.Rows.Add(9000, DBNull.Value)
            dateDt.Rows.Add(DBNull.Value, DBNull.Value)
            With dateDt.Columns.Add("status")
                .Expression = "IIF(IIF(SALARY is null,0,1) + IIF(GENDER is null,1,3)<5,IIF(SALARY is null,0,1) + IIF(GENDER is null,1,3),SALARY < 10000 AND GENDER = 'MALE')"
            End With
            For Each r As DataRow In dateDt.Rows
                Console.WriteLine(r.Item(0) & "             " & r.Item(1) & "             " & r.Item(2))

    I hope this will be helpful.

    Best regards,

    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 10, 2013 6:51 AM