No Data Being Returned?

  • I'm having a weird issue that I hope someone might be able to shed some light on. I have a master table with almost 200,000 records in it. I then have a transaction queue that keeps records that have to be added to the master. Before adding the records in the queue to the master, a check has to be done that they don't already exist. For some reason, I have a couple of records that I know don't exist but the system won't add them because it thinks they do. The following two queries are diametrically opposed and either one or the other should return data but neither do. How is this possible?

    select * from #dtl where id not in (select PmId from cip.dbo.tblGlJrnl where acctid like '03%')

    select * from #dtl where id in (select PmId from cip.dbo.tblGlJrnl where acctid like '03%')

    If I execute the sub-query just on it's own, it returns 177,000+ records. There are two records in the #dtl table (which is just a table I'm playing with to simplify things and not the actual queue table used normally):

    id

    180608

    181672

    If I copy and paste the results from the sub-query in to a text editor and then search for the 2 numbers above, neither is found. Therefore, the first query above should return the 2 records. Regardless, if the first query doesn't return something, then the second query should.

    What am I missing?

  • Never mind... The issue was there was some null values being returned by the sub-query and this was messing things up.

  • I was just about to respond with that - glad you figured it out.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

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