Row with missing timestamp is still selected

[#1]

Hello all,

I have a problem with logs that distribution service makes.

I want to get the name of tasks that have timestamp within some range, and I get the all of them, even it the value is null.

Here is the sample script:

TaskExecutionHistoryItemExtra:

load

    *,

    date#(Left(TaskExecutionHistoryItem.StartTimeStamp,10)) as TaskExecutionHistoryItem.Date,

    interval(Sum(TaskExecutionHistoryItem.Duration), 'hh:mm:ss') as TaskExecutionHistoryItem.TotalExecTime

Resident TaskExecutionHistoryItem_tmp

Where TaskExecutionHistoryItem.StartTimeStamp >= Date(Date#(Today(), 'DD.MM.YYYY')-8,'') & ' ' & '00:00:00'

  and TaskExecutionHistoryItem.StartTimeStamp <= Date(Date#(Today(), 'DD.MM.YYYY')-1,'') & ' ' & '23:59:59'

  and not Isnull(TaskExecutionHistoryItem.Duration) and Len(trim(TaskExecutionHistoryItem.Duration)) > 0

Group by

    %TaskId,

    TaskExecutionHistoryItem.ID,

    TaskExecutionHistoryItem.StartTimeStamp,

    TaskExecutionHistoryItem.EndTimeStamp,

    TaskExecutionHistoryItem.Duration,

    TaskExecutionHistoryItem.TaskStatus,

    Month;

Any of these fields can contain null value, and I want tasks that have all info

    TaskExecutionHistoryItem.StartTimeStamp,

    TaskExecutionHistoryItem.EndTimeStamp,

    TaskExecutionHistoryItem.Duration,

[anbu1984]

load

    *,....

month

where Len(Trim(TaskExecutionHistoryItem.StartTimeStamp)) > 0 And Len(Trim(TaskExecutionHistoryItem.EndTimeStamp)) > 0 And Len(Trim(TaskExecutionHistoryItem.Duration)) > 0;

[#3]

It didnt work.

[anbu1984]

I just noticed at beginning of your post you mentioned, you need rows where field is null. Which field is this?

I want to get the name of tasks that have timestamp within some range, and I get the all of them, even it the value is null.

So you have two conditions?? One with above field is null OR below all fields should have value??

Any of these fields can contain null value, and I want tasks that have all info

    TaskExecutionHistoryItem.StartTimeStamp,

    TaskExecutionHistoryItem.EndTimeStamp,

    TaskExecutionHistoryItem.Duration,

Can you post what you tried?

[#5]

Hi there,

I meant that, even if I specify the range, I still get the rows where the Timestamp is null.

[anbu1984]

Can you post your script?