Oracle compatibility?
Thanks Quantrix for your patience in dealing with my questions. However, I have some more!
I have developed a query through the Quantrix datalink wizard to basically extract labor cost information for a specific project for the month of Oct, 2010 from our accounting database. the information is drawn from two tables – Project Dollars and System period where the common link between the two tables is the column “Period”. The query generated by Quantrix looks like this:
SELECT
PA_PROJECT_DOLLARS.PROJECT_NO,
PA_PROJECT_DOLLARS.YEAR,
PA_PROJECT_DOLLARS.PERIOD,
PA_PROJECT_DOLLARS.BILLABLE_LABOR_COST,
SY_PERIOD.CALENDER_CODE,
SY_PERIOD.PERIOD,
SY_PERIOD.DES1
FROM
( OPS$SQLTIME.PA_PROJECT_DOLLARS PA_PROJECT_DOLLARS
INNER JOIN OPS$SQLTIME.SY_PERIOD SY_PERIOD ON SY_PERIOD.PERIOD = PA_PROJECT_DOLLARS.PERIOD)
WHERE
PA_PROJECT_DOLLARS.PROJECT_NO = ‘C-267-13’
AND PA_PROJECT_DOLLARS.YEAR = ‘2010’
AND SY_PERIOD.CALENDER_CODE = ‘WEEKLY’
AND SY_PERIOD.DES1 LIKE ‘%OCT’
When I run it I get an error as follows:
[Oracle][ODBC][Ora]ORA-00928: missing SELECT keyword
When speaking with on of our database guys here, he indicated the “inner join” command in the query is incompatible with Oracle. He modified the query to read as follows:
SELECT
PA_PROJECT_DOLLARS.PROJECT_NO,
PA_PROJECT_DOLLARS.YEAR,
PA_PROJECT_DOLLARS.PERIOD,
PA_PROJECT_DOLLARS.BILLABLE_LABOR_COST,
SY_PERIOD.CALENDER_CODE,
SY_PERIOD.PERIOD,
SY_PERIOD.DES1
FROM
OPS$SQLTIME.PA_PROJECT_DOLLARS PA_PROJECT_DOLLARS,
OPS$SQLTIME.SY_PERIOD SY_PERIOD
WHERE
SY_PERIOD.PERIOD = PA_PROJECT_DOLLARS.PERIOD
AND PA_PROJECT_DOLLARS.PROJECT_NO = ‘C-267-13’
AND PA_PROJECT_DOLLARS.YEAR = ‘2010’
AND SY_PERIOD.CALENDER_CODE = ‘WEEKLY’
AND SY_PERIOD.DES1 LIKE ‘%OCT’
This query produced the information I was looking for. I was puzzled by the need to revise the “FROM” portion of the query to make it compatible with Oracle. Is this a database driver issue or am I selecting the wrong items in the wizard to establish the link? It seems to occur everytime I am trying to draw information from two tables that are linked through a common column. In establishing the link to the database through the wizard, I have used the ODBC selections and SQL application selections in the wizard. Are these the wrong ones to use with Oracle?
Thanks for your assistance.
Doug
Any column from the second matrix, if dragged from its table to any portion of the quantrix matrix, whether it be the data area, the category areas, the filter area, or the DataNAV qualifier area, simply has a docment icon with a [b:u3vct82k]Ø[/b:u3vct82k] symbol in lieu of a little document icon. It simply will not permit one to “drop” the item anywhere on the matrix.
Doug
We loaded up JDBC drivers for Oracle 8.1.7 to see what the impact was. The JDBC drivers eliminimated the error that I had received above, however, it took a very long time for Quantrix to connect through DataNav to the datasource – it took minutes for the linkage to occur, whereas with the ODBC drivers, it happened in a matter of seconds. With ODBC drivers for Oracle 8.1.7, I can set up the matrix, but I cannot extract any data from the database and put it into the matrix. I can set up categories and they report correctly, but any associated data simply won’t download. So it appears JDBC is the way to go.
Through DataNav I have connected to two tables in my datasource, but I have not figured out how to get DataNav to extract data from both tables. Information from one table can be done, but once a link to one table is established, any columns from the second table simply cannot be exported into the Quantrix matrix. When I right click on a table in the datasource window, there is an option to “link tables”. I tried this (linking the column “Period” that is common to both tables). A greyed out “Period to Period phrase appears after the second table name. The link is established but I have not figured out how to get any information from it in conjunction with the first table. I have not seen anything in the webinars or videos where more than one data source is being used. Thanks for your assistance.
Doug
One thing you might want to try is the JDBC version of the Oracle driver. Looks like there are some drivers for Oracle 8 found here:
[url:6ian246t]http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html[/url:6ian246t]
Since Quantrix is a Java based application, we often find the java based JDBC drivers work better. It does not have to bridge and translate between JDBC and ODBC which reduces chances for error.
Once downloaded, you can load up the .jar file in Tools > Options and that will make it available in your DataSource connection panel.
Tried the DataNAV with limited success. I can create categories from a single table from my data source in Quantrix but I cannot get any of the data into the matrix from the datasource. I get an error as follows:
[Oracle][ODBC]invalid column number <1>
This applies to any table column I try to put into the matrix. This is not the same issue as above because I am only trying to import data from a single table. Have not tried to join these tables into the Quantrix matrix. Any ideas as to what may be going on?
Thanks,
Doug