'LIKE' operator and SQL query

Octopod · Post by **Octopod** » Sat Aug 21, 2004 5:00 am

Hi,

I use the following from MS-Access with my MM DB (and it works):

SELECT COUNT(*) FROM (SELECT DISTINCTROW Artists.Artist FROM Artists INNER JOIN Songs ON Songs.IDArtist = Artists.ID WHERE Songs.SongPath like '*Rock*')

While setting this in a MM script always return '0', can someone explain what's wrong? Problem with special characters like '*' when the string is parsed??

Code: Select all

Set QueryTest = SDB.Database.OpenSQL("SELECT COUNT(*) FROM (SELECT DISTINCTROW Artists.Artist FROM Artists INNER JOIN Songs ON Songs.IDArtist = Artists.ID WHERE Songs.SongPath Like '*Rock*')")

(Using "NOT LIKE" always returns the total number of albums...)

Thanks a lot!

Post by **jiri** » Sat Aug 21, 2004 5:13 am

How do you continue after that 'Set QueryTest = ...' line? Check 'Sample New Window - Report.vbs' script, there is the following used:

Set QueryRes = SDB.Database.OpenSQL( "SELECT COUNT(*) FROM Songs")
Doc.Write "Track count: " & QueryRes.StringByIndex(0) & "<br>"

Do you mean that 'QueryRes.StringByIndex(0)' returns '0'?

Jiri

Octopod · Post by **Octopod** » Sat Aug 21, 2004 5:41 am

jiri wrote:How do you continue after that 'Set QueryTest = ...' line? Check 'Sample New Window - Report.vbs' script, there is the following used:

Set QueryRes = SDB.Database.OpenSQL( "SELECT COUNT(*) FROM Songs")
Doc.Write "Track count: " & QueryRes.StringByIndex(0) & "<br>"

Do you mean that 'QueryRes.StringByIndex(0)' returns '0'?

Jiri

I started my script from the sample you mentionned.
Here is the code:

Code: Select all

    Set QueryRock = SDB.Database.OpenSQL("SELECT COUNT(*) FROM (SELECT DISTINCTROW Artists.Artist FROM Artists INNER JOIN Songs ON Songs.IDArtist = Artists.ID WHERE Songs.SongPath Like '*Rock*')")
    Doc.Write "<LI>Rock: " &QueryRock.StringByIndex(0)& "</LI><br>"
    Set QueryPop = SDB.Database.OpenSQL("SELECT COUNT(*) FROM (SELECT DISTINCTROW Artists.Artist FROM Artists INNER JOIN Songs ON Songs.IDArtist = Artists.ID WHERE Songs.SongPath Like '*Pop*')")
    Doc.Write "<LI>Pop: " &QueryPop.StringByIndex(0)& "</LI><br>"

And yes the result is always '0' whenever i use the LIKE operator (ok if i use '=' and/or '<>', and so on...)

Post by **jiri** » Sat Aug 21, 2004 9:06 am

Ok, I see. The problem is that LIKE syntax differs by connection method to DB. MM connects through ODBC and the correct syntax in this case is: SongPath Like '%Pop%'.

Anyway, shouldn't the query be rather based on Genre field?

Jiri

Octopod · Post by **Octopod** » Sat Aug 21, 2004 7:07 pm

jiri wrote:MM connects through ODBC and the correct syntax in this case is: SongPath Like '%Pop%'.

Right. Works better now.

Thanks.

jiri wrote:Anyway, shouldn't the query be rather based on Genre field?

This was an example