SQL query to fetch the values including NULL

Hi Team,

My current set up for drop down filter accepts multiple values. Implementation is done to fetch the details from SQL query. But when the Blank is selected in the drop down, it implies to NULL value to fetch hte records from database. Below is the query executed which is returning wrong results.

Select * from Table where id in (1,2,3,NULL). This is not giving me correct result set as in(NULL) isnt working. 

Please help me on this.


Thanks,

Saicharan

Hi,

Do not use the null on the SQL.

You can set option1 and values of the special list values of the drop down properties

Set Value1 to 0 and Option1 to your text of choice.

When selecting the value the parameter specified as Variable will be set to null identifier.

If you only want these 4 options no need for SQL, you can set all these options as properties of the drop-down.

Regards,

Daniel


Daniël Kuhlmann wrote:

Hi,

Do not use the null on the SQL.

You can set option1 and values of the special list values of the drop down properties

Set Value1 to 0 and Option1 to your text of choice.

When selecting the value the parameter specified as Variable will be set to null identifier.

If you only want these 4 options no need for SQL, you can set all these options as properties of the drop-down.

Regards,

Daniel


Hi Daniel,

We are not using special list as the values are fetched from aggregate.

I have the list of drop downs coming from the filedid from database. Null also has to be added to the filter list.

Logic is like Select * from Table where id in (@localVariable). This local variable holds the filtered id which accepts multiple select. Not if I select id=1 and NULL values. Query is formed as Select * from Table where id in (1,NULL). This is giving me wrong results as in (NULL) doesnt work. Is there any work around to fix this.

Thanks,

Saicharan

Hello Sai, 

NULL will never work inside an IN clause. But also,  There is no possibility of IDs with value NULL in database. 

So, there is no point in sending NULL to inside the query. 

If you are using a query argument (with expand in-line) to pass the IDs to inside the query to be used inside the IN, you need to process it before passing, to remove any not selected value. 

But if the user has the option to not select any value, you would send an empty string to inside the query, and the IN would also fail. 

So, your code should append the IDs selected in the following way:

Text = Text + If(variable <> NullIdentifier, "," + variable, "") 

And after adding all the ids like this, you pre-append a 0.

Text = "0"+Text

This will change your query to

Select * from Table where id in (0,1,2,3), that will work as the Las parameter is not appended, or like this if the user does not selects anything Select * from Table where id in (0). 

Hope this help. 

Cheers. 

Hi Saicharan,

I didn't mean to say that the Special List is as replacement for your aggregate, the screen print also has an aggregate and a special list entry.

Maybe I wrongly assumed you wanted the null in your aggregate to have a empty option in your dropdown.

My solution would provide for that. Sorry if i misunderstood what you tried to achieve.

Regards,

Daniel

Hi Sai,


Usually, when you want to check a null value in a where clause in SQL, you can use " id is null" or " id is not null".


So, can you try something like:

Select * from Table where id in (1,2,3) or id is null;


Let me know if this works for you.


Regards,

João Silva


Eduardo Jauch wrote:

Hello Sai, 

NULL will never work inside an IN clause. But also,  There is no possibility of IDs with value NULL in database. 

So, there is no point in sending NULL to inside the query. 

If you are using a query argument (with expand in-line) to pass the IDs to inside the query to be used inside the IN, you need to process it before passing, to remove any not selected value. 

But if the user has the option to not select any value, you would send an empty string to inside the query, and the IN would also fail. 

So, your code should append the IDs selected in the following way:

Text = Text + If(variable <> NullIdentifier, "," + variable, "") 

And after adding all the ids like this, you pre-append a 0.

Text = "0"+Text

This will change your query to

Select * from Table where id in (0,1,2,3), that will work as the Las parameter is not appended, or like this if the user does not selects anything Select * from Table where id in (0). 

Hope this help. 

Cheers. 

Hi Eduardo,

Thanks for the suggestion.

The current implementation for the filterId is 

'Blank' is the drop down filter value in the page. If the user selects blank, NULL is the ID passed to the query - select * from table where filterid in(NULL). In this case , we are facing the problem. I tried with select * from table where filterid in (0). It is not showing the details where the filter id details are null. 

I am looking for a solution showing filterid NULL value records when Blank is selected. 


Thanks,

Saicharan


OK, sorry, 

If you use a where with Id in (0), you will never return anything. Thought was what you wanted if the user didn't select anything. 

If you want to fetch everything case user do not select an Id, try to change your logic to create a variable that is like this in the end, if all IDs are null, replace the entire In clause by 1=1, for example. 

So, instead of passing only the contents of the In, pass the whole In, or 1=1.

The build starts as before. 

Text = Text + If(variable <> NullIdentifier, "," + variable, "") 

And after adding all the ids like this, you pre-append. 

Text = if(Text="", "1=1", "in (" + Text + ")") 

And in the SQL use 

WHERE @VARIABLE

This way should bring everything if nothing is selected. 

Cheers

be careful using null.

In my case if i set

where columnname = columnname, the null is not selected

so better to put separate sql - one for null (where columnname is null) and the other which is not null


Just to be clear:

When I mention that you CAN'T have a NULL in an ID in the database, I am using your example you gave: 

Select * from Table where id in (1,2,3,NULL)

In this case, ID (I am assuming) is a PRIMARY KEY of Table, and it is not possible to a PK to be NULL in the database, as it would violate the constraint of being unique.

The exception would be if the Table, in the query, is in the RIGHT side of a LEFT JOIN and there is no matching, as during the execution it would become NULL in the partial results.

Cheers.

Hi Sai,

Much has been said above, but I'm not sure the right answer is fully there. I assume that you have a ListBox Widget allowing multiple selection, and to populate it, you have an Aggregate or SQL that fetches the unique Ids from an Entity, using a DISTINCT or a GROUP BY, and that you have a list that contains Ids, and one of the Ids is a NullIdentifier(), right?

If this is the case, and you want, if NullIdentifier() is selected, to fetch all records that have an Id with value NULL in the database, you need to explicitly test against NULL, since you can't use IN for NULL values (that has nothing to do with OutSystems by the way, that's just how SQL works).

So there's a few things to consider:

  1. If the user does not select a value other than NullIdentifier(), the IN should contain an invalid value ("0" being the prime candidate, as Eduardo pointed out), since you need something between the parentheses. So the Expand Inline Variable you pass to the SQL should contain "0". If there are Ids selected other than NullIdentifier(), you can pass them comma seperated like you already have.
  2. If the user selects the NullIdentifier() Id, you need to add a line specifically testing for this. I'd add another Input Variable to the SQL of type Boolean that indicates whether this is the case. You can then add something like the following to your WHERE clause: "AND (@IsNullSelected = 0 OR {MyEntity}.[ForeignKeyId] IS NULL)" (where {MyEntity} and [ForeignKeyId] are the appropriate Entity and Attribute for your case).

If you have any more questions, please ask.