SELECT query with count() to variable

OL Connect Professional
1

In PPC Workflow (2018) I have a Select DB query that I would like to add a COUNT(*) too rather than run two queries (SELECT and COUNT). Does anyone know how to get the count returned along with the Select result set using the Database Workflow ODBC action?

Also, I build a potentially large WHERE clause 6k+. Is there a size limit on the Database select statement or WHERE clause?

Thank you

2

Including a COUNT(*) in your query will force you to add a GROUP BY clause, which will probably not achieve what you’re looking for since the count will then be for reach group, instead of the total count of all records.

However, you wouldn’t even have to include the count if you output the results to XML, since you will then have the ability to count the number of elements easily.

And I believe the size limit for a SQL statement is 32K in the DB Query task, so you should be good with your large query statement.

3

Thank you for the input. I am already outputting the query results to XML so I will look into how to count the elements.

Thank you

4

The following statement can be used in any parsable field in Workflow to get the count of records:

xmlget(‘/Root[1]/DataPage’,Count,KeepCase,NoTrim)

5

That sure makes the research easy, Thanks :grinning: