Setting default value for advance query to null

Setting default value for advance query to null

  
Is there a way to set the default value of an input of an advance query to null? I need to execute some store procedure and the input of the store procedure requires to be null or a valid entry. The input can be any type : integer, date, text....

Thanks,

 Hi Son,

 

Have you tried to add an input parameter and pass the NullIdentifier() built-in function?

 

Cheers,

Tiago Simões

Hi Tiago,

Can you explain more? When i add NullIdentifier() as default value the input of my advanced query. Outsystems complain about type different between Date and identifier.

I am doing it correct?

Thanks,

 Hi Son,

 

If you want to match an empty date field you can use the NullDate() function. To know more about null values you can read this in the help file.

 

If you still aren't able to achieve what you want please explain more explicitly the query you wanted to do.

 

Cheers,

Tiago Simões

Hi here is my scenario:

I have an action name: UpdateUser which has 4 inputs:; DOB(date of birth), UserName, FirstName, LastName, YearsOfExperience.

And only UserName is required. In side UpdateUser, there is an advance query that look like this:

exec updateCurrentUser @DOB, @UserName, @FirstName, @LastName, @YearsOfExperience

However, when the user don't set the value for any of the optional field, the value need to be null. For example, if DOB is not set, it should be executed like:
exec updateCurrentUser null, @UserName, @FirstName, @LastName, @YearsOfExperience.
This is very different from:
exec updateCurrentUser '1900-01-01',@UserName, @FirstName, @LastName, @YearsOfExperience.
where '1900-01-01' is the NullDate().

How can I do that?

Thanks,

 Hi Son,

 

In that case you could set the Expand Inline parameter to Yes in the Advanced Query parameters and optionally use the If built-in If function when you pass the parameter with "null" value when appropriate.

 

Cheers,

Tiago Simões

Hi Son,

You could even create a function to automaticly pass the null when it detects the nullDate()

Sql will accept text in the parameters although you might run into a server date format issue.

cheers

Rui
We also have situation like Optional Input to be Boolean. If the user don't set anything, advanced query input parameter should be null not False. When i get that situation i have to convert the boolean input into Integer and pass -1. And in the advance query, convert -1 to "null". Is there a cleaner way to do it? I don't like my boolean input to be changed to Integer because user can input 2 or 3 in it.
Hello I´m trying to built the following advanced query:
"SELECT {Tasks}.[Title]+char(13)+char(10)+{Tasks}.[Description], {Tasks}.[DueDate], DiffDays({Tasks}.[DueDate],{Tasks}.[CompletionDate])
from {Tasks} 
Where {Tasks}.[Done] = 'True'"

But i get this error: "Database returned the following error: Error in advanced query GetCompletedTasks: 'DiffDays' is not a recognized built-in function name."

Can someone help on this?

Thanks

Filipe Gaspar
Hi Filipe,

If you are doing an advanced query I believe you should use the functions from the database server you are using (T-SQL for MSFT SQL Server or PL/SQL for Oracle).

Cheers,
Tiago Simões
Olá Tiago (suponho que seja portugês...)

Foi só alterar (como disseste) a função DiffDate para DateDiff (T-Sql) e já funciona corretamente.

Tinha mais uma questão (sou principiante neste tecnologia): estou a tentar preencher um campo com baso no valor de outro campo (boolean), resumindo - se o campo boolean for TRUE, a outra campo deve ser preenchido com a data atual, caso contrário deverá ser colocado a null. Utilizei a funcção IF, com uma expressão, mas apenas funciona quando visualizo a "EditTable", nunca atualiza os dados na BD.

Tens alguma sugestão?

Obrigado.

Abraço,
Filipe Gaspar
Hi Filipe,

Assuming this is an advanced query...

Try use a case statement, something like this
CASE
    WHEN field=1 THEN getdate()
   ELSE null
END as field_name

Cheers



Hi Rui

It's working properly now.

Thanks for the help.


Best regards
Filipe Gaspar