Question Re: Isnull function not working for Date in Technology Q&A

Isnull function not working for Date

former_member375266 — Tue, 21 Nov 2017 02:50:33 GMT

Hi All,

Could you please let me know if the Isnull function in a calculated Column will work for the Date Field.

For example: I have a Field A with Date Type as Date, I created another Calculated Column with formula "isNull(Field A)". If I have a null value then the calculated column should show as 1 else as 0, right? But I'm getting 0 in spite they are Null Or populated.

Attaching a test view which I created with the same formula

I'm currently using HANA2.0 SPS 2 version

Re: Isnull function not working for Date

lbreddemann — Tue, 21 Nov 2017 03:34:22 GMT

We don't see how you've defined the calculated column, how the data looks like before it reaches the calculation or directly afterwards.

As usual, don't tell just tell us a story but show the code.

Re: Isnull function not working for Date

former_member375266 — Thu, 30 Nov 2017 23:06:09 GMT

Sorry for not being so elaborate.

Below is the snapshot which shows the details of my issue.

Snapshot of the base table.

base-table.png

Snapshot of the Calculated Column

cc.png

Snapshot of view output

cv-output.png

So in the calculated Column "isnull", I have used the code isnull("CalDate"). In my base table, i have 2 records populated, one with CalDate as null and another record with actual date populated. But my view output for the calculated column shows 0 for both the records.

please let me know if you need anymore details.

Re: Isnull function not working for Date

lbreddemann — Fri, 01 Dec 2017 03:17:43 GMT

I just tried to reproduce this on SP12 and couldn't. ISNULL() delivers the correct result for my example.

You may want to open a support incident for this, so that the problem can be analysed in more detail.

Re: Isnull function not working for Date

richard-zhao — Mon, 04 Dec 2017 09:31:20 GMT

Hello, Srinivasa First I cannot reproduce this issue on HANA 1.0 SP12 Version. I suggest that you could check your issue from below list.

Could you check your Calculated Column type, please? boolean is correct.
Could you check whether the column that your Calculated Column using is correct, please? I mean isnull("column") whether the column is that you really want to use. Maybe there a few column called CalDate.
If you can exclude possibility above that could be the HANA version issue. thanks

Re: Isnull function not working for Date

former_member375266 — Tue, 05 Dec 2017 22:19:08 GMT

Hi Richard,

I checked my data type for the calculated column and its nvarchar with length 2. I also tried with Boolean but still the same result. The column which I used in the calculated column is right. That's the base table field which has the date value and a null value.