Using CTE Tables in attribute configuration

0

Hi, yes it is possible, but studio has problems with this type of queries.
Check this article by Kamil - https://alterpaths.com/how-configure-form-field-with-advenced-sql-query/
You need to prepare a simple select which will return the same columns names as in the query with CTE, configure the field, and then paste your query with CTE.

Hi,

Sebastian already referenced Kamils post which answers the question. I'm just adding the following in case it is not available at some time:

Query for choice field (attribute):
WITH pickerOptions
as
(
select '-20' as Id, 'Other$$DE$$Anderes' as Label union
select '-10' as Id, 'Business$$DE$$Business' as Label union
select '{DT:144}' as Id, 'Deployment Version$$DE$$Deployment Version' as Label
where '{S:330}' <> '' or '{I:1995}' = '{EPV:349}' union
select '{DT:141}' as Id, 'Environment$$DE$$Umgebung' as Label
where '{S:330}' <> '' or '{I:1995}' = '{EPV:349}' union
select '{DT:139}' as Id, 'Server$$DE$$Server' as Label
where '{S:330}' <> '' or '{I:1995}' = '{EPV:349}' union
select '{DT:140}' as Id, 'Service$$DE$$Service' as Label
where '{S:330}' <> '' or '{I:1995}' = '{EPV:349}'
)

select Id, Label, dbo.ClearWFElemAdvLanguage(Label,SUBSTRING('{USERLAN}',1,2)) LanguageLabel
from pickerOptions
order by LanguageLabel

Best regards,
Daniel