DSXchange

When you ftp the file from windows to unix, ftp it from command line, this way it will transfer in ascii mode and change all the CR/LR to LF. That pipe type character was actually a CR i.e, char(013).

Specify the file type as DOS and see if it shows up after the change.

I think even when you pass NULL, it will overwrite the previous record. Because all three keys are the same. I have to try that but as far as I know it will be overwritten. Build a small job that does that and you will get the results.

ArndW wrote:What are your column definitions? At least one of these is incorrect for DataStage.

Post the reply for ArndW's question. There is little we can do sitting on the other side of your screen. Try making the date column as varchar and see if that helps.

Did you try ArndW's suggestion?
Alternatively, do all the hardcoding in the transformer and dont hardcode anything inside the sql.
Are you getting any sql codes along with your error message, including the warning messages?

Did you try Inserting from command line? Does that work? Have you tried just insert and just update?

O yea, sorry thats the stage name. Hmm, so an open link can still be counted for row counts, I didnt know that. I was under the impression that the link as to close to be able to get the link count. Thanks for the clarification.

You think its the CURRENT DATE that might be the problem? What database are you connecting to. Get the current date within the transformer and pass it to the column "EFF_DT"

And I believe "x_Extract" is the name of the link connected to Oracle, correct?
That link is still open and hence you cannot get the correct result. You have to do this after the link closes. Probably create a file and then in a second job load that file into Oracle.

Apologies on my part, I re-read your previous post. Insert is working but update is not huh.. Have you tried generated sql query as I asked you before. The error maybe due to the fact that you are passing less number of columns in update as compared to the number specified in the column's tab. Try a...

Is it because your missing an "I" from Insert

How many stage variables are you using? Can you paste them here and their respective derivations.

It should work. Hmm, dont know whats going on. Well untill someone throws some more light on it, resort back to your original code. Its not good to have red in your transformer.

You can echo the value of that environtment variable using DSExecute() and assign it to your routine variable. This was discussed here a few weeks back. Search for more details.

Did this happen after assigning the function to the initial value? What version of DataStage are you on?