XSD issue in virtual table creation
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
XSD issue in virtual table creation
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>
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>
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
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.
-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:
Code: Select all
IAAdmin.bat -user xxxx -password xxx -host xx.yy.com -port 9080 -update -projectContent D:\tablegevirtual
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.
Regards,
Robert
Robert
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
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
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 ≤
I think your issue is that the HTML tag for <= is ≤
Last edited by rjdickson on Mon Aug 12, 2013 1:58 pm, edited 1 time in total.
Regards,
Robert
Robert
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
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 ≤ DATE() AND DATEVAL(EFDATE) = '9999-12-31' </WhereCondition>
<Column name="COL1"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>
then i tried replacing ≤ 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?
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 ≤ DATE() AND DATEVAL(EFDATE) = '9999-12-31' </WhereCondition>
<Column name="COL1"/>
</VirtualTable>
</Schema>
</DataSource>
</DataSources>
</iaapi:Project>
then i tried replacing ≤ 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?
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
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>
<?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>
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
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.
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:
Code: Select all
COL1 = "APPLE" AND END_DATE > DATE() AND BEGINDATE <= DATE() AND DATEVAL(EFDATE) = "9999-12-31"
Regards,
Robert
Robert
-
- Premium Member
- Posts: 139
- Joined: Fri Apr 11, 2008 1:02 pm
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
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