Solved

sql My select statement

Posted on 2014-12-15
8
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 56

Expert Comment

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

Expert Comment

by:HainKurt
ID: 40501124
or use this

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

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 56

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 70

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 49

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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 the fundamental information of how to create a table.

623 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