Finding whether a Feild is null or not

[hexa]

Hi friends,

I want to write a before subroutine that finds out whether a field is null or not and if it is null then gives the field a value =0

i have written the following code

OPENSEQ "D:\DS325Files\SalesRecord.txt" TO H.LKEY
ELSE
ErrorCode = 1
Call DSLogInfo("Error in Open","TransformationJobs")
ABORT
END
LOOP
READ File.Rec FROM H.LKEY
ELSE
ErrorCode = 1
Call DSLogInfo("Error in Read","TransformationJobs")
ABORT
END

IF ISNULL(Field(File.Rec,",",3))
THEN
**********************
END
ELSE
ErrorCode = 1
Call DSLogInfo("Error in write","TransformationJobs")
ABORT
END
REPEAT
CLOSESEQ H.LKEY

ErrorCode = 0; * set this to non-zero to stop the stage/job

I want to know the syntax for giving the feild a value = 0
i am not clear abt the syntax to be put in *******

Can anyone guide

Thanks

[tonystark622]

hexa,

I'm not sure I completely understand, but I'll give it my best shot.

I don't understand why you would want to do this in a Before Routine. I would put it in the Derivation for the field on the output link.

The logic would be something like:

Code: Select all

If Not(IsNull(InputLink.FieldName)) Then InputLink.FieldName Else 0

Hope this helps. If I've completely missed your point, please explain further and I'll try to help.

Tony

[hexa]

Hi!

I need to use the routine. Its a must
I know there are other easier ways of doing it but i need to do it in basic

Anyway Thanks for ur reply and guide me if possible

[chulett]

All questions about why it's a "must" and you "need" to do it this way aside...

It seems to me your question is not so much about determining if a field is null or not. That's simple and no different than you'd do in a Derivation inside a job.

What it looks to me what you are doing is reading a flat file and wanting to update the record/field in the same flat file with a zero if it is null. If so, then the answer is no. What you can do is read it in and write it out to a new version of the file with the changes made to those fields. Then, if you wanted, remove the original file and rename the new file to the old name.

So much simpler to handle this inside a job. Perhaps you could share what problem you are trying to solve and people here could then provide you with alternative approaches or solutions. Another pair of eyes and all that.

ps. You'll need to use READSEQ, not READ. WRITESEQ is another thing you'll need. WEOFSEQ may come in handy, as well as the SEEK command, possibly. Check your BASIC manual. And me, personally... I would never code an ABORT into something like this.

[ray.wurlod]

Technically it's not possible in a before-stage subroutine because the first row in a stream has not been processed when the before-stage subroutine is executed.

Do you mean null in every row, null in any row, null in the first row?

How is NULL represented in this particular text file. A zero-length string is not, in and of itself, the same thing as NULL; "" is a known value, NULL is not. By default, the Sequential File stage maps "" to null, but this is only a default and can be changed on the Format tab.

Have you really thought this through? Can you post your algorithm design and test plan? This may make it easier to assist you.