Query Database

Hi

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.

hi,

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 : 

="'abc1', 'ddddd33333'"

part of sql: 

and c.userid in ( ac!sqlMailboxes )

full sql:

select c.userid as Milbox,
cast(a.Creationtime as date) as RFDate,
count(a.uniqueid) as NoOfFaxes
from users c
join documents a
on c.handle = a.ownerid
where 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 ?

hi,

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 : 

="'abc1', 'ddddd33333'"

part of sql: 

and c.userid in ( ac!sqlMailboxes )

full sql:

select c.userid as Milbox,
cast(a.Creationtime as date) as RFDate,
count(a.uniqueid) as NoOfFaxes
from users c
join documents a
on c.handle = a.ownerid
where 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.