Pass NULLIDENTIFIER() as parameter in SQL Widget

Pass NULLIDENTIFIER() as parameter in SQL Widget

  
Hi!

I'm quite new in Outsystems Platform. When I try to execute the below SQL using NULLIDENTIFIER() as the passed by value for the PriorityField, the SQL doesn't return any rows.

Many Thanks,

Chris

SELECT {Issue}.* from {Issue} where {Issue}.[PriorityID] = @PriorityID  order by {Issue}.[SubmitDate]


Hi Christopher,
To allow that query to give a result even if the parameter is null you can change it to:

SELECT {Issue}.* from {Issue} where {Issue}.[PriorityID] = @PriorityID OR {Issue}.[PriorityID] = 0 order by {Issue}.[SubmitDate]
Hi Tiago,

I added the condition and still no records were returned. I replaced the condition with {Issue}.[PriorityID] IS NULL and it worked. But still don't know how come the condition you suggested didn't work considering that the values were 0 in the table.

Anyways, thanks for the reply.

Sincerely,

Chris

Hi Christopher,

Reference Identifiers in the database are NULL in the database to respect the constraints. If they were set to 0 in the database there would have to exist an entry in your Priority entity with Id=0, that doesn't exist.

But since there are no Nulls in the Out systems language when you do a query those values are automatically read as 0. And the Create/Update actions do the oposite conversion automatically as well, making it look like there are 0's in the database.

Regards,
João Rosado
Hi Joao,


Noted. Thanks a lot!


Sincerely,

Chris