Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Thu Jun 21, 2018 8:35 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: How to group SQL statements?
PostPosted: Sun Mar 17, 2013 10:44 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hello All,

I need to do something like:
select * from TABLE where FIELD1 >= Date1 and FIELD1 <= Date2 And (FIELD2 Like "abc" or FIELD2 Like "xyz" or FIELD3 Like "123") sort by Date1

Here is my code:
Select * from "TRANSACTION" where  And "TRANSACTION"."Date In" >= {d '2013-01-01'} And "TRANSACTION"."Date In" <= {d '2013-01-01'} And  ( "TRANSACTION"."Event Description" = 'Access Granted' Or "TRANSACTION"."Event Status" Like 'Entry Granted OK%' Or "TRANSACTION"."Event Status" Like 'Exit Granted OK%' Or "TRANSACTION"."Event Status" Like 'Internal Access Allowed%' )  order by  "TRANSACTION"."Date In", "TRANSACTION"."Time In" ASC;


I can get it all, except for the grouping of the Or's. No SQL error, but no results either.

Thanks in advance to all!
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: How to group SQL statements?
PostPosted: Sun Mar 17, 2013 11:34 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
Select *
from "TRANSACTION"
where "TRANSACTION"."Date In" >= {d '2013-01-01'}
And "TRANSACTION"."Date In" <= {d '2013-01-01'}
And ( "TRANSACTION"."Event Description" = 'Access Granted'
Or "TRANSACTION"."Event Status" Like 'Entry Granted OK%'
Or "TRANSACTION"."Event Status" Like 'Exit Granted OK%'
Or "TRANSACTION"."Event Status" Like 'Internal Access Allowed%' )
order by "TRANSACTION"."Date In", "TRANSACTION"."Time In" ASC;


Only thing I saw wrong was an "AND" right after the WHERE... .otherwise you group AND/OR with parends just like you did

Also... your transaction date comparison is overly verbose..

"Transaction".Date IN"=(d '2013-01-01') has the same effect. as you are saying >= and <= the same date value.

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to group SQL statements?
PostPosted: Sun Mar 17, 2013 11:40 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hi Dave!

Thanks for your response! I completely missed that! Will try it out now!
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: How to group SQL statements?
PostPosted: Sun Mar 17, 2013 11:45 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
I suppose it was a forest and trees issue here!

Thanks Dave! Your eyes fixed it!

Tim


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group