Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email support@alteryx.com for assistance.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How the 'Group Replacement Value for SQL IN Clause' works when using SQL: Update WHERE Clause in the Dynamic Input Tool

ntobon
Alteryx
Alteryx
Created
I am using a workflow where a dynamic query is done on a live database. The SQL query in the Dynamic Input tool has an IN clause.  I set up the Dynamic Input tool to update the IN clause.  I set the Character Limit for the IN clause to be 1000 for example, but Alteryx is replacing only 125 values at a time in my workflow during runtime. Why?

image.png

image.png

image.png

 
The Group Replacement Value for SQL IN Clause determine how the IN clause is processed. By default, an individual query is run for each clause. When selected, this will create a single clause with all the values you input unless it exceeds the Character Limit for the IN Clause. If the the character limit is reached, the single query is divided into smaller queries. The size limit is the threshold of how many characters can be used in the query and not how many values it will use.  

Example:

a. With "Character Limit For IN clause:' = 100

The query is broken into multiple. The result window shows the multiple queries:


image.png

Dynamic Input (6) 12 records were read from aka:Saved_SQL207_User 
(select *  from Sales.SalesOrderHeader  where Sales.SalesOrderHeader.SalesOrderID in ('44343','44344','44345','44346','44347','44348','44349','44350','44351','44352','44353','44354'))

The query is not using 100 values for the In Clause. The query is using 12 values for the In Clause because the are 100 characters in the string: '44343','44344','44345','44346','44347','44348','44349','44350','44351','44352','44353','44354'

c. With "Character Limit For IN clause:' = 1000

The query is broken into multiple. The result window shows the multiple queries:


image.png


Dynamic Input (6)    125 records were read from aka:Saved_SQL207_User 
(select *  from Sales.SalesOrderHeader  where Sales.SalesOrderHeader.SalesOrderID in ('53284','53285','53286','53287','53288','53289','53290','53291','53292','53293','53294','53295','53296','53297','53298','53299','53300','53301','53302','53303','53304','53305','53306','53307','53308','53309','53310','53311','53312','53313','53314','53315','53316','53317','53318','53319','53320','53321','53322','53323','53324','53325','53326','53327','53328','53329','53330','53331','53332','53333','53334','53335','53336','53337','53338','53339','53340','53341','53342','53343','53344','53345','53346','53347','53348','53349','53350','53351','53352','53353','53354','53355','53356','53357','53358','53359','53360','53361','53362','53363','53364','53365','53366','53367','53368','53369','53370','53371','53372','53373','53374','53375','53376','53377','53378','53379','53380','53381','53382','53383','53384','53385','53386','53387','53388','53389','53390','53391','53392','53393','53394','53395','53396','53397','53398','53399','53400','53401','53402','53403','53404','53405','53406','53407','53408'))

The query is not using 1000 values for the IN Clause. The query is using 125 values for the In Clause because the are 1000 characters in the string in the IN Clause.
No ratings