| View previous topic :: View next topic |
| Author |
Message |
mattnotfat
Power User
Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK
|
 Posted: Mon Oct 30, 2006 9:51 am Post subject: SQL SELECT using LIKE statement |
 |
|
Hello Braniacs,
Can anyone help me out with a valid SQL statement using LIKE. In the code below 'DeptCode' is a six character code, 'strCode' is the first four charcters - but I don't get any results. The data source is a spreadsheet.
| Code: | oStatement = oConnectionEstablishment.createStatement()
strStatement = "SELECT * FROM " & CHR$(34) & "Sheet1" & CHR$(34) & " WHERE " & CHR$(34) & "DeptCode" & CHR$(34) & " LIKE " & CHR$(39) & strCode & CHR$(39)
oRSEstablishment = oStatement.executeQuery(strStatement)
|
Many thanks
_________________
The early bird gets the worm, but the second mouse gets the cheese! |
|
| Back to top |
|
 |
DiGro
Super User
Joined: 02 Jun 2004
Posts: 1209
Location: Hoorn NH, The Netherlands
|
| Posted: Mon Oct 30, 2006 9:56 am Post subject: Re: SQL SELECT using LIKE statement |
|
|
| mattnotfat wrote: | | strStatement = "SELECT * FROM " & CHR$(34) |
Well, I'm not the best in SQL but don't I miss something behind FROM ?
There is only one quotation
_________________
DiGro
Windows 7 Home Premium and OOo 3.3 NL (Dutch) |
|
| Back to top |
|
|
mattnotfat
Power User
Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK
|
| Posted: Mon Oct 30, 2006 10:16 am Post subject: |
|
|
Well, I think my statement is valid - it executes no problem.
I reckon there must be something wrong with the LIKE part as it's giving me no results?
_________________
The early bird gets the worm, but the second mouse gets the cheese! |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Mon Oct 30, 2006 10:38 am Post subject: |
|
|
mattnofat:
With HSQLDB, your LIKE statement ( what you are looking for ) needs to be 'surrounded' with % on each side.
To quote from HSQL documentation http://www.hsqldb.org/doc/guide/ch09.html :
| HSQL documentation wrote: | | The LIKE keyword uses '%' to match any (including 0) number of characters, and '_' to match exactly one character. To search for '%' or '_' itself an escape character must also be specified using the ESCAPE clause. For example, if the backslash is the escaping character, '\%' and '\_' can be used to find the '%' and '_' characters themselves. For example, SELECT .... LIKE '\_%' ESCAPE '\' will find the strings beginning with an underscore. |
This is an example of an SQL statement, using LIKE to find any e in the LastName:
| Code: | SELECT
"FirstName",
"LastName"
FROM "Test" AS "Test"
WHERE "LastName" LIKE '%e%' |
Please also note, CHR$(37) will provide the % you need to add.
I hope this helps, please be sure to let me / us know.
Sliderule |
|
| Back to top |
|
|
mattnotfat
Power User
Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK
|
| Posted: Wed Nov 01, 2006 4:36 am Post subject: Ah, so that's what it was! |
|
|
Thanks Sliderule
The problem wasn't so much the syntax for the like statement, it was the way I was checking for the number of results.
After getting the results I did this | Code: | oRSEstablishment.first()
if not oRSEstablishment.isLast() then
do
oRSEstablishment.next()
count = count + 1
loop until oRSEstablishment.isLast()
End if
msgBox "There are : " & count & " results" |
And got no results, wheras if I do this: | Code: | oRSEstablishment.last()
msgBox "Establishment row is: " & oRSEstablishment.Row()
|
I get the right number of results. I wrote the code the first way because that's how I plan to iterate through the results - so it remains to be seen if I'm going to have problems making use of the result set.
Will report back soon...
_________________
The early bird gets the worm, but the second mouse gets the cheese! |
|
| Back to top |
|
|
davelegge
OOo Enthusiast
Joined: 30 Nov 2005
Posts: 112
|
| Posted: Wed Nov 01, 2006 1:57 pm Post subject: Re: Ah, so that's what it was! |
|
|
| mattnotfat wrote: | ....<snip>....wheras if I do this:I get the right number of results. I wrote the code the first way because that's how I plan to iterate through the results - so it remains to be seen if I'm going to have problems making use of the result set.
Will report back soon... |
So why not use
| Code: | oRSEstablishment.last()
RSEcount = oRSEstablishment.Row()
For i = 1 to RSEcount . . . . |
In your first code clip
| Code: | oRSEstablishment.first()
if not oRSEstablishment.isLast() then
do
oRSEstablishment.next()
count = count + 1
loop until oRSEstablishment.isLast()
End if |
the do loop wil never get executed if there is only one row,
since the first row is also the last row!
if you do not like the for loop try something like
| Code: | oRSEstablishment.beforefirst()
do while not oRSEstablishment.isLast()
count = count + 1
oRSEstablishment.next()
loop |
_________________
Dave Legge
==========
Computers allow me to make mistakes
far easier and quicker
than I would otherwise make them |
|
| Back to top |
|
|
mattnotfat
Power User
Joined: 05 Jan 2006
Posts: 58
Location: Bristol, UK
|
| Posted: Mon Nov 06, 2006 4:51 am Post subject: |
|
|
Thanks Dave,
I did use a For loop in the end. I don't have anything against For loops - and maybe it's just lack of sleep fogging my brain, but don't get why my first version wasn't working at all. I had 178 results in the ResultSet yet doing[code]ResultSet.first() then followed it with [code]ResultSet.next()[/code] and the call to [code]the call to ResultSet.last()[/code] returned True.
Is is my logic or the ResultSet object that is a bit strange?
_________________
The early bird gets the worm, but the second mouse gets the cheese! |
|
| Back to top |
|
|
davelegge
OOo Enthusiast
Joined: 30 Nov 2005
Posts: 112
|
| Posted: Mon Nov 06, 2006 9:13 am Post subject: |
|
|
| mattnotfat wrote: | Thanks Dave,
....
Is is my logic or the ResultSet object that is a bit strange? |
Probably 
_________________
Dave Legge
==========
Computers allow me to make mistakes
far easier and quicker
than I would otherwise make them |
|
| Back to top |
|
|
|
