Oracle compatibility?

5.95K viewsDataLink
0

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

0

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

You are viewing 1 out of 11 answers, click here to view all answers.