Tool generating the wrong SQL and failing on the condition while using parallel option

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

2022-02-14 20:55:05.334 Thread:56 - Error executing select statement for update 
2022-02-14 20:55:05.350 Thread:56 - An expression of non-boolean type specified in a context where a condition is expected, near ','. 
2022-02-14 20:55:05.350 Thread:56 - selectSQL:SELECT "PostalCode","ID","ETL_ISCURRENTRECORD" 
FROM "dbo"."O_PC_ADDRESS" 
WHERE (City NOT IN ('Belleville', 'Shawinigan', 'Dollard-Des Ormeaux', 'Cornwall', 'North Vancouver', 'Vernon', 'Blainville', 'West End', 'Chatham', 'Timmins', 'Chateauguay', 'Quinte West', 'West Vancouver', 'Charlottetown', 'Rimouski', 'Saint-Eustache', 'Grande Prairie', 'Boucherville', 'Salaberry-de-Valleyfield', 'Penticton', 'St. Thomas', 'Rock Forest', 'Joliette', 'Mirabel', 'Mascouche', 'Prince Albert', 'Victoriaville', 'Brant', 'Woodstock', 'Campbell River', 'Ancaster', 'Courtenay', 'Orangeville', 'North Cowichan', 'Moose Jaw', 'Midland', 'Cote-Saint-Luc', 'Saint-Georges', 'Val-d', 'Stratford', 'Orillia', 'Pointe-Claire', 'Baie-Comeau', 'Alma', 'Sainte-Julie', 'West Kelowna', 'Port Moody', 'Lloydminster', 'Boisbriand', 'Brandon', 'Vaudreuil-Dorion', 'Thetford-Mines', 'Walnut Grove', 'Prince Edward', 'Sainte-Therese', 'Airdrie', 'Mount Pearl', 'Saint-Bruno-de-Montarville', 'Rouyn-Noranda', 'Saint-Constant', 'Brockville', 'Langley', 'Whitehorse', 'Owen Sound', 'Chambly', 'Sept-Iles', 'Langford', 'Duncan', 'La Prairie', 'Lower Sackville', 'Port Alberni', 'Keswick', 'Varennes', 'Clarence-Rockland', 'Truro', 'Westmount', 'Kirkland', 'Lindsay', 'New Glasgow', 'Cole Harbour', 'Glace Bay', 'Huntsville', 'Terrace', 'North Battleford', 'Beaconsfield', 'Mont-Royal', 'Beloeil', 'Corner Brook', 'Cranbrook', 'Port Colborne', 'Riviere-du-Loup', 'Dieppe', 'Thorold', 'Miramichi', 'Cobourg', 'Dorval', 'Oak Bay', 'Amos', 'Spruce Grove', 'Pitt Meadows', 'Fort St. John', 'Deux-Montagnes', 'Saint-Augustin-de-Desmaures', 'Conception Bay South', 'Saint-Lazare', 'Sainte-Catherine', 'LAssomption', 'Edmundston', 'LAncienne-Lorette', 'Cochrane', 'Lutes Mountain', 'Sainte-Catherine', 'Salmon Arm', 'Rayside-Balfour', 'Petawawa', 'Fort Erie', 'Candiac', 'Yellowknife', 'Collingwood', 'Mont-Saint-Hilaire', 'Camrose', 'Yorkton', 'Saint-Basile-le-Grand', 'Leduc', 'Pembroke', 'Magog', 'Greater Napanee', 'Kenora', 'le Plateau', 'Fort Saskatchewan', 'Okotoks', 'Gaspe', 'Matane', 'Summerside', 'Prince Rupert', 'Swift Current', 'Colwood', 'Rosemere', 'Williams Lake', 'Saint-Lin-Laurentides', 'Simcoe', 'Quesnel', 'Bay Roberts', 'Mont-Laurier', 'Portage la Prairie', 'Dolbeau-Mistassini', 'Powell River', 'Brooks', 'Bathurst', 'Kentville', 'Elliot Lake', 'Thompson', 'Aldergrove', 'Canmore', 'Hawkesbury', 'North Perth', 'Grand Falls-Windsor', 'Parksville', 'Beauharnois', 'Ingersoll', 'Montmagny', 'Cold Lake', 'Sainte-Marie', 'Uxbridge', 'Fall River', 'Amherstburg', 'Sainte-Marthe-sur-le-Lac', 'Wetaskiwin', 'Pincourt', 'Paris', 'Becancour', 'North Saanich', 'Cowansville', 'Dawson Creek', 'Sainte-Anne-des-Plaines', 'Lambton Shores', 'Sainte-Adele', 'Les Coteaux', 'Whistler', 'Smiths Falls', 'Sylvan Lake', 'Temiskaming Shores', 'Cantley', 'Rawdon', 'Labrador', 'Angus', 'Prevost', 'Mercier', 'Lacombe', 'Strathmore', 'Hanceville', 'South Huron', 'Bells Corners', 'LIle-Perrot', 'Estevan', 'Hinton', 'Notre-Dame-de-l', 'High River', 'Nelson', 'Carleton Place', 'Trail', 'Selkirk', 'Lorraine', 'Steinbach', 'Arnprior', 'Lachute', 'Val-des-Monts', 'La Tuque', 'Weyburn', 'Amherst', 'Dorchester', 'Roberval', 'Saint-Raymond', 'Huron East', 'Kapuskasing', 'Saint-Sauveur', 'Oromocto', 'Kitimat', 'La Malbaie', 'Stony Plain', 'Mont-Tremblant', 'Binbrook', 'Whitecourt', 'Lavaltrie', 'Ladysmith', 'Pont-Rouge', 'Castlegar', 'Otterburn Park', 'Dauphin', 'Bois-des-Filion', 'Winkler', 'Concord', 'Bracebridge', 'Notre-Dame-des-Prairies', 'Dryden', 'Taber', 'Morinville', 'Silver Berry', 'Saint-Felicien', 'Saint-Hippolyte', 'Bridgewater', 'Goderich', 'Wild Rose', 'Renfrew', 'Edson', 'Fort Frances', 'Chandler', 'Kirkland Lake', 'Slave Lake', 'Happy Valley-Goose Bay', 'Wasaga Beach', 'Chibougamau', 'Revelstoke', 'Aylmer', 'Pont Rouge', 'Camlachie', 'Banff', 'Yarmouth', 'Carignan', 'Campbellton', 'Beaumont', 'Innisfil', 'Delson', 'Sydney Mines', 'Baie-Saint-Paul', 'Hanover', 'La Sarre', 'Asbestos', 'Merritt', 'Bluewater', 'Coaticook', 'Hampstead', 'Olds', 'Greenwood', 'Gibsons', 'Carbonear', 'Saint-Zotique', 'Kincardine', 'Plessisville', 'Brownsburg-Chatham', 'Ponoka', 'Mont-Joli', 'Rocky Mountain House', 'Kimberley', 'Perth', 'Parry Sound', 'Pointe-Calumet', 'Morden', 'Coaldale', 'Summerland', 'Stephenville', 'Amqui', 'Beauceville', 'Saint-Joseph-du-Lac', 'Sooke', 'Iqaluit', 'Drayton Valley', 'Farnham', 'The Pas', 'Bromont', 'Saint-Felix-de-Valois') AND LEN(LTRIM(RTRIM(PostalCode)))=7 AND SUBSTRING(LTRIM(RTRIM(PostalCode)),1,1) NOT IN ('0','1','2','3','4','5','6','7','8','9') )
AND "ID","ETL_ISCURRENTRECORD" BETWEEN 3180725 AND 3740613 

Environment

Release : 4.9.1

Component : Fast Data Masker

Cause

We were able to reproduce the issue - the problem is that they are overriding unique columns in their mapping file. They need to either remove the override from the mapping file or ensure that the override is consistent for all masking functions applied to the same table, including the WHERE meta-function. In their particular case, they would need to copy their unique columns to lines with WHERE condition. Also note that it's just advice for a quick workaround, we'll probably need to fix the UI tool if it produces such files.

 

Resolution

The fixed binary is available - FastDataMasker-4.9.240.0.zip - now it should run correctly even with the original mapping file.