Behold my showstopper in Data Migrator:

(FOC14006) SQL TRANSLATOR ERROR NULL DBE hTblDBE

Here's my situation. I have a process that takes material from one table to another (all SQLServer). After the move I go back and delete from the source if the transformation to the destination succeeds. To do this, I need to match up the key field in the source to the key field in the destination by adding "DC" to the front of it. The define is very simple --

'DC' | FORM_NUMBER

Make the longer key, then use that define in a join to see if there is a matching record in the destination table.

I do this for three different sets of tables. For two of those sets, all runs smoothly. For the third, I get the error I show above. The code and master files are otherwise identical.

When I remove the concatenation from the define all works correctly, although the keys don't match.

I'm very confused as to why it would work in two places and not the other.

Any insight?

Although the masters are identical, are the SQL Server table defintions identical? I'm going to assume the synonym builder was used to create the master so at the time the master was originally created, it matched the SQL Server table. Is it possible that since the master was originally created, it was manually updated, or that the SQL Server table were modified and the master not refreshed?

My thought is that although the master indicates these fields should match in type and size that maybe they really don't.

I'm sure you are famililar with the concept of Automatic Pass-Thru (APT) where if all the source tables are in the same database iWay will if possible simply "pass through" your query to the RDBMS and let it do any JOIN processing.
However if you reference any DEFINEd fields in the synonym, then APT cannot be used, and the join is done using FOCUS instead.
I think that's why you are seeing a difference in behaviour, APT vs NON-APT processing. If so then you might try doing the concatenation IN THE JOIN OBJECT itself, for example T1.KEYFLD = 'DC' || T2.KEYFLD.
Also, another customer reported the same sympton (error message) as you, altho with a somewhat different cause. Their issue was addressed in the latest maintenance release 7.6.7. If you haven't applied this maintenance you may want to do so and see if resolves your issue too.

You da man, Clif.

In the past I've had a lot of trouble with concatenating in the join criteria itself, but based upon your reply I gave it a shot and tried this --

T1.ENCOUNTER_ID = CONCAT('DC', T3.FORM_NUMBER)

It worked without a hitch. Pushing the conversion into the SQL cleared the problem. Please forward me your address so that I can send pie.

J.

Nahh, now I'm getting it on a plain-old join. What a hassle. So much for making a quick change.

J.

John, I take it you're saying you're getting the FOC14006 on something else. Can you post the SQL and the synonyms, as well as any source transformations? Do you get the FOC14006 when you do a check SQL or does it show up in the log? Or both?

It seems to be a by-product of stacking too many files into a single flow. I'm using it as a marker that my code is getting too complicated and that I need to break it into multiple passes.

Likely this will make the code more maintainable, so DM's inability to understand the code is a blessing in disguise -- the problem magically melts away when I pull the exact same source out into it's own smaller flow.

Always nice to know someone is reading this stuff though -- thanks for tuning in.

J.

And . . . it's back. On a small file. This thing is costing me a LOT of hours and I have no clue what the problem is. This time it's not a defined-based join.

I think it's the "all other" error that gets thrown whenever something goes wrong. What a hassle.

Installing 7.6.7 at this time is NOT going to be a popular request.

Still looking for anyone that can give me insight on this ugly thing.

Sag.

This message has been edited. Last edited by: John_Edwards,

I'm not sure what you mean by "a small file." A small data flow? A small source file?

In any case, until you are able to apply the current maintenance to 7.6 (that would now be 7.6.8) please ensure that your select statement can use APT. The run time log will have a message if it can't.

If that doesn't resolve the problem, please open a hottrack case and upload:
* All source & target synonyms (mas/acx files)
* Data Flow (fex/etg files)

This message has been edited. Last edited by: Clif,

Arrrrrrrrrrrrrrrrrgggggggggggggggggghhhhhhhhhhhhhh!!!