Vanilla 1 is no longer supported or maintained. If you need a copy, you can get it here.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Someone care to work out this database bug for me?

edited October 2005 in Vanilla 1.0 Help
So i'm just using a piece of software we have here for our client management stuff. I need to get a list of all the clients with a number below 1000. Now i can write my own sql statements and get them to perform it for me so it should be simple, right? So i run a statement such as SELECT ...... FROM Clients WHERE Client.AccountNumber < "999" And it returns all the clients. When i search for clients less than 1000 it returns the client number 100. It seems like the client number record isnt just an int, but i cant work out what it is. I've never come across anything like this before... Is there any search i can run to actually get all the client numbers below 1000? Needless to say the helpdesk arent answering,


  • Options
    MarkMark Vanilla Staff
    Well, first of all, if you're searching an int field you don't need to put the 999 in quotes. I don't know if that would make a difference, but you never know.

    select * from clients where accountnumber < 1000

    should work fine...
  • Options
    yeah, when i tried it without it gave me a data mismatch error - thus why i was using them. In the end i got through to the helpdesk and it turns out that AccountNumber was actually an alphanumeric field (when the last time there was an alphanumeric *number* i'm not entirely sure) so using a lessthan statement confused it. I ended up just using a statement checking for when the client number was less than 4 digits long which worked just fine.
  • Options
    MarkMark Vanilla Staff
    haha - nice solution :)
  • Options
    Yeah - the helpdesk took over my pc and wrote the statement for me. Though they also made a huge where clause to check that clientnumber had one of the digits 0-9 in which was a little reduntant so i took that out and edited the statement a little more. Hats off to them for realising that it i just wanted less than 4 digit numbers. What they'd have done if i wanted all the client numbers less than 1500 i'm not entirely sure. Then again, when i rang up earlier in the week to ask how to do a search for surnames containing a series of letters i was told it was impossible. Which seemed a little odd. Obviously they hadnt heard of wildcards.
This discussion has been closed.