Not able to create virtual table based on Null values

[Madhumitha_Raghunathan]

Hi,

I am trying to create a virtual table on Information Analyzer and I need to fetch all the records which have null value in a date field.

When I try to do END_DT IS NULL or END_DT = TO_DATE(NULL,'MM-DD-YYYY') in the free form editor it is not working. Though these are working directly on the database as queries.

I am getting the message saying "Invalid DateTime value entered, cannot be converted to DateTime format"

Does anyone know of any other way of doing this when creating virtual tables or if it is possible at all?

Thanks,
Madhumitha

[ray.wurlod]

Just bring the date column across and let column analysis worry about whether the value is null or not.

[Madhumitha_Raghunathan]

Thank you so much for the response, I guess the column analysis is already done..

We are trying to create a virtual table with a valid set of data so that we can build data rules on them. Since we are using scd 2 we need to use records with null expiry_dt. This is where we are facing an issue.

Is there a way to filter it out before the rule definition?


Thanks,
Madhumitha

[rjdickson]

Hi,

A PMR may need to be opened to fix that Virtual Table functionality. It should work.

As a workaround, can you use an 'AND' condition in your rule to ignore records with populated dates?

[Madhumitha_Raghunathan]

Hi Robert,

Thank you so much. We have raised a PMR. And also we will try to include it in the Rule Definition.

Will let know know how the PMR goes.

Thanks,
Madhumitha

[rjdickson]

Another option is to use the API to create a virtual table. You have more flexibility there than in the GUI. Create a file (NULLDATES.txt for example) with the following code. Replace all things inbetween | with your stuff:


<?xml version="1.0" encoding="UTF-8"?>
<iaapi:Project xmlns:iaapi="http://www.ibm.com/investigate/api/iaapi" name="|project Name|">
<DataSources>
<DataSource name="|DataSource|">
<Schema name="|Schema|">
<VirtualTable name="|Customer_No_Null_Dates|" baseTable="|BaseTable|">
<description>Customers with null dates</description>
<WhereCondition> END_DT IS NULL</WhereCondition>
<Column name="|COL1|"/>
<Column name="|COL2|"/>
<Column name="|COL3|"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>

Then run c:\IBM\InformationServer\ASBNode\bin\IAAdmin.bat -user |user| -password |password| -host |host| -xml -update -projectContent c:\NULLDATES.txt

If all is ok, you will receive NO messages back. If there is an error, you will see a bunch of error messages.

The full XSD is documented at this link.

[Madhumitha_Raghunathan]

Awesome!!

Thank you so much Robert!! I tried it out and it worked.

Though when I try to edit that table definition through IA it didnt work...

But when i changed the metadata in the XML and reran it the changes got updated.

Regards,
Madhumitha

[sivaetl.dwh]

I had the same issue. I opened the free form editor and typed in my query as such and it worked fine for me.

for e.g.
file_date is null

[Madhumitha_Raghunathan]

Hi Siva,

I tried to type it in the free-form editor also and it validated fine.... But I had problem saving the query. Free-form editor also didnt work for me.
Exactly what u mentioed. But it worked through teh HTTP-API.

Thanks,
Madhumitha

[sivaetl.dwh]

Hi Madhumitha,

'n' things - 'n' ways as long as they work


Regards,
Siva