Pass Through Query Code

K

Kevin

I have the below code in a Pass through query that pulls from a Oracle DB.
It is pulling data from 2002 through today. It takes 13 minutes to run and I
really only need data where the KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE is
equal to or greater than 12/31/2008. If I can get help with how the code
should be I think it will run much faster. Also is there a way to append
this data directly into a table named tbl_CompleteActualData?

SELECT KTMG_TIME_SHEETS.RESOURCE_USER_ID, KTMG_TIME_SHEETS.STATUS_CODE,
KTMG_TIME_SHEETS.PERIOD_ID, KTMG_TIME_SHEETS.TIME_SHEET_ID,
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID, KTMG_TIME_SHEET_LINES.WORK_ITEM_ID,
KTMG_TIME_SHEET_LINES.WORK_ITEM_SET_ID,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_TIME,
KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_DETAIL_ID
FROM (KTMG_TIME_SHEET_LINES INNER JOIN KTMG_TIME_SHEETS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_ID = KTMG_TIME_SHEETS.TIME_SHEET_ID) INNER
JOIN KTMG_TIME_SHEET_LINE_DETAILS ON KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID
= KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_ID;
 
J

Jerry Whittle

The following should work; however, sometimes Oracle is picky about date
formats:

SELECT KTMG_TIME_SHEETS.RESOURCE_USER_ID, KTMG_TIME_SHEETS.STATUS_CODE,
KTMG_TIME_SHEETS.PERIOD_ID,
KTMG_TIME_SHEETS.TIME_SHEET_ID,
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID, KTMG_TIME_SHEET_LINES.WORK_ITEM_ID,
KTMG_TIME_SHEET_LINES.WORK_ITEM_SET_ID,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_TIME,
KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_DETAIL_ID
FROM (KTMG_TIME_SHEET_LINES
INNER JOIN KTMG_TIME_SHEETS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_ID = KTMG_TIME_SHEETS.TIME_SHEET_ID)
INNER JOIN KTMG_TIME_SHEET_LINE_DETAILS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID
= KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_ID
WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE => #12/31/2008# ;

Are you really sure that you want dates from 2009?

Also it doesn't gaurentee that it will be much faster. See if the
ACTUAL_DATE field is indexed. Otherwise you may be disappointed. If the date
doesn't change, another option would be to create a view in Oracle out of the
SQL above and just query the view.
 
K

Kevin

I had the date wrong it should have been 12/31/2007. I am getting error
though ODBC Call failed missing expression. What do I need to do to fix this
error? Plus is there a way to change this code so it appends the data
directly into the table tbl_CompleteActualData? Thanks for the quick reply.
 
O

Ofer Cohen

Not sure about Oracle, but usinh SQL Server, using a date as a criteria you
need to write single quote before and after the date and not # like in Access

SELECT KTMG_TIME_SHEETS.RESOURCE_USER_ID, KTMG_TIME_SHEETS.STATUS_CODE,
KTMG_TIME_SHEETS.PERIOD_ID,
KTMG_TIME_SHEETS.TIME_SHEET_ID,
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID, KTMG_TIME_SHEET_LINES.WORK_ITEM_ID,
KTMG_TIME_SHEET_LINES.WORK_ITEM_SET_ID,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_TIME,
KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_DETAIL_ID
FROM (KTMG_TIME_SHEET_LINES
INNER JOIN KTMG_TIME_SHEETS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_ID = KTMG_TIME_SHEETS.TIME_SHEET_ID)
INNER JOIN KTMG_TIME_SHEET_LINE_DETAILS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID
= KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_ID
WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE => '12/31/2008'
 
K

Kevin

Still getting error ODBC call failed 00936 missing expression. and what
about turning the code into an append to the table tbl_CompleteActualData is
this possible?

Thanks again.

Kevin
 
S

Stefan Hoffmann

Kevin said:
I had the date wrong it should have been 12/31/2007. I am getting error
though ODBC Call failed missing expression. What do I need to do to fix this
error?
Click to expand...
Try this:

WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE =>
To_Date('12/31/2008', 'mm-dd-yyyy')
Plus is there a way to change this code so it appends the data
directly into the table tbl_CompleteActualData?
Click to expand...
No, but you can use this passthrough query as source for an append query.


mfG
--> stefan <--
 
K

Kevin

Ok jsut did the below code and still getting ODB call failed ORA-00936
missing expression

SELECT KTMG_TIME_SHEETS.RESOURCE_USER_ID, KTMG_TIME_SHEETS.STATUS_CODE,
KTMG_TIME_SHEETS.PERIOD_ID,
KTMG_TIME_SHEETS.TIME_SHEET_ID,
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID, KTMG_TIME_SHEET_LINES.WORK_ITEM_ID,
KTMG_TIME_SHEET_LINES.WORK_ITEM_SET_ID,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE,
KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_TIME,
KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_DETAIL_ID
FROM (KTMG_TIME_SHEET_LINES
INNER JOIN KTMG_TIME_SHEETS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_ID = KTMG_TIME_SHEETS.TIME_SHEET_ID)
INNER JOIN KTMG_TIME_SHEET_LINE_DETAILS ON
KTMG_TIME_SHEET_LINES.TIME_SHEET_LINE_ID
= KTMG_TIME_SHEET_LINE_DETAILS.TIME_SHEET_LINE_ID
WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE => To_Date('12/31/2008',
'mm-dd-yyyy')
 
J

John Spencer

First I would try using something along the lines of

WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE >= '2007/01/01'

OR try

WHERE KTMG_TIME_SHEET_LINE_DETAILS.ACTUAL_DATE > '2006/12/31'

If those fail, then I would find an Oracle site or developer and ask them
about the proper syntax to do this.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Stefan Hoffmann

hi Kevin,
Ok jsut did the below code and still getting ODB call failed ORA-00936
missing expression
Click to expand...
Use SQL Tools (http://sqltools.net) or any other tool for accessing
Oracle directly.

You have an syntax error in your SQL statement.


mfG
--> stefan <--
 
K

Kevin

So the query works without the WHERE statement so it is safe to assume the
problem is in the where statement?

Kevin
 
K

Kevin

Stefan I dont understand what I will use the sql tool for? I have downloaded
it but what do I do with it?
 
S

Stefan Hoffmann

hi Kevin,
Stefan I dont understand what I will use the sql tool for? I have downloaded
it but what do I do with it?
Click to expand...
Connect to your Oracle instance and get your SQL right... Your error is
an Oracle error.


mfG
--> stefan <--
 
K

Kevin

i have pasted the code into sql tools i get the same error but how do i get
the code right? line 15 pos 49 text ORA-00936 missing expression
 
J

John Spencer

Line 15 postion 49 is the position of the ">" character.

First thing I would try would be to use
= (as the operator) and not =>
Click to expand...

I have no idea what To_Date is. Is that a built-in function of Oracle?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
K

Kevin

The change to >= from => seems to have worked the query now runs and it is
only taking 3 minutes!!!!

Thank you all for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass Through Query Make Table 3
Pass Through Query Help!!! 3
Pass-Through query used to update an access table 2
pass through parameter 4
Pass Through Query Error 2
Pass through query to oracle db 2
Pass Through Query using Local Access Table 1
Code for Criteria in a Pass Through Query 3

Top