Why does this SQL statement not work.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why does this SQL statement not work.....

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2008-02-29 : 11:05:43

VB.NET hitting MySQL Express using a sqldataadapter

Here is my Sql statement


SELECT     FormName, FieldSeq, FieldTitle, FieldType, FieldLength, DecimalPlaces, CodeList, CodeAdd, CodeMask, TableName, AlternateTableName, FieldName, 
                      FieldRequired, CodeTableName, CanEdit, ToolTip, ImportantColor
FROM         FormFieldInfo
WHERE     (FormName = 'frmPolicy') AND (FieldSeq < 1000) AND (FieldName <> 'Split1') AND (FieldName <> 'Split2') AND (FieldName <> 'Split3') AND 
                      (FieldName <> 'Split4')
ORDER BY FieldSeq

This works great it gets the rows that i want except for there are 4 rows in which the FieldName value is Null and these are also being excluded. Can someone tell me why?

I have tried multiple variations on this select statement and either it doesn't exclude these rows or it excludes these and the nulls.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 11:08:22

Add this condition as well:

FieldName IS NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-02-29 : 11:09:59

if you want the null values, your where clause needs to be this:

WHERE FieldName is null OR
(
(FormName = 'frmPolicy') AND (FieldSeq < 1000) AND
(FieldName <> 'Split1') AND (FieldName <> 'Split2') AND
(FieldName <> 'Split3') AND (FieldName <> 'Split4')
)

elsasoft.org

demausdauth
Starting Member

17 Posts

Posted - 2008-02-29 : 11:10:24

I tried that ( did it again just to make sure ) and then i get no rows returned from the table.

demausdauth
Starting Member

17 Posts

Posted - 2008-02-29 : 11:13:53

jezemine -- just tried your suggestions and still not elimintating the 'Split1' etc..., but it does leave the null rows

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 11:14:35

Do it as shown by jezemine.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 11:15:29

NULL is a "not a known value".
So when you compare it to be not equal to 'SplitX' the parser cannot tell if the value is equal or not equal to 'SplitX'.
And disposes the record because it can't do a comparison.

SELECT		FormName,
		FieldSeq,
		FieldTitle,
		FieldType,
		FieldLength,
		DecimalPlaces,
		CodeList,
		CodeAdd,
		CodeMask,
		TableName,
		AlternateTableName,
		FieldName, 
		FieldRequired,
		CodeTableName,
		CanEdit,
		ToolTip,
		ImportantColor
FROM		FormFieldInfo
WHERE		FormName = 'frmPolicy'
		AND FieldSeq < 1000
		AND (FieldName NOT IN ('Split1', 'Split2', 'Split3', 'Split4') OR FieldName IS NULL)
ORDER BY	FieldSeq

E 12°55'05.25"
N 56°04'39.16"

demausdauth
Starting Member

17 Posts

Posted - 2008-02-29 : 11:19:52

peso -- works great thanks
I also learned something that i didn't know about sql in the comparisons of nulls (seems like i learn something new everyday)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources