sql My select statement

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

lulu50Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
use "MajorUpgradeCost is not null or ..."
0
HainKurtSr. System AnalystCommented:
or use this

coalesce(MajorUpgradeCost, '') != '' or ...
0
HainKurtSr. System AnalystCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HainKurtSr. System AnalystCommented:
just read the question again :) you should use "or"

this should work

coalesce(MajorUpgradeCost, '') != '' or coalesce(PlannedUpgradeYear, '') != '' or ...
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lulu50Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.