Nulls in dates, Rnd function problems

[gateleys]

I have a stage variable sv_weeks which performs a DateDifference. I had to check NULL condition for fire_date, if it is NULL set to 27, else set it to difference between the 2 dates of type Timestamp (23,3).

Code: Select all

sv_weeks = If fromX.fire_date = char(128) then 27 else DateYearFirstDiff(fromX.fire_date, fromX.hire_date)/7

Now, emp_duration (integer 3) =

Code: Select all

if sv_weeks < 27 then weeks else 0

Problems:
1. The transformer is followed by an AGG (where derivation is Last(emp_duration) and type interger 3)...which when run warns -
myJob..AggSal: At row 4429, link "outTrans1", while processing column "outAgg1.emp_duration"
Value treated as NULL
Attempt to convert String value "" to Long type unsuccessful

2. If I use the Rnd function in the DateYearDifference function to round to the next week, then sv_weeks (which are NULL) are evaluated to being > 27 and temp_duratoin takes the else path. But I want it to be set to 27 if it is Null.

Please tell me where I have gone wrong.

[ketfos]

Hi,
You are trying to calculate the diff between to date fields.
fromX.fire_date, fromX.hire_date)/7

Both teh fields are same.

Ketfos

[gateleys]

Hi,
You are trying to calculate the diff between to date fields.
fromX.fire_date, fromX.hire_date)/7

Both teh fields are same.

Ketfos

If only you would "see" properly..they are 2 different dates- hire and fire.
May be you had an illusion.

[ketfos]

Hi,
I am sorry. I was just trying to help you.

Thanks so much for a nice response.

ketfos

[ray.wurlod]

Prefer IsNull() to "= Char(128)". It is more efficient and more reliable.

The Rnd() function generates a random number. You need the Fix() or Oconv() function to do rounding.

[gateleys]

Prefer IsNull() to "= Char(128)". It is more efficient and more reliable.

The Rnd() function generates a random number. You need the Fix() or Oconv() function to do rounding.

Hi Ray, I tried Fix() to round the number as below, but it still doesn't round it. Below is the code which takes in 2 dates and computes number of weeks between them. If number is 2.1 or 2.9, it should still return 3.

Code: Select all

Fix((DateYearFirstDiff(TheLink.date2, TheLink.date1))/7)

[chulett]

Curious if you read the online help for the Fix function? With no options it rounds to a default precision of four - and you want zero.

Plus, what you want is not strictly 'rounding' as rounding would take 2.9 up to 3 but would also take 2.1 down to 2. You need to play the old game of adding .5 to the computed value before rounding to get the results you desire.

I've never had the pleasure of fix'ing anything before but I'd try:

Code: Select all

Fix((DateYearFirstDiff(TheLink.date2,TheLink.date1)/7)+.5,0)

and see if that works. Of course it is late and I have yet to go home, so no guarantees.

[ray.wurlod]

Fix(number, precision, mode)

Mode rounds excess digits if 0, truncates if non-zero.

All this and more is in the DataStage BASIC manual.

[chulett]

Mode rounds excess digits if 0 or not specified, truncates if non-zero.

[ray.wurlod]

Good catch. Of course I know that the function says, internally, "if not specified use 0", but not everyone else does.