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?...

Discussion posts and replies are publicly visible

Parents

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

Reply

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

Children

No Data