Pass Through Query Code

  • Thread starter Thread starter Kevin
  • Start date Start date
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;
 
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.
 
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.
 
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'
 
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
 
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 <--
 
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')
 
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
..
 
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 <--
 
So the query works without the WHERE statement so it is safe to assume the
problem is in the where statement?

Kevin
 
Stefan I dont understand what I will use the sql tool for? I have downloaded
it but what do I do with it?
 
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 <--
 
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
 
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
..
 
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.
 
Back
Top