There is a ugly hack I know. You can create a variable with expand inline set to yes. And fill the variable with something like this: 

IF(MovieID IS Nullidentifier(),"WHERE 1=1", "WHERE ({MV_MEDIA_INFO}.[ID] = " + MovieID+")")

You could separate using if, if MovieID <> null true, pass to SQL without WHERE clause. When false, pass to your SQL below.

Marios Tofarides wrote:

WHERE ({MV_MEDIA_INFO}.[ID] = @MovieID Or @MovieID IS NULL)

Hi,

WHERE ({MV_MEDIA_INFO}.[ID] = @MovieID Or isnull(@MovieID,0)=0)

Thanks!

The problem is that an NullIdentifier() in Outsystems is not the same as a NULL.

So basicly you are checking : 0 IS NULL (which would return false)

The best you could do is to check if @MovieId = 0

You could also make it with 1 check (but I doubt you would want this haha) by doing:

{MV_MEDIA_INFO}.[ID] LIKE '%@MovieID%'

I would also recommend to not use a IF in SQL, it's very inefficient.

Simply rewrite your WHERE as below:

WHERE @MovieID = 0 OR {MV_MEDIA_INFO}.[ID] = @MovieID

An Entity Identifier Type in Outsystems is never NULL in the database. If you don't specify a value to it, it will assume the default value for Identifiers, which is 0.

Hi Marios,

The solution should be this:

WHERE ({MV_MEDIA_INFO}.[ID] = @MovieID Or @MovieID = 0)

Regards,

Marcelo

Hi,

I am not checking Null with 0.

I am simply replacing all nulls to 0 and 0= 0 it will return all rows.

Thanks!

Hello Marios,

While both Aurelio and Marcelo gave you the solution (replace @MovieId IS NULL with @MovieId = 0), explantions are not entirely correct.

So, allow me to explain why this is the solution.

In an SQL, when you pass a NullIdentifier() as value to a query parameter (that has as data type an Entity identifier), it will pass the value 0 (zero).

So, in your original condition is:

{MV_MEDIA_INFO}.[ID] = @MovieID Or @MovieID IS NULL

And this will be sent to the database server as:

WHERE {MV_MEDIA_INFO}.[ID] = 0 Or 0 IS NULL

As you can see, the 0 IS NULL will be UNKNOWN (or False, to simplify), and probably you don't have any record with ID = 0. No records will be returned.
If you replace the @MovieId IS NULL with @MovieID = 0, than your query will be executed as:

WHERE {MV_MEDIA_INFO}.[ID] = 0 Or 0 = 0

Now, the 0 = 0 will be TRUE for all records, no matter the ID. You are "shuttind down" the {MV_MEDIA_INFO}.[ID] = @MovieId condition (because of the OR).

But there is a problem in the affirmation that OutSystems "never" store a NULL value in a database. The problem is that this is not entirely true... ;)
There is one situation where OutSystems in fact stores a NULL value: NOT MANDATORY Foreign Keys that do not have a value. It does this to avoid a database error during the store of the record, as if you put 0 (zero) instead NULL, the database will try to find the line in the other table with ID = 0 and probably will not find, generating an exception.

So, when you are saving a record, that has a NON MANDATORY foreign key and you are not providing a value, it will not be given a default value to this FK, but instead the record will be saved with NULL in this field.

This is important, because if you are doing something like this:

And you are passing NullIdentifier() as value to @Entity1Id parameter, in the hope you find all records in Entity2 that does not have a value assigned in this Non Mandatory FK, you will get NO RECORDS.

But if you replace the @Entity1Id by IS NULL, you will receive results:

Hope this helps in the future.

Cheers.   

Thanks, Marcello, Aurelio, and Jitendra for the solution. 

Eduardo, thanks for the explanation and the time you took to compose it for us. I didn't know that Outsystems "converts" a NullIdentifier() into 0. That's why I tried IS NULL.

Thanks everyone for your help! :) 

Marios