I am trying to run this querry in query database but getting an error:
SELECT ID,PRIORITY,CREATION,DURATION,FIRM_GROUP,IS_SCHEDULED,STATUS FROM XYZ_VIEW WHERE IS_TEMP=1 AND STATUS NOT IN ('Closed', 'Complete', 'Cancelled') AND IS_SCHEDULED = 1 ;
Discussion posts and replies are publicly visible
Please remove the semi- colon at the end of the query statement.
Is there a reason for not using queryEntity?
Agreed - this is a pretty straightforward query that can easily be done using a!queryEntity(). I highly recommend only using the Query Database node when you have extremely complex SQL. The Query Database node doesn't return the data as a CDT, which makes it more difficult to work with in a process. Plus, you can use a!queryEntity() in any expression, while the Query Database Smart Service only works in a process model.
I don't want to open new question but I have same problem.
I need to provide in criteria dynamically but I get no results. There is no error and no data. In data section the in criteria are definded as :
part of sql:
and c.userid in ( ac!sqlMailboxes )
select c.userid as Milbox, cast(a.Creationtime as date) as RFDate,count(a.uniqueid) as NoOfFaxesfrom users cjoin documents aon c.handle = a.owneridwhere c.userid in ( ac!sqlMailboxes )group by c.userid, cast(a.Creationtime as date)
Same query run on SQL Server return data.
How to deliver this in criteria based on string array variable ?
Really the same question applies here. This is a simple query - can you use a!queryEntity() or a!queryRecordType() instead of the Query Database Node? Those functions are much easier to work with and should support the query you suggested.
I would create a view in DB and query it using queryEntity().
I can't agree that using query Entity is easier as there is join in SQL so I would have to create 2 datatypes then 2 query entities and then use one query entity results as filter criteria for second query entity. At the end i would have to join results based on the id column to have it one object.
It's super easy to create a View to do the Join you need, with accompanying CDT / data store entity, and then query that. This is super standard practice. And in 21.2+ you also have the option to use record type relationships. Either of these approaches will be much easier than trying to deal with directly using SQL in a process.
First, I agree with the other suggestions in the thread regarding creating a view for your query, attach a CDT to it (only need one) and utilize a!queryEntity() to retrieve data.
However, if for some reason it is required to use a Query DB node, the way we used to do this in legacy versions of Appian was to use INSTR (Oracle) or CHARINDEX (MSSQL) such as the example I posted in this thread.
© 2023 Appian. All rights reserved.