For the Query Database Smart Service, it looks like it doesn't allow an aste

you can use stored procedure to do your operation in SQL and get back the required result in Appian.

I am not sure about your requirement, hope this will help.

Hi Nick

You cannot use * in SQl statement box, But if you want to multiplication in with SQL data You can choose below options

1. Use query entity or Query rule to get the data from table and handle multiplication in Appian
2. If you want to use 'Query Database' node then first get the column value from table and then do multiplication logic in appian (to be specific use apply function in data output tab of Query database node)
Note: - Not recommended if number of rows returned is large
3. You can also use view or store procedure, but then you have to maintain 2 extra data base object and CDT and they have their own drawbacks


Thanks
Ajinkya

Hi Nick,

You can use * with bracket

select (col1*2) from table1
above query is woking for me

Thanks
Ajinkya

The * is still not working for me even with the brackets. The * is in the where clause instead of the select statement, I'm not sure if that would make a difference.

Nick -
I'm assuming this is a SQL statement you've tested working in your db client. Is that correct?
If so, would you share your SQL as if you were writing it directly in the db client?
That might help us come up with some ideas that would work for you.
Thanks,
Rob

Yes, the SQL statement runs without any problem on the db client. I can't post the formula we're using, but here is a generic SQL statement that is giving me the same error:

SELECT any_column FROM generic_table where 180*tan(45)*cos(45)

So I was able to get around this by using the a!query rule in SAIL, and then by applying the math funtion using the index(where(search())) to the result set.
So I have:

local!AllDetails: a!queryEntity()...
...
local!filter: index(
local!AllDetails.data,
where(
search(
180*tan(45)*cos(local!AllDetails.data.field),
180*tan(45)*cos(local!AllDetails.data.field)
)
)
)

Glad you found your solution, Nick.
It can be difficult to get comfortable querying databases without SQL using Appian. But, Appian has been designed to enable designers to do exactly that.

It's recommended that you avoid use of the query database smart service, or keep it in mind as a last resort. Appian's query rules and functions are more secure in the Appian context, and configurations that use the query database smart service tend to involve more steps when the time comes to promote applications across environments.

The way I was able to get around this is to use division on the other side of the equation instead of multiplication for ex if the where condition is "where column1 = column2 * a ", I replaced it by "where column1/a=column2"