asked on

SQL Query - Select Statement Help

Good Day Experts!

I am having what seems to be a very straightforward issue but I cannot figure it out!

I am getting results as expected from this Select statement...about 35000 records with [Audit Reason 3] values being NULL:

Select
[Audit Reason 3],*
from
[Processing Table]
Where
[Process Week] > '5/1/2013'

I have added to this to only include records where [Audit Reason 3] <> 'Import History'. So I have this Select statement:

Select
[Audit Reason 3],*
from
[Processing Table]
Where
[Process Week] > '5/1/2013' and [Audit Reason 3] <> 'Import History'

NOW, I get no records! I can't figure out why [Audit Reason 3] <> 'Import History' is now causing no records to show.

Can you help?

Thanks,
jimbo99999

ralmada

maybe some trailing spaces?

rtrim([Audit Reason 3]) <> 'Import History'

or using like

[Audit Reason 3] not like 'Import History%'

SOLUTION

Ross Turner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Andrew Crofts

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

I just checked and the DataType is varchar. I will try the trailing spaces idea...thanks, I did not even think down that road.

Jimbo99999

ASKER

I tried the following and received no records returned:

rtrim([Audit Reason 3]) <> 'Import History'
[Audit Reason 3] not like 'Import History%'
[Audit Reason 3] not like 'Import History'

I tried the following and it worked:

isnull([Audit Reason 3],'') not like '%Import History%'

Ok, problem is I do not understand why it worked and the others didn't! I mean NULL is <> 'Import History' so I guess that is where I am confused.

jimbo99999

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

And now that I'm thinking, I guess you don't even have a trailing spaces problem. So I would just do

Select
[Audit Reason 3],*
from
[Processing Table]
Where
[Process Week] > '5/1/2013' and ([Audit Reason 3] <> 'Import History' or [Audit Reason 3] is null)

Jimbo99999

ASKER

Thank you all for your repsonses. I have archived all for future reference.

Have a good day,
jimbo99999

Andrew Crofts

Thanks