SQL Query problem

[glenlove2002]

Hi,

I'm trying to run an outer join, and while the SQL query works outside VB 2005, when used in VB 2005 it's a completely different story. It's really frustrating - it's like Microsoft has decided that they want people to write SQL queries in a different way that normal. Here's the query.

sqlString = "SELECT C.Course_code, C.Course_title, COUNT(C.Course_code) AS Attendees " & _
"FROM COURSEOFFERING O, COURSE C, STUDENT_COURSEOFFERING S " & _
"WHERE O.Course_code(+) = C.Course_code " & _
"AND O.Offering_id = S.Offering_id " & _
"GROUP BY C.Course_code, C.Course_title"

VB 2005 keeps complaining about a missing extra "(". Why???

 

[ArtieChoke]

Are you sure it's VB giving you the error? Is this at build time or run time? And what type of database are you accessing? I haven't seen that form of outer join syntax before.

What is the exact error?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[glenlove2002]

It's an outer join statement used for oracle databases, so i guess it wouldn't work after all, seeing as I'm using Access. However, I tried converting it to the ANSI JOIN format, and I'm still having problems:

sqlString = "SELECT C.Course_code, C.Course_title, COUNT(O.Course_code) AS Attendees " & _
"FROM ((COURSEOFFERING O RIGHT OUTER JOIN COURSE C " & _
"ON O.Course_code = C.Course_code) " & _
"INNER JOIN STUDENT_COURSEOFFERING S " & _
"ON O.Offering_id = S.Offering_id) " & _
"GROUP BY C.Course_code, C.Course_title"

In this case, I get an exception saying that the join statement is not supported.

Pls help

 

[ArtieChoke]

Try dropping the outer keyword (Access isn't ansi SQL).

I don't know if the old MS sql style works, but you can also try *= for left outer join and =* for right outer join in the where clause:

select * from a, b
where a.x *= b.x

"I think we're all Bozos on this bus!" - Firesign Theatre