Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Not Answered
+1
person also asked this
people also asked this
Replies
8 replies
Subscribers
8 subscribers
Views
6908 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
General
Is there any way by which we can create a dynamic SQL query and implement
ankitt
over 9 years ago
Is there any way by which we can create a dynamic SQL query and implement it in Appian?
I have a requirement where there are 5 textfields(name, DOB, age, address and phone) and on the basis of input in these textfields want data from database. If user gives input to any 1 of the field or combination of fields, then data should be queried on the basis of all the filled fields
Is there any solution to such a problem?...
OriginalPostID-125741
OriginalPostID-125741
Discussion posts and replies are publicly visible
0
siddharthg521
over 9 years ago
Hi Ankit, Here is the expression rule you can use to generate the dynamic SQL query based on values. In the below query I have taken the first 4 inputs (4 inputs to your expression rules) - you can extend them for n inputs.
=concat("Select * from <Table_Name> where ",with(
local!name: if(and(not(isnull(ri!name)),ri!name<>""),"name = "&ri!name,{""}),
local!DOB: if(and(not(isnull(ri!DOB)),ri!DOB<>0),"DOB = "&ri!DOB,{""}),
local!age: if(not(isnull(ri!age)),"age = "&ri!age,{""}),
local!address: if(not(isnull(ri!address)),"address = "&ri!address,{""}),
joinarray(difference({local!name,local!DOB,local!age,local!address},{""})," AND ")
))
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
ankitt
over 9 years ago
Thank you. But how would you implement such a query?
When we are using query rule or query database(smart service), we have to predefine inputs and map them. So in such a case mapping won't be possible
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
siddharthg521
over 9 years ago
Make use of the custom function - executequery()
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
siddharthg521
over 9 years ago
This function will return output in JSON format, and you then need to use the JSON parse functions - extractjsonarray or readjsonarray functions to parse JSON and map it to your respective PVs.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Tim
Certified Lead Developer
over 9 years ago
I think queryentity() would be a better approach for this, you can use a similar approach to what is mentioned above in the definition of the query whilst still maintaining datasubset and cdt functionality
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Soujanya B
over 6 years ago
Use QueryEntity to achieve this
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
Arpit Bhargava
over 6 years ago
Hi Ankit
You can do two things
1. Save the SQL query in the database and use the query entity to hit the SQL query using the process.
2. Use the function executesqlquery() function to execute.
With the help of the database , it will be helpful to have execution of SQL queries dynamically.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
0
josep
over 6 years ago
If you really need to make the queyr i the database then the best option is to create whe dynamic query inside a procedure, but for my it doesn't sounds like something oyu want to do that way because you need the values in appian ,So, Your requirement sounds more like a rule using queryEntity with lot of validations(if's) on the rule inputs parameters
which will give you the queryFilter
Not exactly but sometihng similar to the answer that Ram gave to this question
community.appian.com/.../query-needs-to-be-dynamically-created-based-on-the-search-fields-using-a-queryentity
I Hope this helps
Best Regards
Jose Perez
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel