Execute sql functions

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

What are the DB functions? would you provide the link?

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. 

Which Appian function we should use to do #2 below?  

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)

Do you have an example?

I am not sure you are addressing this in the best way. It sounds like you have a one to many situation and I am not sure any of this has been written to the DB yet.
You may want to look at your UI and your CDTs for the parent and child. If you write the parent first, you will get the PK that can then be used as a FK for the child (this is just one of many approaches) and then the nextval is not needed.

There may be some tutorials for building grids, etc. that might help..

I would like to write an expression rule to call SELECT SEQ.NEXTVAL SEQ FROM DUAL and return the GUID before launch the process model to save the data. What Appian function can i use to call the SQL statement you suggested that calls SELECT SEQ.NEXTVAL SEQ FROM DUAL as an inner select?

In other word, is there an Appian function that can execute raw SQL statements?

I agree with Christine, this should be handled natively in Appian without writing your own SQL statement. And even if you get the SQL to work, you could have other issues too - what would happen if you have two people submit at the same time? You may not be guaranteed to get the next value in the sequence.

If your concern is that you need to make sure each address is associated with the correct contact, there's plenty of other ways to do that:

• Create another CDT which stores the index of the parent and child on clicking the submit button. For example, you could have a CDT (that doesn't actually have an associated table) that just stores the contact index and the address index. Then, use two separate writes: one to the contact table and one to the address table. Since you have kept the list of both indexes, you should be able to associate the correct contact ID to the address as a foreign key.
• Use nested CDTs. These can get complex, but this is one of the main use cases for using nested CDTs. If you have address nested inside of contact, it's now easy to understand which addresses are associated with each contact.

I agree with Christine, this should be handled natively in Appian without writing your own SQL statement. And even if you get the SQL to work, you could have other issues too - what would happen if you have two people submit at the same time? You may not be guaranteed to get the next value in the sequence.

If your concern is that you need to make sure each address is associated with the correct contact, there's plenty of other ways to do that:

• Create another CDT which stores the index of the parent and child on clicking the submit button. For example, you could have a CDT (that doesn't actually have an associated table) that just stores the contact index and the address index. Then, use two separate writes: one to the contact table and one to the address table. Since you have kept the list of both indexes, you should be able to associate the correct contact ID to the address as a foreign key.
• Use nested CDTs. These can get complex, but this is one of the main use cases for using nested CDTs. If you have address nested inside of contact, it's now easy to understand which addresses are associated with each contact.

Is there an example or more documents about nested CDTs on Appian website? Would you share the link? I'd like to learn how nested CDTs work. thx. 

Would you share an example to "Create another CDT which stores the index of the parent and child on clicking the submit button. For example, you could have a CDT (that doesn't actually have an associated table) that just stores the contact index and the address index. Then, use two separate writes: one to the contact table and one to the address table. Since you have kept the list of both indexes, you should be able to associate the correct contact ID to the address as a foreign key"?

Here's information on nested CDTs: https://docs.appian.com/suite/help/latest/cdt_design_guidance.html#nested-cdts

For the second example, it will depend a lot on how your interface is set up. How are you currently associating each contact with the appropriate address? Can you share the expression for your interface?

Generally though, you would create a new CDT with just two fields: contactIndex and addressIndex. Then, on submitting your form, you would set up key value pairs to associate each address with the appropriate contact. Then, your data in this CDT might end up something like this:

• contactIndex: 1, addressIndex: 1
• contactIndex: 1, addressIndex: 2
• contactIndex: 2:, addressIndex: 3