Skip to content


Map Data Check SQLValidation Task Error

greglong asked 8 years ago
I have an issue with the Data Map Check function (Task: Sql Validation) which I am unable to get to work. 
SmartConnect Version
I have a simple GP GL Journal import and have created the following SQL Validation task:
I have tried both using a Custom Connection and existing connection
The validation is looking at the GL00105 table and querying my source account column with the GL00105.ACTNUMST column

On integration it fails:

When trying to get back into the Task, I get the following error messages:
Incorrect syntax near the keyword 'distinct'

User Defined Query is not a valid table.  It si only for use with the query builder modify option

These errors occur another 5 times and the task now looks like this: Note Table has changed to say "User Defined Query" instead of GL00105

Looking at the SQL statement in the Query, it looks OK

An ideas of why this is occurring – it seems like an issue!
greglong replied 8 years ago

Should I be logging this issue with support?  It doesn't seem to be setup issue.  Thanks in advance for any help.

kmalone replied 8 years ago

Having the same issue and it seems pretty straight forward. Is it a bug?

greglong replied 8 years ago

I'll send an email to eone support to see if I can get any further response.  I'm hoping to get a better response.

Best Answer
patrick answered 8 years ago
<p>I was able to repro the error that you are seeing.</p>
<p>It looks like a bug when you re-open an existing validation task.</p>
<p>However the validation itself seems to be working fine in my testing. In my testing, i had 6 accounts/lines and 2 were bad in they didn't exist.</p>
<p>When i ran the map, i got the same message:</p>
<p>Completed map pre-run validation task TEST2 unsuccessfully.</p>
<p>If you click the Validation Errors button, it openes up a window where you can see the data and then the EONERUNERROS column.</p>
<p>My errors were(not unexpectedly) Map validation task TEST2 failed for : ACTNUMST = '000-1000-11' Map validation task TEST2 failed for : ACTNUMST = '000-1921-00'</p>
<p>The other 4 accounts were OK so didn't show as errors.</p>
<p>I can run the map multiple times and i get the same results – so it all works and therefore I'd say is all OK.</p>
<p>The query that it runs:</p>
<p>select DISTINCT [GL00105].[ACTNUMST] FROM [GL00105]</p>
<p>is correct in that the application pulls the list of <whatever> (accounts in this case).</p>
<p>Then it validates the data against the resultset it pulled. I would assume it does this so as not to make a DB call for every validation. Not sure if this is a good strategy or not but it is what it is and doesn't matter for this specific issue.</p>
<p>I will enter a bug in for the errors that you get when you re-open the window and it'll be fixed in a future release.</p>
<p>In the meantime before it gets fixed and since it does seem to work correctly in the actual validation, the only thing i can offer is to not edit an existing validation task. </p>
<p>patrick eOne</p>

If you would like to submit an answer or comment, please sign in to the eOne portal.