IsValid and SetNull

ds_developer · Post by **ds_developer** » Fri May 04, 2007 11:47 am

I am getting a runtime error anytime I use IsValid and SetNull together in an IF THEN ELSE statement in a derivation.

The error is: TargetTable,0: Operator terminated abnormally: received signal SIGSEGV

The target is the Oracle Enterprise Stage and I have verified the field can be nulled by using SetNull() alone as the derivation.

The derivation that causes the job to abort is:

Code: Select all

IF IsValid("Timestamp",svBaseDate) THEN StringToTimestamp(svBaseDate) ELSE SetNull()

I have verified the stage variable is formatted correctly by using this line:

Code: Select all

IF IsValid("Timestamp",svBaseDate) THEN StringToTimestamp(svBaseDate) ELSE TimestampFromDateTime(pLOWDATEVALUE, "00:00:00")

(pLOWDATEVALUE is just a parameter set to "0001-01-01" that is used as a default date)

When I run this derivation, the job runs successfully.

I would like to set a nullable date field to null instead of some default value.
Any ideas why this is happening?

Thanks.
John

Edwink · Post by **Edwink** » Fri May 04, 2007 12:24 pm

i m using the same function in my jobs it is running fine...

once i got the same problem ...but issue is, target field is not nullable..

DSguru2B · Post by **DSguru2B** » Fri May 04, 2007 1:00 pm

I think its an issue with SetNull() in a stage variable. I know I have read something about it here. Search in the archives.

ds_developer · Post by **ds_developer** » Fri May 04, 2007 1:15 pm

The SetNull is not in the stage variable derivation. It is in the derivation of a timestamp field on the link going directly to the Oracle Enterprise Stage. I am using a stage variable but all it does is format into the default timestamp (yyyy-mm-dd hh:nn:ss)

I appreciate the help!
John

DSguru2B · Post by **DSguru2B** » Fri May 04, 2007 1:40 pm

A small correction in your stage variable derivation. You need a space between the date part and the time part.

Code: Select all

IF IsValid("Timestamp",svBaseDate) THEN StringToTimestamp(svBaseDate) ELSE TimestampFromDateTime(pLOWDATEVALUE, " 00:00:00")

ds_developer · Post by **ds_developer** » Fri May 04, 2007 2:40 pm

Thanks for your continued attention.

TimestampFromDateTime() has 2 parameters: one Date and one Time, so I don't think the space is needed. I'm not building a timestamp string here.

Thanks again.
John

DSguru2B · Post by **DSguru2B** » Fri May 04, 2007 2:42 pm

Duh....Clumsy me. Sorry about that. Most of the cases I have dealt with is the absence of space between the date part and time part. Anywho, good luck.

ds_developer · Post by **ds_developer** » Mon May 07, 2007 2:19 pm

... got off on a bit of tangent there for a while.

Is anyone able to use IsValid and SetNull in an IF THEN ELSE statement of a derivation? It is causing an abort for me.

Any ideas?

Thanks,
John

nick.bond · Post by **nick.bond** » Mon May 07, 2007 4:07 pm

ds_developer, are you doing this in stage variables? you can't set a stage variable to null in DS EE. If you are using an unpatched version of DS client then it will let you compile it but won't work.

ds_developer · Post by **ds_developer** » Mon May 07, 2007 4:16 pm

The derivation is using a stage variable, but I not trying to set the stage variable to null. The derivation that is causing the abort is on the link to the Oracle database. In DataStage, the field is defined as a nullable Timestamp. In Oracle, the field is defined as a nullable Date. I have verified I can null the field by using SetNull() alone in the derivation.

Thanks for looking.
John

nick.bond · Post by **nick.bond** » Mon May 07, 2007 4:28 pm

Sorry that was lazy of me, I only ready your last question and didn't see that everything I said had already been discounted.

I have used both IsValid and SetNull in a column derivation before, but I guess something must be different.

One thing to try is to use APT_DISABLE_COMBINATION = 'True' in the job just to see if any different error comes out in the log.

The only other thing I can think of, which is only a workaround, is to use a stage variable to do the

Code: Select all

 IF IsValid("Timestamp",svBaseDate) THEN StringToTimestamp(svBaseDate) ELSE TimestampFromDateTime(pLOWDATEVALUE, " 00:00:00")

and then in your column derivation check for the low date and set to null.

ds_developer · Post by **ds_developer** » Wed May 09, 2007 8:27 am

Thanks for the information Nick.

Setting APT_DISABLE_COMBINATION = True did not change the error message and the job still aborts.

I tried your suggestion about catching the LOWDATEVALUE and then setting the NULL, but that gave mixed results. What I found out is if all my data is valid (meaning no records take the ELSE SetNull() statement) or all the rows take the ELSE SetNull() the job runs fine. When my data set contains valid and invalid data, taking the THEN and ELSE portion on different records, it aborts.

Are better descriptions of the APT_ environment variables available anywhere?
Thanks,
John

nick.bond · Post by **nick.bond** » Wed May 09, 2007 3:55 pm

Descriptions of the APT variables are in the back of the Advanced Parallel job developers guide.

...just came to me....when your dates are Not Valid, are they null?

I have faint recollection that you can't pass a null into IsValid, so you have to check whether the input is null first, then check if it is valid and then set the value.

Stupid I know but I think that is how I had to use it in the past. I don't have EE here so can't check this for you, so I hope I'm not leading you astray but maybe you could check this.