Question regarding forms and field validation

@ajoyr Understood, even in the beginning I was surprised how a a!queryEntity() as suggested by others can help you with the built in SQL statements you have in place. a!queryEntity() works in your case if and only if the native sql statements in the 'Validation' are rather maintained as attributes in an database entity.

I don't believe that there is one such reference on the forum as per my knowledge. Further I don't think there is an OOTB function or shared component to execute SQL statements. Once upon a time there used to exist a Shared Component function by name executesql() (I don't remember the name exactly), but it has been deprecated. I think developing one such function might take lot of time but to the best of my knowledge, this would be an easier approach.

And in the other way, if we try to parse the SQL statements in Appian, I don't think that's possible, even if it is, it's a lot and lot more cumbersome process. Because parsing the SQL statements and appending the dynamic inputs and then again querying on the whole isn't that easy as per my knowledge.

As suggested by other practitioner, you might opt for stored procedure, but even this demands significant amount of time. Because, you will end up in developing procedures which will be the replicas of existing native SQL statements. If you want to go ahead with writing the procedures, there is a plugin at https://forum.appian.com/suite/tempo/records/type/components/item/i8BCLGOdlMUpdGVqT-RV7oRg74uEGJO5C8ZhL3Yukyv6zRTk30LjvusuVlHbS9BJg/view/summary which will help you in reading the stored procedure outputs when ever you are using SAIL and also map the outputs conveniently thereafter.

So on the whole you might be left with the following options afaik:
1. Parsing native SQL statements directly in Appian: This requires a strong mechanism to be built which will understand the native SQL, accept the inputs and render the results accordingly. Implementation could be highly complex.
2. A plugin that could parse native SQL statements (which are in form of text), substitute inputs, execute the statements and finally returns you the desired results. (Query Database is a smart service, so you couldn't use it in the SAIL interface.)
3. Write stored procedures that are equivalent to database validations you already have in place. You could make a query to them with the help of the plugin as specified by me above.
4. Maintain the native SQL statements in tables similar to decision matrix. For instance, a simple query against the database should let us know if the age is greater than 40. This way we could also leverage the Appian features. Even in this case, the complexity depends and might go high depending on the complexity associated with native SQL queries. I would generally prefer for this approach.

Let's see if you can still get much valuable inputs from other practitioners.

@ajoyr Understood, even in the beginning I was surprised how a a!queryEntity() as suggested by others can help you with the built in SQL statements you have in place. a!queryEntity() works in your case if and only if the native sql statements in the 'Validation' are rather maintained as attributes in an database entity.

I don't believe that there is one such reference on the forum as per my knowledge. Further I don't think there is an OOTB function or shared component to execute SQL statements. Once upon a time there used to exist a Shared Component function by name executesql() (I don't remember the name exactly), but it has been deprecated. I think developing one such function might take lot of time but to the best of my knowledge, this would be an easier approach.

And in the other way, if we try to parse the SQL statements in Appian, I don't think that's possible, even if it is, it's a lot and lot more cumbersome process. Because parsing the SQL statements and appending the dynamic inputs and then again querying on the whole isn't that easy as per my knowledge.

As suggested by other practitioner, you might opt for stored procedure, but even this demands significant amount of time. Because, you will end up in developing procedures which will be the replicas of existing native SQL statements. If you want to go ahead with writing the procedures, there is a plugin at https://forum.appian.com/suite/tempo/records/type/components/item/i8BCLGOdlMUpdGVqT-RV7oRg74uEGJO5C8ZhL3Yukyv6zRTk30LjvusuVlHbS9BJg/view/summary which will help you in reading the stored procedure outputs when ever you are using SAIL and also map the outputs conveniently thereafter.

So on the whole you might be left with the following options afaik:
1. Parsing native SQL statements directly in Appian: This requires a strong mechanism to be built which will understand the native SQL, accept the inputs and render the results accordingly. Implementation could be highly complex.
2. A plugin that could parse native SQL statements (which are in form of text), substitute inputs, execute the statements and finally returns you the desired results. (Query Database is a smart service, so you couldn't use it in the SAIL interface.)
3. Write stored procedures that are equivalent to database validations you already have in place. You could make a query to them with the help of the plugin as specified by me above.
4. Maintain the native SQL statements in tables similar to decision matrix. For instance, a simple query against the database should let us know if the age is greater than 40. This way we could also leverage the Appian features. Even in this case, the complexity depends and might go high depending on the complexity associated with native SQL queries. I would generally prefer for this approach.

Let's see if you can still get much valuable inputs from other practitioners.