By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Complex Query Keeps Running Not Returning Data

P: n/a
Hi there,

I was wondering if someone could shed some light on a problem I have no
idea on how to fix.

I created an Excel Add-In that uses an ADO connection to an Access
database on a file server, which in turn has its tables linked to an
Oracle back-end. I'm pretty sure I can take out Access as the middleman
by just querying against the Oracle database, but that's not my
question.

I created a complex query that uses 8 tables, gets aggregated, and has
a critiera on a MAX aggregate field.

If I don't aggregate the query it works fine and the results are
returned to Excel.

The field in question is a date field. The purpose of the aggregate
function is to get the most recent date results. The critiera against
this field allows the user to select a date range to query the data.
The objective is to find out each person's latest test scores per
module exam.

There's actually another aggregated field that's filtered against, but
it has no effect on the problem in question. The field is PASSED_IND,
which indicates whether the exam was passed "Y" or failed "N".

The query works fine, with no syntax error when run in Access's query
interface. I took the SQL from it and pasted into Excel's VBA module to
create a simple programmatic tool to fetch the data dynamically..

Is there something to do with time-outs or an issue with Oracle that
I'm not aware of? The acceptance of my SQL in Oracle is what I'm not
sure about. If any experienced users have come across this, I would
appreciate your guidance and advise.

Here is my SQL:

"SELECT tbl8.CLIENT_NAME AS [CLIENT PROG], " & _
"tbl3.PROGRAM_VERSION_ID AS PROG_VER, " & _
"tbl6.MODULE_ID AS MOD_ID, tbl4.VARIATION AS VAR, " & _
"tbl4.DESCRIPTION AS VER, tbl3.VERSION_ID AS VER_ID, " & _
"tbl7.MODULE_NAME AS MOD_NAME, " & _
"Max(tbl6.ATTEMPT_DATE) AS LAST_ATTEMPT, " & _
"Max(tbl6.PASSED_IND) AS PASSED, tbl2.ENABLED_IND AS ENABLED, " & _
"tbl6.SHOPPER_ID AS SID, tbl1.EDATE AS HIRE_DATE, tbl1.NAME, " & _
"tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, tbl1.HPHONE, tbl1.WPHONE, " & _
"tbl1.CALLWORK, tbl1.EMAIL " & _

"FROM (COLOSSUS_EMPLOYEE AS tbl1 INNER JOIN " & _
"(COLOSSUS_MODULE_CERTIFICATIONS AS tbl2 RIGHT JOIN " & _
"((((COLOSSUS_PROGRAM_VERSIONS AS tbl3 INNER JOIN " & _
"COLOSSUS_VARIATION_VERSIONS AS tbl4 ON " & _
"tbl3.VERSION_ID = tbl4.VERSION_ID) INNER JOIN " & _
"COLOSSUS_AUDITS AS tbl5 ON tbl4.AUDIT_ID = tbl5.AUDIT_ID) " & _
"INNER JOIN COLOSSUS_MODULE_EXAM_ATTEMPTS AS tbl6 ON " & _
"tbl3.PROGRAM_VERSION_ID = tbl6.PROGRAM_VERSION_ID) INNER JOIN " & _
"COLOSSUS_MODULES AS tbl7 ON tbl6.MODULE_ID = tbl7.MODULE_ID) ON " & _
"(tbl2.SHOPPER_ID = tbl6.SHOPPER_ID) AND " & _
"(tbl2.MODULE_ID = tbl6.MODULE_ID)) ON " & _
"tbl1.SHOPPERID = tbl6.SHOPPER_ID) INNER JOIN " & _
"COLOSSUS_CLIENTS AS tbl8 ON tbl5.CID = tbl8.CID " & _

"GROUP BY tbl8.CLIENT_NAME, tbl3.PROGRAM_VERSION_ID, " & _
"tbl6.MODULE_ID, tbl4.VARIATION, tbl4.DESCRIPTION, " & _
"tbl3.VERSION_ID, tbl7.MODULE_NAME, tbl6.SHOPPER_ID, tbl1.EDATE,
tbl2.ENABLED_IND, tbl1.NAME, tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, " & _
"tbl1.HPHONE, tbl1.WPHONE, tbl1.CALLWORK, tbl1.EMAIL, tbl5.CID " & _

"HAVING (((Max(tbl6.ATTEMPT_DATE)) Between #" & rDate1 & _
"# And #" & rDate2 + 1 - ((1 / 3600) / 24) & "#) " & _
"AND ((Max(tbl6.PASSED_IND))='Y'))"

Any feedback would be much appreciated!

:-)

Jan 12 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.