SQL SELECT Syntax Question

[VoidState]

Hi

I am using php, ODBC and Filemaker Pro 5.5

Everything works fine except this query, which is making my filemaker db hang. Any idea what's wrong with it?

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' AND (accesslevel=0 OR accesslevel=1 OR accesslevel=2) ORDER BY UPPER(username) ASC

Its something to do with the (accesslevel=0 OR accesslevel=1 OR accesslevel=2) bit but according to what I know of SQL I'm sure that's the right syntax for an SQL query. Or is it...

Is there some limitations with Filemaker's implementation of SQL perhaps?

The field accesslevel is a number field so that's not the trouble.

Cheers for your help,

Ferg

PS I have also tried SELECT id, username, password, minsteraccesslevel FROM pw WHERE company='minster' AND minsteraccesslevel IN(0, 1, 2) ORDER BY UPPER(username) ASC with equal lack of success.

[VoidState]

After much playing around, I have found that by switching the OR part of the query to the beginning it works! The documentation on Filemaker ODBC is scanty at best, so I thought I'd post it here:

This works: SELECT id, username, password, accesslevel FROM pw WHERE accesslevel=0 OR accesslevel=1 OR accesslevel=2 AND company='minster' ORDER BY UPPER(username) ASC

Note there are no brackets. They cause it to hang, too.

These also work:

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' AND accesslevel BETWEEN 0 AND 2 ORDER BY UPPER(username) ASC

and:

SELECT id, username, password, accesslevel FROM pw WHERE company='minster' AND accesslevel >= 0 AND accesslevel <= 2 ORDER BY UPPER(username) ASC

[Garry Claridge]

Thank you.

Great information.

Garry

[Icecold]

I know to use ODBC to connect to my SQL Database in Visual basic with this code. My Question is If I have a filemaker database how would I connect to it using a ODBC. Some code example would be nice. Because I want to try to use Filemaker database with an Application I create in RealBasic. And If someone can show me how to connect to

filemaker In Realbasic that would really help me alot. To finish my project. As I am having a hard time in RealBasic

Dim adoPrimaryRS As Recordset

Dim db As Connection

Dim dbOpenStr As String

dbOpenStr = "PROVIDER=SQL;dsn="";uid=" & "" & ";pwd=" & "" & ";database=Stock;"

Set db = New Connection

db.CursorLocation = adUseClient

db.Open dbOpenStr

Set adoPrimaryRS = New Recordset

adoPrimaryRS.Open "select * from NEW INV where ITEM = '" & Newstring & "'", db, adOpenStatic, adLockReadOnly

[David W]

You could also try WHERE accessLevel in (0,1,2) AND ...

[truman_nasa]

Not adding anything to the conversation just want to loose the lurker title.