Need to verify and "AND" tsql

[sapator]

Hi.

select * from X
where ....etc

and not (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null)
and not (transi_strtype = 'S' and TransI_strStatus = 'R')

What I need to do is not remove every transaction that is not empty or null on TransI_strReceiptNumber but also the transaction should not be of transi_strtype = 'S' and TransI_strStatus = 'R'


So if a transaction is like so
Trid transi_strtype TransI_strStatus TransI_strReceiptNumber
1000 S R NULL

It needs not to appear in the select statement.

Is the above correct? I think it is but I'm not 100% sure on the parenthesis. Should I just put a big one (parenthesis) and include the whole statement?
p.e. -- and not ( (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null) and (transi_strtype = 'S' and TransI_strStatus = 'R') )

[TysonLPrice]

You should mention what DBMS you are on. This is MS SQL:

Not tested:

select * from X
where (isnull(TransI_strReceiptNumber,'') ='' and isnull(transi_strtype,'') not in('S','R'))

[Spooman]

Sapator

As an alternative to TP's approach, I used this

SELECT #1

strSQL = "SELECT * FROM Authors WHERE [Year Born] <> Null"

SELECT #2

            strSQL = "SELECT * FROM Authors WHERE [Year Born] <> Null " & _
                "and Author <> Null " & _
                "and not Author = ' ' " & _
                "and not Au_id = 611 " & _
                "and not [Year Born] = 1947"

to get this



Let me explain

1. The top left FlexGrid contains 6290 records
   
   • in rr 3, Author is " "
   • in rr 4, Author is Null
2. The bot left FlexGrid uses SELECT #1 .. eliminates Null Birthdates .. [Year Born]
3. The bot right FlexGrid uses SELECT #2 .. eliminates also ..
   
   • Author is " "
   • Author is Null
   • ID is 611 .. record 6
   • Birthdate = 1947 .. records 5,8,17

HTH

EDIT-1

Thus, you might want to change your statement from this

select * from X
where ....etc

and not (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null)
and not (transi_strtype = 'S' and TransI_strStatus = 'R')

... to this

select * from X
where ....etc

and TransI_strReceiptNumber <>'' 
and TransI_strReceiptNumber <> null
and transi_strtype <> 'S' 
and TransI_strStatus <> 'R'

.. where the above is pseudo-code.
Natch, you'll need to adopt the more formal syntax I used above for SELECT #2.

EDIT-2

BTW, I tried several variations of your approach, ie, using ( .. ), and got unexpected results or error statements. I can provide those if you think it would be helpful.

Spoo

[techgnome]

Should I just put a big one (parenthesis) and include the whole statement?
p.e. -- and not ( (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null) and (transi_strtype = 'S' and TransI_strStatus = 'R') )

In this case, the result is the same either way, so it doesn't matter... personally I see them as two different (albeit related) conditions, so my personal preference is to keep them as two conditions.

That said...

select * from X
where ....etc

and not (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null)
and not (transi_strtype = 'S' and TransI_strStatus = 'R')

You're looking at it from an exclusionary POV, which is why your where is full of nots... I was having trouble with it because it didn't feel right, then I figured it out. I tend to think from an inclusion POV... instead of thinking about what you want to toss out, think about what you want to keep in:
Stuff that has a receipt number, and where the transo_strType isn't "S" and the TransI_strStatus isn't "R"... now it almost writes itself:

select * from X
where ....etc

and isnull(TransI_strReceiptNumber'' <> ''
and (transi_strtype <> 'S' and TransI_strStatus <> 'R')

Now it reads in a more positive manner: give me records where there is a ReceiptNumber, AND where the strType isn't "S" and the strStatus isn't "R"
Theoretically now you can get rid of the parenthesis too, depending on what else you have in your where clause.
But based on what you've given, that's how I'd structure that.

-tg

[Spooman]

.. reply moved to EDIT-1 of my post #3

TG snuck in a post and ruined my continuity ..

[techgnome]

and TransI_strReceiptNumber <> null -- I'd argue that because things cannot be equal to NULL, they are null are not null, this should be "is not null" instead of "<> null"

-tg

[szlamany]

<> NULL will not work. = and <> are reserved for non null values.

[Spooman]

SZ

<> NULL will not work. = and <> are reserved for non null values.

I beg to differ with you on that.

I used <> NULL in my SELECT #1 in post #3
It not only worked, but it reduced the recordcount from 6290 to 22.

EDIT-1

Presumably, if one couldn't use <> NULL, the alternatives would be

• Not IsNulll()
• Not Null
• Not Is Null

           strSQL = "SELECT * FROM Authors WHERE [Year Born] not IsNull()"
..or ..
           strSQL = "SELECT * FROM Authors WHERE [Year Born] not Null"
..or ..
           strSQL = "SELECT * FROM Authors WHERE [Year Born] not is Null"

Indeed, all 3 cause an error (I've just grabbed the 2nd one) ..



Do you have another suggested syntax?


Spoo

[sapator]

I think the syntax is ' WHERE [Year Born] is not null ' but let's not get on your turf.

I have a little trouble understanding techgnome's ' and isnull(TransI_strReceiptNumber'' <> '' '
(TransI_strReceiptNumber'' ?
Is it maybe (TransI_strReceiptNumber,'' <> '') ?

All in all does my initial query works as expected. No matter if I use it or not?

Thanks.

Edit. No matter if <> Null is valid or not (in some language), if you go to an interview and use it like this you will be banned. It may work in access (isn't that what you are using?) but access isn't really TSQL correct, isn't it now?

[techgnome]

I had to go back to see what I wrote - and isnull(TransI_strReceiptNumber'' <> '' yeah, I'm not sure what I was thinking... that's clearly not right... it should have been
and isnull(TransI_strReceiptNumber,'') <> ''

-tg

[szlamany]

@spooman - I assumed from the thread title (TSQL) and knowing what sapator works on that this was in fact MS SQL with T-SQL.

Syntax for touching nulls is:

FIELD IS NOT NULL

or

FIELD IS NULL

or

IsNull(FIELD,'value if it is null)= or <> whatever you want now since the FIELD is NO LONGER NULL.

I know nothing about ACCESS - I am a T-SQL coder

[Spooman]

Sorry guys .. didn't notice the "t"

Yes .. I was using MS SQL
I've never heard of T-SQL

[TysonLPrice]

and TransI_strReceiptNumber <> null -- I'd argue that because things cannot be equal to NULL, they are null are not null, this should be "is not null" instead of "<> null"

-tg

As with a lot things in our business "it depends"...

Try this on MS SQL

DECLARE @val CHAR(4)

SET @val = NULL
SET ANSI_NULLS ON
If @val =NULL
            PRINT 'TRUE'
ELSE
            PRINT 'FALSE'

SET ANSI_NULLS OFF
If @val =NULL
            PRINT 'TRUE'
ELSE
            PRINT 'FALSE'

I don't have a link to this anymore but here is a more detailed explanation from where I ran into that:

Understanding the difference between “IS NULL” and “= NULL”

When a variable is created in SQL with the declare statement it is created with no data and stored in the variable table (vtable) inside SQLs memory space. The vtable contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory.

When you SET the variable it is allotted a memory address and the initial data is stored in that address. When you SET the value again the data in the memory address pointed to by the variable is then changed to the new value.

Now for the difference and why each behaves the way it does.

“= NULL”

“= NULL” is an expression of value. Meaning, if the variable has been set and memory created for the storage of data it has a value. A variable can in fact be set to NULL which means the data value of the objects is unknown. If the value has been set like so:

DECLARE @val CHAR(4)

SET @val = NULL

You have explicitly set the value of the data to unknown and so when you do:

If @val = NULL

It will evaluate as a true expression.

But if I do:

DECLARE @val CHAR(4)

If @val = NULL

It will evaluate to false.

The reason for this is the fact that I am checking for NULL as the value of @val. Since I have not SET the value of @val no memory address has been assigned and therefore no value exists for @val.

Note: See section on SET ANSI_NULLS (ON|OFF) due to differences in SQL 7 and 2000 defaults that cause examples to not work. This is based on SQL 7.

“IS NULL”
Now “IS NULL” is a little trickier and is the preferred method for evaluating the condition of a variable being NULL. When you use the “IS NULL” clause, it checks both the address of the variable and the data within the variable as being unknown. So if I for example do:

DECLARE @val CHAR(4)

If @val IS NULL

PRINT ‘TRUE’
ELSE
PRINT ‘FALSE’

SET @val = NULL

If @val IS NULL

PRINT ‘TRUE’
ELSE
PRINT ‘FALSE’

Both outputs will be TRUE. The reason is in the first @val IS NULL I have only declared the variable and no address space for data has been set which “IS NULL” check for. And in the second the value has been explicitly set to NULL which “IS NULL” checks also.

SET ANSI_NULLS (ON|OFF)

Now let me throw a kink in the works. In the previous examples you see that = NULL will work as long as the value is explicitly set. However, when you SET ANSI_NULLS ON things will behave a little different.

Ex.

DECLARE @val CHAR(4)

SET @val = NULL

SET ANSI_NULLS ON

If @val =NULL

PRINT ‘TRUE’

ELSE

PRINT ‘FALSE’

SET ANSI_NULLS OFF

If @val =NULL

PRINT ‘TRUE’
ELSE
PRINT ‘FALSE’

You will note the first time you run the = NULL statement after doing SET ANSI_NULLS ON you get a FALSE and after setting OFF you get a TRUE. The reason is as follows.

Excerpt from SQL BOL article “SET ANSI_NULLS”

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

End Excerpt

So as defined by SQL92, “= NULL” should always evaluate false. So even setting the value explicitly means you will never meet the = NULL if condition and your code may not work as intended. The biggest reason where = NULL will shoot you in the foot is this, SQL 7 when shipped and installed is defaulted to ANSI_NULL OFF but SQL 2000 is defaulted to ANSI_NULL ON. Of course you can alter this several ways but if you upgraded a database from 7 to 2000 and found the = NULL worked only when you set if explicitly when you roll out a default 2000 server your code now breaks and can cause data issues.

Yet another reason to use IS NULL instead as under SQL 92 guidelines it is still going to evaluate to TRUE and thus your code is safer for upgrading the server.

Summary

If summary unless you need to check that the value of a variable was set to equal NULL and you have set ANSI_NULLS ON, then always use the “IS NULL” clause to validate if a variable is NULL. By using = NULL instead you can cause yourself a lot of headaches in trying to troubleshoot issues that may arise from it, now or unexpectedly in the future.

Basis


Some of the information provided comes from how C++ works and how SQL behaves under each circumstance. Unfortunately, SQL as far as I know does not have an addressof function to allow me to output the actual memory address to show what occurs under the hood. In C++ when a variable is created the variable has an address of 0xddddddd (in debug but it can be different non-real addresses as well). When you set the variable the first time checking the address will give you a valid memory address where the data is being stored. Also, more information can be obtained from SQL Books Online in the sections on IS NULL and SET ANSI_NULLS….

[Spooman]

I think the syntax is ' WHERE [Year Born] is not null ' but let's not get on your turf.

You are right .. that works fine on my turf. ..

All in all does my initial query works as expected. No matter if I use it or not?

Returning to your turf .. seems like you are still looking for an answer. Correct?

• If you are still looking, did you try my pseudo-code in my post #3 .. EDIT-1?
• If you have a solution, could you post it?

Spoo

[sapator]

Actually I left that for now as we are running like crazy with another issue.

I think I would leave the initial code or try techgnome's (but is similar to yours) as is more compact and I also like to parenthesize things that they belong to a, how to say it, they are bound together, as transi_strtype and TransI_strStatus will always need to be check as one entity, so if I used an "or" without parenthesis, all hell would broke loose.

However, having said that, I am not sure if the isnull will put some non sargable issues on an execution plan. I have tested in the past an did not see any issues with isnull but I remember someone here specifying that isnull may break sargability (?).

[Spooman]

Sapator

OK.
And I generally concur with your desire to parenthesize "common" issues.

That said, I can at least offer this regarding the MS SQL world ..

This works

            strSQL = "SELECT * FROM Authors WHERE [Year Born] is not Null " & _
                "and Author is not Null " & _
                "and not Author = ' ' " & _
                "and not Au_id = 611 " & _
                "and not [Year Born] = 1947"

This does not

            strSQL = "SELECT * FROM Authors WHERE [Year Born] is not Null " & _
                "and Author is not Null " & _
                "and not Author = ' ' " & _
                "and not (Au_id = 611 and  [Year Born] = 1947)"

It returns records where Au_id = 611 and where [Year Born] = 1947

Spoo

[sapator]

How about.
and not Au_id = 611 or [Year Born] = 1947
vs
and not (Au_id = 611 or [Year Born] = 1947) ?

This will make the second one work.

So it depends on what you need.

[Spooman]

Sapator

Nope, removing paren's and using or does not work .. it does not eliminate the 1947's

            strSQL = "SELECT * FROM Authors WHERE [Year Born] is not Null " & _
                "and Author is not Null " & _
                "and not Author = ' ' " & _
                "and not Au_id = 611 or  [Year Born] = 1947"

However, keeping the paren's and using or DOES work. Both the 611's AND 1947's are eliminated

            strSQL = "SELECT * FROM Authors WHERE [Year Born] is not Null " & _
                "and Author is not Null " & _
                "and not Author = ' ' " & _
                "and not (Au_id = 611 or  [Year Born] = 1947)"

BTW, "my turf" has all along been my effort to match your OP.
You had only shown pseudo-code, so I chose a sample Access MDB included with VB6 with which to test

Thus, regarding your OP ..

select * from X
where ....etc

and not (TransI_strReceiptNumber ='' or TransI_strReceiptNumber is null)
and not (transi_strtype = 'S' and TransI_strStatus = 'R')

.. the following were meant to be comparable

• my Au_id = 611 for your transi_strtype = 'S'
• my [Year Born] = 1947 for your TransI_strStatus = 'R'

EDIT-1

However, I now occurs to me that I may have mis-interpreted your needs

Trid transi_strtype TransI_strStatus TransI_strReceiptNumber
1000 S R NULL

You want to eliminate records only if all 3 are as shown .. ie

Trid transi_strtype TransI_strStatus TransI_strReceiptNumber
1000 S              R                NULL    .. eliminate
1000 S              R                empty   .. eliminate
1000 S              R                2100    .. keep
1000 S              S                2100    .. keep

Is this correct?

Spoo

[sapator]

Yes removing parenthesis does not work. That was the point. You need the parenthesis in that example.

I want to eliminate only if 2 are shown and the 3rd is empty or null
R - S - (empty-null) = eliminate - kill kill kill!.

But i think we have covered this.