empty DB columns are not ''NOT NULL'' so search for not null fails

  • Nope... gave credit where credit was due... I got lazy and said "heck, they probably don't have the right kind of index, anyway, so why bring it up?"  You reminded me to make sure to "play the lottery" so you at least have the opportunity to win.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • wow!, you guys really get stuck in, don't you? I am the original poster and I posted a solution which worked for me. right after the one where I said I would get back to you. I used a WHERE field <>'' and that was enough for my data. For some reason my post was removed from the thread.

    You're right Jeff, I don't have an index but looking at this, I guess I should start working smart.

    At the moment I'm going for 'if it aint broke...'

  • heh... you also bring up another great point... (maybe not intentionally)... If you do like you say with WHERE field <> '', it will inherently NOT find nulls because nulls can be compared against with <> unless you change the default settings on the database (bad idea).  It's like having an implicit IS NOT NULL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I agree that it works with the default settings but I do not like to depend on the settings too much... Unless I am dealing with a very controlled environment (read it: I am controlling it )

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

Viewing 4 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply