SQL server NULL value problem

Question

0.00/5 (No votes)

See more:

I have written the following SQL query

select MT.TraAsset, MI.McMake, MI.McModel, MI.McSerial, MT.TraType, MT.TraDate, MT.TraFrom, MT.TraTo,
	F.cFloor_Descriptin,F.nFloor, C.cCmpName 
	from Smt_Mc_McTransfers MT
	left join Smt_Mc_McInformation MI on MI.McAsstNo=MT.TraAsset
	left join SpecFo.dbo.Smt_Floor F on MT.TranFloor=F.nFloor
	left join SpecFo.dbo.Smt_Company C on MT.TranCompanyID=C.nCompanyID
	where (MT.TraApprvBy is null) and (TraApprvDate is null) and MT.TranStatus <> 'Cancel'

There brings no row but one row has to be shown on the basis of above query.
When I change the condition MT.TranStatus is null then the one row shows.
In the database TranStatus =NULL. Thanks in advance.

Posted 24-Oct-14 0:43am

Sumon562

Updated 24-Oct-14 0:44am

v2

Add a Solution

Comments

Maciej Los 24-Oct-14 6:49am

There is not SQL issue, but UI problem.

Sumon562 24-Oct-14 6:56am

I run it in SQL query analyzer. I am using SQL Server 2008. If has anyway please suggest

Abdul Samad KP 24-Oct-14 8:02am

In the SQL, NULL is treated as special types, so MT. TranStatus <> 'Cancel' will not include rows where MT. TranStatus is null.
To get your desired output, you can use ISNULL (MT. TranStatus,'') <> 'Cancel'

3 solutions

Solution 1

MT.TranStatus is NULL so it dont check for that row.If you simply edit the row with something else it will come.You have to insert any status on MT.TranStatus instead of NULL

Posted 24-Oct-14 2:04am

Laiju k

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Accepted Answer · 2014-10-24T02:04:00

Solution 2

See: "Common mistakes" (on NULL usage)[^].

Posted 24-Oct-14 2:04am

CPallini

Comments

vangapally Naveen Kumar 24-Oct-14 8:10am

good link for beginners..

Maciej Los 24-Oct-14 9:23am

+5

CPallini 24-Oct-14 9:33am

Thank you.

Maciej Los · Accepted Answer · 2014-10-24T03:31:00

Solution 3

Sumon562 wrote:
(...) one row has to be shown on the basis of above query.
When I change the condition MT.TranStatus is null then the one row shows.

Try to change code this way:

SQL

where (MT.TraApprvBy is null) and (TraApprvDate is null) and COALESCE(MT.TranStatus,'') <> 'Cancel'

COALESCE[^] function replaces NULL with 'default' value ;)

Posted 24-Oct-14 3:31am

Maciej Los

Comments

CPallini 24-Oct-14 9:34am

Nice, 5.

Maciej Los 24-Oct-14 10:43am

Thank you, Carlo ;)