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 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.
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.
Eduardo Jauch wrote:
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.
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.
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.
João Silva
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.
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.
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:
If you have any more questions, please ask.