Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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

0
lulu50
Asked:
lulu50
1 Solution
 
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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

this should work

coalesce(MajorUpgradeCost, '') != '' or coalesce(PlannedUpgradeYear, '') != '' or ...
0
 
QlemoC++ DeveloperCommented:
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
 
PortletPaulCommented:
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
 
lulu50Author Commented:
Thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now