Solved

sql My select statement

Posted on 2014-12-15
8
188 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:Huseyin KAHRAMAN
ID: 40501122
use "MajorUpgradeCost is not null or ..."
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 40501124
or use this

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

Expert Comment

by:Huseyin KAHRAMAN
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 51

Expert Comment

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

this should work

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

680 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