SQL statement

[carolineon]

I want to open a ADODB recordset with the codes below.

Set recSocket = New ADODB.Recordset
recSocket.Open strSql, cnn

But the program cannot execute
recSocket.Open strSql, cnn

The error message is
"ORA-00936:missing expression"

Can somebody please tell me why?This is urgent.
Prompt answer will be very appreciated.
Thank you.

[srinika]

Hint :
Put a break point & check whether the strSql is Correct (is it empty or null)!
Srinika

[d.paulson]

Post your SQL string. That is likely where your problem is.


David Paulson

[carolineon]

May i know if i can use INNER JOIN to join tables that i refer directly from Oracle (i use ADO)?


sql1 = "Select (required fields) From " & _
"table1 INNER JOIN " & _
"table2 INNER JOIN table3 " & _
"ON table2.MEASUREMENT=table3.MEASUREMENT " & _
"and table2.LOTNUMBER=table3.LOTNUMBER " & _
"and table2.CATEGORY=table3.CATEGORY " & _
"and table2.ROW_CNT=table3.ROW_CNT " & _
"ON table1.LOTNUMBER=table2.LOTNUMBER " & _
"and table1.MEASUREMENT=table2.MEASUREMENT "
Set recSocket = New ADODB.Recordset
recSocket.Open sql1, cnn

Another thing is i try this sql1 above and i got an error message "SQL command not properly ended".
What's wrong? i have to finish this tomorrow. Please help. Thank you.

[Cakkie]

First of all, your join expression isn't formed correct, you need to place some ( ) round sone parts, like this:

Select (required fields)
From table1 INNER JOIN (
table2 INNER JOIN table3
on (
table2.MEASUREMENT = table3.MEASUREMENT
and table2.LOTNUMBER=table3.LOTNUMBER
and table2.CATEGORY=table3.CATEGORY
and table2.ROW_CNT=table3.ROW_CNT
)
) on (
table1.LOTNUMBER=table2.LOTNUMBER
and table1.MEASUREMENT=table2.MEASUREMENT
)




About the other thing about the statement not being ended properly, try adding ; to the end, as I believe must be done when using oracle.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Strat]

Oracle does not support the INNER JOIN keywords to complete a join. You must construct it with something like:

select t1.measurement, t2.category
from table1 t1, table2 t2
where t1.lotnumber = t2.lotnumber

The t1 and t2 in the "from" line are aliases for table1 and table2 that simply make it easier to add type out the field names (you just have to type t1.fieldname instead of table1.fieldname)

If you wanted to make an outer join, you would simply add a (+) on one side of the join, like:

where t1.lotnumber = t2.lotnumber(+)
or
where t1.lotnumber(+) = t2.lotnumber

[carolineon]

hi strat,
now i change it back to DAO again and set the connection as below.

Set wspNew = CreateWorkspace("ODBCWorkspace", "Test_mon_access.mdb", "", dbUseODBC)
Set conn = wspNew.OpenConnection("connection", dbDriverNoPrompt, False, _
"ODBC;DATABASE=LPC_SERVER;UID=uid;PWD=psw;DSN=lpc_int")

and i open a recordset with :

Set recSocket = conn.OpenRecordset(strSql, dbOpenDynaset)

Can it support INNER JOIN now ???

[Strat]

The problem with the INNER JOIN keywords is simply that ORACLE does not understand them. To create an inner join in ORACLE, you need to use the SQL syntax similar to what I posted. It really doesn't matter if you're using DAO, RDO, or ADO.

I don't really work with Access databases too much, but I know that Access can use the INNER JOIN keywords. The code you posted using DAO to connect to an Access database should work.