Solved

sql My select statement

Posted on 2014-12-15
8
183 Views
Last Modified: 2014-12-16
Hi,

I have a select statement that I want to filter out any null value but instead my query display the null value.

I just want to display the record if one of those field has a value otherwise don't.

select EditTime, AppID, AppName, MajorUpgradeCost, PlannedUpgradeYear, UpgradeStrategy, SoftwareVersion,
OldestVersionSupportedBySupplier, EmployeeName
 from EDIT_ApplicationDescription
 where MajorUpgradeCost != '' or PlannedUpgradeYear != '' or UpgradeStrategy != '' or SoftwareVersion != ''
 or OldestVersionSupportedBySupplier != '' or EmployeeName != ''
 order by EditTime desc

Open in new window



the output that I am getting is this:

EditTime	AppID	AppName	MajorUpgradeCost	PlannedUpgradeYear	UpgradeStrategy	SoftwareVersion	OldestVersionSupportedBySupplier	EmployeeName
58:43.0	52	test1 	NULL	NULL	NULL	NULL	NULL	NFOCO
58:02.0	52	test12	NULL	NULL	NULL	NULL	NULL	NFOCO
01:00.0	139	test3	NULL	NULL	NULL	NULL	NULL	CBARR
10:20.0	1687	test5	NULL	NULL	NULL	NULL	NULL	CBARR
03:28.0	2	test8	NULL	NULL	NULL	NULL	NULL	CBARR
51:53.0	68	test9	NULL	NULL	NULL	NULL	NULL	CBARR
31:54.0	1771	test10	NULL	NULL	NULL	NULL	NULL	CBARR
17:15.0	352	test14	NULL	NULL	NULL	NULL	NULL	CBARR
42:53.0	167	test15	NULL	NULL	NULL	NULL	NULL	CBARR
12/12/2014 14:35	7942	test17	$100K to $500K	2016	Apply as soon as available	FormSuite v4 for .NET	FormSuite v4 for .NET	c602518
12/12/2014 14:35	7942	test20	$100K to $500K	2016	Apply as soon as available	FormSuite v4 for .NET	FormSuite v4 for .NET	c602518
12/12/2013 12:59	8069	test25	NULL	NULL	NULL		NULL	a104154
12/12/2013 12:59	8068	test26	NULL	NULL	NULL		NULL	a104154

Open in new window


but the output that I want is this:

EditTime	AppID	AppName	MajorUpgradeCost	PlannedUpgradeYear	UpgradeStrategy	SoftwareVersion	OldestVersionSupportedBySupplier	EmployeeName
12/12/2014 14:35	7942	test17	$100K to $500K	2016	Apply as soon as available	FormSuite v4 for .NET	FormSuite v4 for .NET	c602518
12/12/2014 14:35	7942	test20	$100K to $500K	2016	Apply as soon as available	FormSuite v4 for .NET	FormSuite v4 for .NET	c602518

Open in new window

0
Comment
Question by:lulu50
8 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40501122
use "MajorUpgradeCost is not null or ..."
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40501124
or use this

coalesce(MajorUpgradeCost, '') != '' or ...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40501127
or add more code and make it ugly...

MajorUpgradeCost IS not NULL and MajorUpgradeCost != '' and...

I guess you should convert all "or" to "and" as well...
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 51

Expert Comment

by:HainKurt
ID: 40501133
just read the question again :) you should use "or"

this should work

coalesce(MajorUpgradeCost, '') != '' or coalesce(PlannedUpgradeYear, '') != '' or ...
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40501245
Is this about MsSql or MySQL? The former will not match nulls by default, do either you use MySQL or have unset the ANSI SQL NULL.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40501960
Using functions on data might looks compact and neat, but it isn't great for performance as it can remove the ability to use indexes (making a query slower) see sargable

I recommend using IS NOT NULL it's easier to read in my view. Note for each field you would need your existing condition AND the IS NOT NULL condition within parentheses.
SELECT
      EditTime
    , AppID
    , AppName
    , MajorUpgradeCost
    , PlannedUpgradeYear
    , UpgradeStrategy
    , SoftwareVersion
    , OldestVersionSupportedBySupplier
    , EmployeeName
FROM EDIT_ApplicationDescription
WHERE (MajorUpgradeCost <> ''  AND MajorUpgradeCost IS NOT NULL)
OR (PlannedUpgradeYear <> ''  AND PlannedUpgradeYear IS NOT NULL)
OR (UpgradeStrategy <> '' AND UpgradeStrategy IS NOT NULL)
OR (SoftwareVersion <> ''  AND SoftwareVersion IS NOT NULL)
OR (OldestVersionSupportedBySupplier <> '' AND OldestVersionSupportedBySupplier IS NOT NULL)
OR (EmployeeName <> ''  AND EmployeeName IS NOT NULL)
ORDER BY EditTime DESC
;

Open in new window

I also prefer to use <> for not equal but != works of course.
0
 

Author Closing Comment

by:lulu50
ID: 40502484
Thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now