SQL Syntax question

[demoman]

Hi

I am using Pervasive 2000 (SP2), through an ODBC connection. Can someone explain why following syntax works:

select * FROM ACTIVITY_ARA_ACTIVITY AAA, ACTIVITY_ARA_STATUS AAS where AAA.Invoice = AAS.Invoice

and this syntax does NOT work, even though it is documented in the Pervasive 'help'.

select * FROM ACTIVITY_ARA_ACTIVITY AAA inner join ACTIVITY_ARA_STATUS AAS ON AAA.Invoice = AAS.Invoice

Here is how I am connecting:

Dim cnGold As adodb.Connection
Dim rsAR As adodb.Recordset

Set cnGold = New adodb.Connection
Set rsAR = New adodb.Recordset

With cnGold
.ConnectionString = "Provider=MSDASQL;User ID=steve;password="";Data Source=TSGold;"
.CursorLocation = adUseServer
.Mode = adModeReadWrite
.Open
End With


Thanks

 

[Linda84]

Without some research, I can't say why those two statements generate different results, but I do know that there are a lot of SQL-related syntax/engine changes and fixes between SP2 and SP4. Do you see the same problem with SP4? I've seen both forms of joins work in SP4.

In general, both formats for joins are supported, and the only "guide" is that you stick to one or the other in a given query for best performance. Usually we'll see a query needing to do both an Outer Join and an inner join mixing both types of join syntax. An outer join can only be done using the second syntax you showed, so if you need outer joins, use that same syntax for the inner joins in the same query.

 

[demoman]

Thanks Linda!

I want to upgrade to SP4, but I cannot. We use Timberline and they have a very short list of users on SP4. I have been waiting for nearly a week to see if we can get their latest release. So far, no response.

What I am trying now is to create a view with the Inner join syntax I CAN get to work, and another view with the outer joins. Then I am going to link them using a third (inner) join. Seems like a lot of extra overhead, but that is what happens when things do not work as advertised.

Thanks
Steve