Hi all,
I wanted to compare two different tables with each other via a minus/except comparison.
However I noticed that I get a different amount of rows (~1500 in the first and ~470 in the second case) , if I run
Select * from tab1 minus Select * from tab2
vs
Select col1…coln from tab1 minus Select col1…coln from tab2
I copied all the column names from the processor, so I am quite certain, that the difference is not due to the fact, that I have forgotten a column.
If I add a query processor between the processors that provide the input and the minus/except processor, and I specify the exact columns, I can do the Select * in the minus comparison and get 470 rows as well.
Spontanenously, the following comes to my mind: I could imagine that ONE DATA’s hidden __ROW_ID__ column is playing tricks on you here. It seems as if you are unknowingly taking __ROW_ID__ into account in the first case, and likely get all rows of tab1, whereas you do what you actually want to do in the second case.
This theory would need to be verified by debugging the server code. You might also try to select that special column and rename it via an alias to see whether your queries can see it.
I wasn’t able to select the column, but that would explain the behavior indeed
You actually can filter out the __ROW_ID__ with the following processor’s code. You can import it by clicking on “Import” in a Workflow, and pasting in the processor’s code.
JE9EQ0xCUkQkeyJ0eXBlIjoibm9kZXMiLCJkYXRhIjp7Im5vZGVzIjpbeyJpZCI6IjIzNGRhMGE3LTE4NmItNDYxNy1hZmNkLTdkMzU4NzY1NzhiNiIsInByb2Nlc3NvciI6IjAwMDAwMDAwLTAwMDAtMDAwMC0wMDAyLTAwMDAwMDAwMDAwMiIsImNvbmZpZyI6eyJleGNsdWRlIjpbeyJuYW1lIjoiX19ST1dfSURfXyJ9XSwidGFibGVuYW1lIjoiIiwiYWxsb3dFbXB0eU91dCI6ZmFsc2V9LCJyZXN1bHQiOm51bGwsIm9wdGlvbnMiOnsiY29uZmlnRWRpdEV4cGVydE1vZGVFbmFibGVkIjpmYWxzZX0sIngiOi0xMzYsInkiOi0xOCwibmFtZSI6IkV4Y2x1ZGUgQ29sdW1ucyAocm93IGlkKSIsImNvbG9yIjp7Im5hbWUiOiJjeWFuIDcwMCIsImhleCI6IiMwMDk3YTciLCJzdHlsZSI6eyJjb2xvciI6InJnYigyNTUsMjU1LDI1NSkiLCJiYWNrZ3JvdW5kLWNvbG9yIjoicmdiKDAsMTUxLDE2NykifSwiJCRoYXNoS2V5Ijoib2JqZWN0OjE0NDAwIn0sIm91dFR5cGVzIjp7IjIzNGRhMGE3LTE4NmItNDYxNy1hZmNkLTdkMzU4NzY1NzhiNi9vdXQuZGF0YSI6W119LCJjb25maWdUeXBlcyI6e30sImhhc0pvYkVycm9ycyI6ZmFsc2UsImhhc0pvYlJlc3VsdCI6ZmFsc2UsImhhc0F1dGhvcml6YXRpb25EaW1lbnNpb25BcHBsaWVkIjpmYWxzZSwidGlFcnJvciI6ZmFsc2UsImNvbmZpZ1ZhbGlkIjp0cnVlLCJoYXNOb3RlcyI6ZmFsc2UsImNvbm5lY3RvcnMiOlt7InR5cGUiOiJpbiIsImlkIjoiMjM0ZGEwYTctMTg2Yi00NjE3LWFmY2QtN2QzNTg3NjU3OGI2L2luLmRhdGEiLCJwb3J0TmFtZSI6IklucHV0IiwiY29udGFpbnNEZWJ1Z0luZm9ybWF0aW9uIjpmYWxzZX0seyJ0eXBlIjoib3V0IiwiaWQiOiIyMzRkYTBhNy0xODZiLTQ2MTctYWZjZC03ZDM1ODc2NTc4YjYvb3V0LmRhdGEiLCJwb3J0TmFtZSI6Ik91dHB1dCIsImNvbnRhaW5zRGVidWdJbmZvcm1hdGlvbiI6ZmFsc2V9XSwiam9iSGFzR2VuZXJhbEVycm9yIjpmYWxzZSwiaGFzSm9iV2FybmluZ3MiOmZhbHNlLCJvblRoZUZseVJlc3VsdCI6eyJ0eXBlIjoib25UaGVGbHlSZXN1bHQiLCJwcm9jZXNzb3JSdW50aW1lSWQiOiIyMzRkYTBhNy0xODZiLTQ2MTctYWZjZC03ZDM1ODc2NTc4YjYiLCJyZXN1bHROYW1lSW5EZWZpbml0aW9uIjoiX09OX1RIRV9GTFlfUkVTVUxUXyIsInNjaGVtYU9uRXhlY3V0aW9uIjpudWxsLCJ3YWxsVGltZU1pbGxpcyI6NiwidG90YWxUaW1lTWlsbGlzIjo0OSwiY3VzdG9tUHJvY2Vzc29yTmFtZSI6bnVsbH0sImNvbmZpZ1N0YXR1cyI6eyIkJHN0YXRlIjp7InN0YXR1cyI6MSwidmFsdWUiOm51bGx9fX1dLCJlZGdlcyI6W10sImdyb3VwcyI6W10sIm5vZGVOb3RlcyI6e319fQ==
@ThomasZ Can you maybe try to filter out the ROW_ID using this processor from both input tables you are trying to compare, and see if you still get a differing number of rows compared to manually selecting them?