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_NAM E AS [CLIENT PROG], " & _
"tbl3.PROGRAM_V ERSION_ID AS PROG_VER, " & _
"tbl6.MODUL E_ID AS MOD_ID, tbl4.VARIATION AS VAR, " & _
"tbl4.DESCRIPTI ON AS VER, tbl3.VERSION_ID AS VER_ID, " & _
"tbl7.MODULE_NA ME AS MOD_NAME, " & _
"Max(tbl6.ATTEM PT_DATE) AS LAST_ATTEMPT, " & _
"Max(tbl6.PASSE D_IND) AS PASSED, tbl2.ENABLED_IN D AS ENABLED, " & _
"tbl6.SHOPPER_I D AS SID, tbl1.EDATE AS HIRE_DATE, tbl1.NAME, " & _
"tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, tbl1.HPHONE, tbl1.WPHONE, " & _
"tbl1.CALLW ORK, tbl1.EMAIL " & _
"FROM (COLOSSUS_EMPLO YEE AS tbl1 INNER JOIN " & _
"(COLOSSUS_MODU LE_CERTIFICATIO NS AS tbl2 RIGHT JOIN " & _
"((((COLOSSUS_P ROGRAM_VERSIONS AS tbl3 INNER JOIN " & _
"COLOSSUS_VARIA TION_VERSIONS AS tbl4 ON " & _
"tbl3.VERSION_I D = tbl4.VERSION_ID ) INNER JOIN " & _
"COLOSSUS_AUDIT S AS tbl5 ON tbl4.AUDIT_ID = tbl5.AUDIT_ID) " & _
"INNER JOIN COLOSSUS_MODULE _EXAM_ATTEMPTS AS tbl6 ON " & _
"tbl3.PROGRAM_V ERSION_ID = tbl6.PROGRAM_VE RSION_ID) INNER JOIN " & _
"COLOSSUS_MODUL ES AS tbl7 ON tbl6.MODULE_ID = tbl7.MODULE_ID) ON " & _
"(tbl2.SHOPPER_ ID = tbl6.SHOPPER_ID ) AND " & _
"(tbl2.MODULE_I D = tbl6.MODULE_ID) ) ON " & _
"tbl1.SHOPP ERID = tbl6.SHOPPER_ID ) INNER JOIN " & _
"COLOSSUS_CLIEN TS AS tbl8 ON tbl5.CID = tbl8.CID " & _
"GROUP BY tbl8.CLIENT_NAM E, tbl3.PROGRAM_VE RSION_ID, " & _
"tbl6.MODULE_ID , tbl4.VARIATION, tbl4.DESCRIPTIO N, " & _
"tbl3.VERSION_I D, tbl7.MODULE_NAM E, tbl6.SHOPPER_ID , tbl1.EDATE,
tbl2.ENABLED_IN D, tbl1.NAME, tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, " & _
"tbl1.HPHON E, tbl1.WPHONE, tbl1.CALLWORK, tbl1.EMAIL, tbl5.CID " & _
"HAVING (((Max(tbl6.ATT EMPT_DATE)) Between #" & rDate1 & _
"# And #" & rDate2 + 1 - ((1 / 3600) / 24) & "#) " & _
"AND ((Max(tbl6.PASS ED_IND))='Y'))"
Any feedback would be much appreciated!
:-)
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_NAM E AS [CLIENT PROG], " & _
"tbl3.PROGRAM_V ERSION_ID AS PROG_VER, " & _
"tbl6.MODUL E_ID AS MOD_ID, tbl4.VARIATION AS VAR, " & _
"tbl4.DESCRIPTI ON AS VER, tbl3.VERSION_ID AS VER_ID, " & _
"tbl7.MODULE_NA ME AS MOD_NAME, " & _
"Max(tbl6.ATTEM PT_DATE) AS LAST_ATTEMPT, " & _
"Max(tbl6.PASSE D_IND) AS PASSED, tbl2.ENABLED_IN D AS ENABLED, " & _
"tbl6.SHOPPER_I D AS SID, tbl1.EDATE AS HIRE_DATE, tbl1.NAME, " & _
"tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, tbl1.HPHONE, tbl1.WPHONE, " & _
"tbl1.CALLW ORK, tbl1.EMAIL " & _
"FROM (COLOSSUS_EMPLO YEE AS tbl1 INNER JOIN " & _
"(COLOSSUS_MODU LE_CERTIFICATIO NS AS tbl2 RIGHT JOIN " & _
"((((COLOSSUS_P ROGRAM_VERSIONS AS tbl3 INNER JOIN " & _
"COLOSSUS_VARIA TION_VERSIONS AS tbl4 ON " & _
"tbl3.VERSION_I D = tbl4.VERSION_ID ) INNER JOIN " & _
"COLOSSUS_AUDIT S AS tbl5 ON tbl4.AUDIT_ID = tbl5.AUDIT_ID) " & _
"INNER JOIN COLOSSUS_MODULE _EXAM_ATTEMPTS AS tbl6 ON " & _
"tbl3.PROGRAM_V ERSION_ID = tbl6.PROGRAM_VE RSION_ID) INNER JOIN " & _
"COLOSSUS_MODUL ES AS tbl7 ON tbl6.MODULE_ID = tbl7.MODULE_ID) ON " & _
"(tbl2.SHOPPER_ ID = tbl6.SHOPPER_ID ) AND " & _
"(tbl2.MODULE_I D = tbl6.MODULE_ID) ) ON " & _
"tbl1.SHOPP ERID = tbl6.SHOPPER_ID ) INNER JOIN " & _
"COLOSSUS_CLIEN TS AS tbl8 ON tbl5.CID = tbl8.CID " & _
"GROUP BY tbl8.CLIENT_NAM E, tbl3.PROGRAM_VE RSION_ID, " & _
"tbl6.MODULE_ID , tbl4.VARIATION, tbl4.DESCRIPTIO N, " & _
"tbl3.VERSION_I D, tbl7.MODULE_NAM E, tbl6.SHOPPER_ID , tbl1.EDATE,
tbl2.ENABLED_IN D, tbl1.NAME, tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, " & _
"tbl1.HPHON E, tbl1.WPHONE, tbl1.CALLWORK, tbl1.EMAIL, tbl5.CID " & _
"HAVING (((Max(tbl6.ATT EMPT_DATE)) Between #" & rDate1 & _
"# And #" & rDate2 + 1 - ((1 / 3600) / 24) & "#) " & _
"AND ((Max(tbl6.PASS ED_IND))='Y'))"
Any feedback would be much appreciated!
:-)