Hi you all, I need to use a Query DB with more "where" variable

Appian 6.6.1

is ac!OtherAndValue another string with more "AND" statements? What happens if you add all ANDs directly in the setup tab?

And if the ac!OtherAndValue is another possible value for your requisition plant, then consider using the LIKE or IN operators.

Yes the ac!OtherAndValue is setting with Process Variable with the "AND" ....
If I put the "AND" variable on the query the query works well..

the ac!OtherAndValue is setting as :
AND DATE_FORMAT(requisitiondate,'%Y-%m-%d') BETWEEN STR_TO_DATE('2013-05-01','%Y-%m-%d') AND STR_TO_DATE('2013-05-04','%Y-%m-%d')

The acp's were designed to hold values of parameters of you query but they won't work to hold a whole string that itself is another query/operator/statement or the rest of the query/operator/statement. You can use acp's to make your inputs parameters in the query but not to hold the string of the statement itself.

Ok. Thanks Edoardo. So How I can create a complex dynamic query with out of the box instruments?

You can still get the dynamic part going by using acp´s to pass values but not queries themselves. It seems you are already doing this in the first part where you do

AND purchaserequisition.plant=ac!plantId

the dynamic aspect of that condition is the =ac!plantId, just add directly the rest of the query and just replace any hard-coded values with acp's

Yes I understand. But the problem is that I need a where condition only IF a Process variable isn't null.

This works
Select a, b from table where a=ac! Value1

Now if the pv! Value1 isn't null I need to add, in the previous query, this condition:

And date_format (b,....) between xxxx and yyyyy

How I can do this?

Thanks

In that case you better use a gateway to check the value of the pv and direct the flow to a different Query RDBMS node that includes the other query.

Or you can use the the executequery(datasource, query) function that comes with the Advanced Form Utilities plug-in. This would replace your Query RDBMS node. This is an example:

=executequery("java:/jdbc/appianMySQL","select count(a_id) as numberOfFruits from fruit")

given it takes a string you can easily make it dynamic. I am not a big fan of using this expression to execute a query since this will return it in a type of JSON-style array (its main purpose is to be used by the attachAutocompleteFromQuery function)