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
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