DSXchange

Hi
As i was getting issue creating virtual table through "GUI", I tried one of the examples posted in the
viewtopic.php?t=146526&highlight=virtual+table

but the xsd provided there was not working for me, below are the issues
i tried running this command
C:\IBM\InformationServer\ASBNode\bin>IAAdmin.bat -user xxxx -password xxx -host xx.yy.com -port 9080 -xml -create -projectcontent D:\tablegevirtual

getting the error

Error: Value for parameter "-projectContent" is missing.

i tried giving the file in single quote, double quote for -project content, but no use, can anyone please tell me where i'm going wrong?

and can anyone please post a sampe xml file for virtual table creation
with multiple where clause, preferrably if it includes conditions for
specifying "current date" and extracting date from timestamp and comparing it with another date.

Below is the sample of my xsd

<?xml version="1.0" encoding="UTF-8"?>
<iaapi:Project xmlns:iaapi="http://www.ibm.com/investigate/api/iaapi" name="IA">
<DataSources>
<DataSource name="PROD">
<Schema name="CUSTO">
<VirtualTable name="CUSTOMER_VIRTUAL1" baseTable="CUSTOMER">
<description>CUSTOMER VIRTUAL TABLE</description>
<WhereCondition> FRUIT = 'ORANGE' </WhereCondition>
<WhereCondition> PURCHASEDATE <= CURRENT_DATE </WhereCondition>
<WhereCondition> DATE(EXPIRTYDATE) => '2013-12-31' </WhereCondition>
<Column name="FRUIT"/>
<Column name="SHOP"/>
<Column name="SHOPLOCATION"/>
<Column name="STATE"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>

2 things:

1 - It's case sensitive, so it needs to be -projectContent
2 - Doesn't look like you have the full name of your XML file

Yes:
-Use the syntax as described in the documentation (including case).
-Remove the -xml (that is for output)
-Use -update instead of -create. It should create the virtual table if it does not exist, or update the definition if it does.

So, your command line should be:
Also, your XML will probably not work because it has multiple <WhereCondition>. Change to one with AND or OR statements, as needed. Or, better yet, just try with one thing to start.


Finally, for dates, try something like:
<WhereCondition>DATEVAL(Date_column) < DATE()</WhereCondition>
This converts Date_column to a date value (in the format of mm-dd-ccyy) and makes sure it is less than the current date (in the format of mm-dd-ccyy).

The functions I used are from the DataDirect ODBC reference guide in C:\IBM\InformationServer\ODBCDrivers\docs\odbcref.pdf. I'm not sure if they all work, but these above statement did produce expected results for me.

Hi rj, thanks for responding..

your command has worked, thanks a lot...

but i'm struggling to get the rest of the syntax for the where conditions right, can you please specify me how to specify multiple where conditions with "AND" in that xml..

thanks a lot

AND's work just fine. Be sure to use the correct quotes (single or double - I forget , use parenthesis as needed, and to have only one <WhereCondition> </WhereCondition>

Hi thanks for responding..

i tried so many ways.. but couldnt nail it..
can you please tell what's the issue in the below code.. the command finishes successfully, but i'm not even able to even open the table, its showing "the edit virtual table cannot be opened"

<?xml version="1.0" encoding="UTF-8"?>
<iaapi:Project xmlns:iaapi="http://www.ibm.com/investigate/api/iaapi" name="IA">
<DataSources>
<DataSource name="datasource1">
<Schema name="schema1">
<VirtualTable name="virtual1" baseTable="actualsource">
<description>actual source table</description>
<WhereCondition> COL1 = 'APPLE' AND END_DATE > DATE() AND BEGINDATE <= DATE() AND DATEVAL(EFDATE) = '9999-12-31' </WhereCondition>
<Column name="COL1"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>


Also there is something as "<SQLExpression>" in the document, if an SQL can be directly used can you please show how it can be done, your help is really appreciated, below is the query i want to achieve

select colunmae1
from schemea1.actualsource
where COL1 = 'APPLE'
and END_DATE > current_date
and BEGINDATE <= current_date
and DATE(SOMEDATE) = '9999-12-31'

SOMEDATE IS A timestamp

please let me know how to write ans xsd for this one

Direct SQL entry is not possible.

I think your issue is that the HTML tag for <= is &le;

any idea on <sqlexpression> ????

Sorry - I edited my previous post with:

Direct SQL entry is not possible.

Hi robert

i tried exactly like below, but getting an error as " </stacktrace><message>The XML request passed as parameter could not be parsed for the following reason: The entity "le" was referenced, but not declared.</message><classn
cential.investigate.api.exceptions.CannotParseRequestException</classname><requestURI>/InformationAnalyzer/update</requestURI></exception>"

<?xml version="1.0" encoding="UTF-8"?>
<iaapi:Project xmlns:iaapi="http://www.ibm.com/investigate/api/iaapi" name="IA">
<DataSources>
<DataSource name="datasource1">
<Schema name="schem1">
<VirtualTable name="virtual1" baseTable="actualsource">
<description>actual source table</description>
<WhereCondition> COL1 = 'APPLE' AND END_DATE > DATE() AND BEGINDATE &le; DATE() AND DATEVAL(EFDATE) = '9999-12-31' </WhereCondition>
<Column name="COL1"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>


then i tried replacing &le; with just "<" this time the table is getting created, but when i tried editing the that table, i couldnt even open it, getting validation errors...

any other way to do it... whether those conditions are working for you?

What happens with double quotes instead of single quotes?

tried that as well, table is getting created but when tried to open, getting the error as "The edit virtual table cannot be opened"

<?xml version="1.0" encoding="UTF-8"?>
<iaapi:Project xmlns:iaapi="http://www.ibm.com/investigate/api/iaapi" name="IA">
<DataSources>
<DataSource name="datasource1">
<Schema name="schem1">
<VirtualTable name="virtual1" baseTable="actualsource">
<description>actual source table</description>
<WhereCondition> COL1 = "APPLE" AND END_DATE > DATE() AND BEGINDATE < DATE() AND DATEVAL(EFDATE) = "9999-12-31" </WhereCondition>
<Column name="COL1"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>

robert, one basic question... whether multiple where conditions and these kind of function are even possible in IA?? ..

am i keep hitting a metal wall???

They are possible.

Let's try something different....

Please create a virtual table in the GUI (not command line).
Select your columns (to include COL1, END_DATE, BEGINDATE, and EFDATE).
Check the 'use free form editor' check box.
Paste in: Then validate and go.

robert
thanks first of all for responding

did exactly the same...

on the GUI, the error message is

"Too many open or close parantheses, Missing one or more columns
Invalid Date value entered, cannot be converted to date format."

In the iaserver.log file the error is

this one 4 times

ODBC function "SQLNumResultCols" reported: SQLSTATE = HY000: Native Error Code = -180: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]STRING REPRESENTATION OF DATETIME VALUE HAS INVALID SYNTAX. DATE(


this one once
=========
com.ascential.asb.cas.shared.ConnectorServiceException: ODBC function "SQLNumResultCols" reported: SQLSTATE = 42S22: Native Error Code = -206: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]9999-12-31 NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.
at com.ascential.asb.cas.handler.ConnectorAccessHandler$Worker.processCreateDataProducerRequest(ConnectorAccessHandler.java:840)
at com.ascential.asb.cas.handler.ConnectorAccessHandler$Worker.run(ConnectorAccessHandler.java:518)


Error [IBM-IA-SERVER] [] Something went wrong. More details follow... ODBC function "SQLNumResultCols" reported: SQLSTATE = 42S22: Native Error Code = -206: Msg = [IBM(DataDirect OEM)][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]Apple NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.

DB:Db2 zos,

END_DATE : Data type: DATE
BEGINDATE: Data type: DATE
EFFDATE: Data type: Timestamp