Automate

Hello, 

We have run into a problem with a chain of query scripts that we use to review article master data.  The initial script queries table CDHDR to find which articles were created or changed in a particular week.  All other subsequent scripts use those results using the IN operator to return data from all other article master data tables for us to perform a data quality review.  Recently, we have provided the business with transaction scripts to allow for mass updating data.  However, this has allowed them to change over 1000 articles a week and now our query script chain is failing due to size limitations.  I searched online and found that the IN operator can only use up to 1000 entries which I believe is causing the issue.  Is there any way to use a list of inputs that change week to week without using the IN operator or is there a way to still use IN but without these issues?  

Thanks, 
Mark

------------------------------
Mark Turnbull | Operational Data & Reporting Specialist
Province of Nova Scotia | 9024566149
------------------------------

Hi Mark

You could run the same query chained together for whatever you think the maximum number would be.

• Query 1 uses the dynamic input - instead of to the end of the list, choose a number - say column B row 2 to 900 - Choose output to be clear prior run
• Query 2 picks up at column B row 901 - 1800 - Choose output append
• Query 3 picks up at column B row 1801 - 2600 - Choose output append
• etc

Please note with the append feature, you may end up with up to 5 blank rows between the data sets.  It's an 'as designed' feature. :)

Hope that gives you an idea.

Best Regards,
Sigrid

------------------------------
Sigrid Kok
PSE | Winshuttle NA
------------------------------

Original Message:
Sent: 02-11-2022 12:46
From: Mark Turnbull
Subject: Looking for Query IN Limitation Workaround

Hello,

We have run into a problem with a chain of query scripts that we use to review article master data.  The initial script queries table CDHDR to find which articles were created or changed in a particular week.  All other subsequent scripts use those results using the IN operator to return data from all other article master data tables for us to perform a data quality review.  Recently, we have provided the business with transaction scripts to allow for mass updating data.  However, this has allowed them to change over 1000 articles a week and now our query script chain is failing due to size limitations.  I searched online and found that the IN operator can only use up to 1000 entries which I believe is causing the issue.  Is there any way to use a list of inputs that change week to week without using the IN operator or is there a way to still use IN but without these issues?

Thanks,
Mark

------------------------------
Mark Turnbull | Operational Data & Reporting Specialist
Province of Nova Scotia | 9024566149
------------------------------