Blog Documentation Book a Demo

Someone care to work out this database bug for me?

MinisweeperMinisweeper New
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,

Comments

  • 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...
  • MinisweeperMinisweeper New
    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.
  • MarkMark Vanilla Staff
    haha - nice solution :)
  • MinisweeperMinisweeper New
    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.