IsNotNull in transformer stage - won't compile

[James47889]

Hi everyone,

I have a Sequential File stage connected to a Transformer stage and then onto an Oracle DB stage loaded onto the server. The information in the Sequential File stage is coming from a .csv and all information is in single rows, so I am using the Field function in the Transformer to split the long row up into segments and put into individual columns in the Oracle stage.

SF column name = EMP_DATA
SF stage link = Employee_Source

The pipe | is the delimiter in the .csv

I am trying to use the IsNotNull function to firstly look at all rows that aren't null and keep them being populated with data as normal, and then looking at all values that are Null and adding the word 'UNKNOWN' to these rows.

I have tried to get it to work but have been unsuccessful. This is my code:

If IsNotNull(Field(Employee_Source.EMP_DATA,'|',47)) Then (Field(Employee_Source.EMP_DATA,'|',47)) Else 'UNKNOWN'

I should also add that is has been working fine and populating rows and columns in the table absolutely fine with the basic: Field(Employee_Source.EMP_DATA,'|',47)
It is only the IF statement combined with the IsNotNull that isn't working.

If anyone can help me or provide any advice I'd greatly appreciate it!

Thanks :D

[chulett]

Parallel function only. Use Not(IsNull(xxxx)) in a Server job. Or you could write your own.

[James47889]

Thanks for the quick response! Oh whoops sorry I meant this is a parallel job. I don't know if that means IsNotNull should work in parallel or not? Regardless, I've tried the above as follows:

If Not(IsNull(Field(Employee_Source.EMP_DATA,'|',47))) Then Field(Employee_Source.EMP_DATA,'|',47) Else "UNKNOWN"

I'm getting the following errors:

##E IIS-DSEE-TFEV-00015 17:05:43(011) <transform> Error when checking composite operator: Only an input field can be "null" function argument: [line 37,character 12].
##E IIS-DSEE-TFEV-00018 17:05:43(012) <transform> Error when checking composite operator: A composite call is not supported for function "null"; [line 37,character 12].

[chulett]

Well... you did mark the job type as Server and posted in the Server forum.

Let's start by getting you into the proper forum... and here we are.

[James47889]

Sorry! Completely new to DSXchange, should learn to read

[chulett]

I'm assuming the error you saw with the IsNotNull() function was similar? That it wants you to check an input field rather than the results of a composite function call? If so, seems to me you'd need to store the result of the Field() function in a stage variable and then do a separate null check.

[James47889]

Yes it was a similar error with IsNotNull. Ah that makes sense. I'll get started on that and post results. Thanks :D

[chulett]

No worries... if everybody followed all of the rules and put everything right where it belonged the first time, I'd be out of a Moderator job. :D

Speaking of which, use the Reply with quote option when there's a reason to quote something, say several posts down the road when you are replying to something earlier. Most of the time a simple Reply to topic works just fine. And saves me the work of cleaning up the spurious quoted text. LOL

ps. I'm assuming your 8.x version is high enough to support null stage variables.

[James47889]

Ah the buttons right there!! I couldn't see it before. Got it thanks

[ray.wurlod]

The error message is not about the function itself, but about the fact that you're trying to apply it to something (a Field() function) that is not an input field. You need to re-work your logic so that you're only testing an input field with IsNotNull().

Why not just use NullToValue() function?