Advanced sql static entity text identifier compair in statement

Hello!

I have the following statement in my SQL only it doesn't work:

CAST({ST_WORKFLOW_STATUS}.[Code] as CHAR) in (@WorkflowStatusList)

The {ST_WORKFLOW_STATUS}.[Code] is the text identifier of the static entity

The @WorkflowStatusList can be like: 'OPEN','PENDING'


Any tips on how to do this?

Solution

Hello freek,

Did you set property Expand inline = Yes of the input WorkflowStatusList?

You need to so this to accept the list of the WorkwordersStatus (split by commas)

Please see example 2 here: https://success.outsystems.com/Documentation/Architecture_Dashboard/Code_Patterns/Best_practices/Don't_abuse_expand_inline_parameters 

BR,

Luis

Solution

Hi Freek,

Consider to create an Input parameter to your AdvancedSQL in which you pass the static entity text identifier and then use that parameter in your SQL.

Regards,

Daniel

Luís Cardoso wrote:

Hello freek,

Did you set property Expand inline = Yes of the input WorkflowStatusList?

You need to so this to accept the list of the WorkwordersStatus (split by commas)

Please see example 2 here: https://success.outsystems.com/Documentation/Architecture_Dashboard/Code_Patterns/Best_practices/Don't_abuse_expand_inline_parameters 

BR,

Luis

 This was it! thnx!

 

Hi Freek,

Please see also the Post from Daniel above.

You should avoid the use of hardcoded values in the filter. You can pass the value from Static entity as I show below:

A list of {ST_WORKFLOW_STATUS}.[Code] can be send as a parameter to the function the function then translates them to a string for the query. I think this is better then have them as separated input parameters, because this makes it dynamic. Only I know have:

(CAST({ST_WORKFLOW_STATUS}.[Code] as CHAR) in (@WorkflowStatusList) or @WorkflowStatusList = '') 

Only because of the Expand inline the compare with '' is not working anymore


@WorkflowStatusList = ''

Hi Freek, 

Do you want to return all data? You can use a boolean parameter for that. 

For example @ShowAll and you can use it like below and pass this value = true if you want to return all

(CAST({ST_WORKFLOW_STATUS}.[Code] as CHAR) in (@WorkflowStatusList) or @ShowAll= 1)