So I have a simple query that takes in the following parameters from a report (Company Code and Division Code) and returns a list of all items that are within that company and within that division of said company.
Now I want to have the option to select all division. So instead of choosing company one division one, you could choose company one division all.
So I added a value to my division code properties with NULL as the actual code and 'All' as the name.
My query is long but here is the most important line
Having Style.Company_Code = @CompanyCode And Style.Division_Code = @DivisionCode
So at the end of the long query, it just selects all the rows with the specified company and division code.
If I change the line to this (removing the division part completely)
Having Style.Company_Code = @CompanyCode
It works as intended, showing me all items from all division of a specified company, ignoring the division field completely. However, obviously, this removes the functionality of choosing a division.
So I changed the line to this.
Having Style.Company_Code = @CompanyCode And (Style.Division_Code = @DivisionCode OR @DivisionCode = NULL)
If I select a division, it works as it should, but if I select 'ALL' which has a value of NULL, it just returns nothing. What am I doing wrong?
The issue is on the way that logic behind NULLS. Condition NULL = NULL is evaluated as FALSE In my proposal you ask: NULL IS NULL and this is TRUE Please, try as below:
HAVING Style.Company_Code = @CompanyCode
AND (Style.Division_Code = @DivisionCode OR @DivisionCode IS NULL)