Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID Sign In with Twitter
Sign In Apply for Membership

Categories

In this Discussion

Who's Online 14

connectrgarygaryleafmonstersarowlwp422 +9 guests

Someone care to work out this database bug for me?

MinisweeperMinisweeper
in Questions
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
    Posts: 4,883
    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
    Posts: 5,574
    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
    Posts: 4,883
    haha - nice solution :)
  • MinisweeperMinisweeper
    Posts: 5,574
    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.