[solved] Access values of data source in sql query

0

Hi,
The question is, what do you want to achieve?
Where do you want to connect the data source?

You can connect the data source to e.g. sqlgrid or choose field and you don't have to write a separate query.

https://docs.webcon.com/docs/2024R1/Studio/ConnectionsAndDataSource/DataSources/.

This SQL statement reads data from the SQL Server and imports it into WEBCON (Subworkflow action).
I would like to join a data source from WEBCON to retrieve the corresponding ID.
This is necessary because the data needs to be passed in the format ID#value, as one of the fields Im importing data is a dropdown field (which requires this specific format)

ok - now I understand.
What type of data source do you need to connect? fixed value list, BPS internal view or sql (or something else)?
I think the easiest thing to do would be to add an INNER JOIN to your query and read the necessary values.

I need a source via a fixed value list, one via sql and one via Rest Webservice.

1. Fixed value list - you can read fixed value e.g. from a query like this

" SELECT dbo.ClearWFElemId(item) as ItemID
,dbo.ClearWFElemId( dbo.ClearWFElem(item)) as ItemName
,dbo.ClearWFElem( dbo.ClearWFElem(item)) as ItemDescription
from dbo.SplitToTable((select WFS_StaticValues ​​FROM [dbo].[WFDataSources] where WFS_ID = xxx ),';' ) test "

where xxx is the ID of your data source (fixed value list)

2. SQL data source - so you just have to add it via inner join ;)

3. REST API Data source - I think this will be the biggest challenge. As far I know, you can't add it dynamically in an SQL query.
In this case you can set only the ID (maybe with technical field) on the start subworkflow action, and then in the started element on the path with which you start the workflow, read the appropriate value from the source and set value via action "Change value of single field" (BTW - you can do the same for every dictionary/data source what you have in this case)

Hi,
The SQL inner join worked great - I'm just not quite sure how to combine my import query and the fixed value list query. Can you please give me a hint?

and can I leave 'item' in the query or does something else belong in there? - since I don't have access to the columns of the source
-> Is it perhaps possible to pass this via an index? e.g. index{0} or something similar?

Hi,
attached is an example of reading the name to the id from the fixed values ​​list.

1 - column with ID
2 - Data source ID
3 - column with ID#Name (from the fixed values ​​list)

"item" is the column name returned by the "SplitToTable" function

query:
SELECT DET_Value1
,(SELECT TOP 1 CONCAT(
dbo.ClearWFElemId(item) ,'#', dbo.ClearWFElemId( dbo.ClearWFElem(item)))
from dbo.SplitToTable((select WFS_StaticValues ​​FROM [dbo].[WFDataSources] where WFS_ID = {DS:xxxx} ),';' )
WHERE dbo.ClearWFElemId(item) = DET_Value1) as test
FROM WFElementDetails
WHERE DET_WFDID = {WFD_ID}
AND DET_WFCONID = {WFCON:xxxx}

Hi, thanks that was very helpful!