1

I am trying get a result for the following query.

Select * from FACHL where Comp_Year = '2018-2019' and 
        Trans_date between '04/01/2018' And '03/31/2019' And Order_By <> 'SMAN'
        Order By Account_Code,Trans_Date,Document_No,S_no

This gives me no result. If I remove Order_By <> 'SMAN' from Query , it shows me result.

The data in Order_By column is NULL for all rows. So I tried Order_By = NULL but no result.

The image shows my data. RESULT IMAGE

Where am I committing mistake here???

Thanx..

Hitesh Shroff
  • 71
  • 1
  • 1
  • 9
  • where Comp_Year = '2018-2019' looks a bit lonely should you not have an and after the date? – P.Salmon Sep 17 '18 at 12:17
  • An `And` or `Or` is missed after `Comp_Year = '2018-2019'` – Hassan Sadeghi Sep 17 '18 at 12:20
  • This question is based on misunderstanding how equality comparisons in SQL work in the face of NULL. The linked duplicate is based on misunderstanding how equality comprisons in SQL work in the face of NULL. I could have found any number of existing questions to link to and the answers *all* cover much the same ground, so I didn't look for one that *specifically* exactly matched how you'd chosen to phrase your question here. – Damien_The_Unbeliever Sep 17 '18 at 12:51
  • @Damien_The_Unbeliever Do I need to reply here? Sorry I posted in question. Actually its not like "how equality comparisons in SQL work in the face of NULL".. I just gave example that putting `Order_By = NULL` even don't work, I was unaware that `Order_By IS NULL` works but with NULL is just example. Actually I want to know why Order_By <> 'STRING' don't work? I hope you understand. – Hitesh Shroff Sep 17 '18 at 12:57

1 Answers1

0

SQL NULL means I don't know.

If you want to get NULL row need to use Order_By IS NULL rather than Order_By = NULL

Select * 
from FACHL 
where Comp_Year = '2018-2019' AND
Trans_date between '04/01/2018' And '03/31/2019' And Order_By IS NULL
Order By Account_Code,Trans_Date,Document_No,S_no

NOTE

NULL can't use <> or = to get the value when the ANSI_NULLS set ON

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Okay but what about `Order_By <> 'SMAN'`. This should return the result. – Hitesh Shroff Sep 17 '18 at 12:41
  • `Order_By IS NULL` WORKED... But why not this ??? `Order_By <> 'SMAN'` – Hitesh Shroff Sep 17 '18 at 12:44
  • As my answer say `NULL` isn't a value you can't use `<>` or `=` to get the. `NULL` is very special one in SQL. – D-Shih Sep 17 '18 at 12:53
  • You only can get the `NULL` by `col Is NULL` when the ANSI_NULLS set ON – D-Shih Sep 17 '18 at 12:54
  • So lets say that there are 2 rows with value in Order_By as 'SMAN' and 3 rows with Order_By as 'ABC' and Other 3 rows with value in Order_By as NULL. I want result where Order_By <> SMAN. What shall I do? – Hitesh Shroff Sep 17 '18 at 13:09
  • use OR operations `Select * from FACHL where Comp_Year = '2018-2019' AND Trans_date between '04/01/2018' And '03/31/2019' And Order_By IS NULL OR Order_By<>'SMAN' Order By Account_Code,Trans_Date,Document_No,S_no` ` – D-Shih Sep 17 '18 at 13:10
  • ` AND Order_By <> 'SMAN' OR Order_By IS NULL` Worked.. Thanx. I got to know a new thing/concept today. – Hitesh Shroff Sep 17 '18 at 13:12