Hi All,
How can be execute sql server table valued functions from appian ? Are there any plug-ins available for the same ?
Thanks and regards,
Ishani Joshi
Discussion posts and replies are publicly visible
If you can share an example of what you are needing to do, we may be able to provide a more specific answer....However, here are two approaches to performing functions on data being pulled /displayed / captured / etc in Appian:
1. Use Appian functions to perform the manipulations (https://docs.appian.com/suite/help/19.3/Appian_Functions.html), combine with a!forEach to apply to arrays
2. Use DB functions in views to manipulate the data before pulling in into Appian
Hope this helps
Thank you
Christine
Thank you for the help !
I'm not sure about it's limitations, but you could also look into the execute stored procedure plugin on the App Market.
What are the DB functions? would you provide the link?
Would you elaborate how does #2 works?
The DB functions are functions of whatever DB you are using, not Appian. There are many great SQL tutorials online, but here is a MYSQL listing of some functions: https://www.w3schools.com/sql/sql_ref_mysql.asp
Regarding #2, how to pull the data into Appian?
The SQL I want to execute is SELECT SEQ.NEXTVAL SEQ FROM DUAL. DUAL is a special table in Oracle which does not show up in the table dropdown list when creating the CDT.
You can not access functions like this or the DUAL table directly in CDTs. You need to create a view directly in the DB to use functions or the DUAL table.
1. Why do you need the nextval? If for a PK, then, you should be using the sequence automatically as part of the trigger on insert and this is not needed.
2. If you need a nextval for another reason, you can us that select (just use an alias on the column) in a select with another table (use an inner select or other to combine with the table you are getting the main part of your data from).
Thx for the quick reply. On one form, we allow the user to enter multiple contacts and each contact could have multiple addresses. Since there is only one submit button on the form, we need the GUIDs of the contacts to associate the contacts with their address before the user click the submit button (aka before launching the process model). Otherwise the process model does not know which addresses belong to which contacts.