SQLCODE - Upsert

akrzy · Post by **akrzy** » Wed Feb 23, 2005 9:48 am

What am I doing wrong that the following error appears :
"
Sequential_File_10: Error when checking operator: Could not find input field "sqlcode"
"

I use write mode=Upsert and reject=TRUE.

ArndW · Post by **ArndW** » Wed Feb 23, 2005 10:34 am

akrzy,

I think you need to explain a bit more. "upsert" doesn't apply to sequential files, so you are probably working with a table as well ... When are you getting the error and what are you doing? Could your sequential output file be a reject link from database output stage?

ray.wurlod · Post by **ray.wurlod** » Wed Feb 23, 2005 3:06 pm

Have you used the name Sequential_File_10 for a database stage??

The error message suggests that you have an expression that uses a field name sqlcode, but that this does not exist in an input stream, or is incorrectly qualified (left over from an earlier version of the job design perhaps).

akrzy · Post by **akrzy** » Wed Feb 23, 2005 3:56 pm

Let me explain.

My job reads data from flat file and writes them to database table for example MS SQL Server.

In the Propertise tab (ODBC) I set "Write mode = Upsert" to reject rows to flat file (Sequential_File_10).

For every destination table I have the same error:
"Could not find input field "sqlcode" "

ArndW · Post by **ArndW** » Thu Feb 24, 2005 2:46 am

Akrzy,

did you check your SQL as Ray suggested? Could you be using "sqlcode" incorrectly as a parameter somewhere? Is your error still present when you remove the reject sequential file link?

akrzy · Post by **akrzy** » Thu Feb 24, 2005 3:45 am

The "sqlcode" coulmn appears in the output link when I check rejected=TRUE (databse stage). This columns appears automaticlaly.

ArndW · Post by **ArndW** » Thu Feb 24, 2005 4:13 am

and does the "sqlcode" column show up in the column definitions of the sequential reject file (it should)? Perhaps you need to turn off column propagation for this stage... do you have it set?

akrzy · Post by **akrzy** » Thu Feb 24, 2005 4:36 am

Yes, "sqlcode" there is in the column definitions of the sequential reject file

vigneshra · Post by **vigneshra** » Thu Feb 24, 2005 4:57 am

Hi akrzy,
As Arnd says, you need to turn off the column propagation for the reject link. That will solve the problem. Again, I raise a question here. What is the impact in the performance on turning on/off the column propagation option? Is it significant?

akrzy · Post by **akrzy** » Thu Feb 24, 2005 5:01 am

How can I turn off the column propagation for the reject link?

ArndW · Post by **ArndW** » Thu Feb 24, 2005 6:49 am

akrzy,

go into your Oracle stage, to the output link and in the bottom left of the COLUMNS tab you will see the checkbox.

akrzy · Post by **akrzy** » Thu Feb 24, 2005 7:14 am

I can't see any checkbox

akrzy · Post by **akrzy** » Thu Feb 24, 2005 7:41 am

I've found.
In the ADministrator I checked "Enable runtime column propagation".
But It doesn't work still.

ArndW · Post by **ArndW** » Thu Feb 24, 2005 8:11 am

You need to uncheck the box in the designer of that job stage, not in the administrator. Are you writing straight from the Oracle stage into a Sequential error file, or are you going through a transform? I just wrote a dummy job with a reject link and it worked fine... Perhaps you could delete the file & link from your job and recreate it - just to make sure that it isn't some issue in the job design. Also, did you enable the tick-box in the Oracle stage to write the rejects to a reject link?

vigneshra · Post by **vigneshra** » Thu Feb 24, 2005 8:25 am

Hi akrzy,
Just double-click the stage from which the link to the reject file arises (It may be a transformer or a database stage itself). Go to the column tab and find the check-box at the bottom left. Just uncheck and your problem will be solved! I don't know any other way to explain :D