Knowledge Base / Designer / How the 'Group Replacement Value for SQL IN Clause' works when using SQL: Update WHERE Clause i...
Procedure
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?
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:
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 parts. The result window shows the multiple queries:
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.
Example:
a. With "Character Limit For IN clause:' = 100
The query is broken into multiple. The result window shows the multiple queries:
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 parts. The result window shows the multiple queries:
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.