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
