Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Nov 21, 2018 3:06 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 6:42 pm 
Offline

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

This SQL command returns all records (even in the Pervasive SQL Control Center Tools). Can anyone tell me why?
select * FROM "TRANSACTION"; WHERE "TRANSACTION.Date In"= '2013-01-15';

Thank you,
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 6:50 pm 
Offline
User avatar

Joined: Wed May 10, 2006 2:42 pm
Posts: 2985
Location: Germany
There is an extra ; inbetween.

Greetings
Christian

_________________
See you in Orlando, Florida for Real World 2013
More details and registration here:
http://www.realsoftware.com/community/realworld.php


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 7:12 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
Hi Christian,
Hmm... I changed it but SQL did not like the new command. Changed again to this:
select * FROM "TRANSACTION" WHERE 'TRANSACTION.Date In'='2013-1-15'

Now SQL likes the command, but no records are returned. I also tried '2013-01-15' no records..... I tried other dates too, but they also did not return any results. However a command without restrictions reveals these dates all exist - sanity test passed.

Any ideas why it would be doing this?

Thanks,
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 7:25 pm 
Offline
User avatar

Joined: Wed May 10, 2006 2:42 pm
Posts: 2985
Location: Germany
Did you check errormessage on db?

Maybe the quotes for the table name are wrong?

Greetings
Christian

_________________
See you in Orlando, Florida for Real World 2013
More details and registration here:
http://www.realsoftware.com/community/realworld.php


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 7:32 pm 
Offline

Joined: Wed Mar 17, 2010 10:33 am
Posts: 258
Location: Sydney, Australia
If the field name has a space in IT enclose it in "double quotes".
http://www.sqlite.org/lang_keywords.html

ie; Quote the table name and the field names seperately.

try:
select * FROM "TRANSACTION" WHERE "TRANSACTION"."Date In"= '2013-01-15';


Does it really need to have a space in it?
If you have the choice, I'd suggest you not name anything with a space, using an underscore instead is pretty common practice,
and I wouldn't use a keyword to name anything either.


Regards
Denis


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 8:44 pm 
Offline

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

This is for a Pervasive SQL db file. It is an existing file and so I cannot change the DDF's for the column.

I tried breaking it up as you suggest, but it fails with a SQL Syntax error.
Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: SQL Select returns all records instead of desired
PostPosted: Sun Mar 10, 2013 8:46 pm 
Offline

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

Using the Pervasive tools themselves, there is no error reported when executing the SQL Command. It returns 0 rows:

<<<<<<<<<<<<<<<<<<<<<<<<
Account Alarm Number RemoteAddress Slot Module Device Number BldgNo Door No Lock_State Door_State Date In Time In Date Out Time Out Event Description Event Status GateCode Tenant FName Tenant LName ACK_Sent ACK_Received
=========== ============ ============= ====== ==================== ========== ========== ========== ========== ========== =========== ========== =========== ============================== ==================================================================================================== =========== ============================== ============================== ======== ============

0 rows were affected.

>>>>>>>>>>>>>>>>>>>>>>>>


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 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