Stage Variable - conditional incrementing

aravindk · Post by **aravindk** » Fri Jun 25, 2004 6:54 am

Hello all,

I am incrementing a stage variable based on two conditions. It looks something like this:

NextKey = if (isnull(ref_link.ref_key)) or not(isnull(input_link.exp_dt)) then KeyGetNextConcurrent(1) else NextKey

This increment works for the first condition but not the second. When ref_link returns null on a lookup it correctly generates the next key. But if the input_link.exp_dt is not null, it is not incrementing. It is using the previous value instead.

Input_link is my primary input and the ref_link is a lookup stage. Any idea why this is not working?

Thanks in Advance,

chulett · Post by **chulett** » Fri Jun 25, 2004 7:10 am

Nope.

It's a pretty straight-forward check and I don't see any problems with the logic. What worries me is this quote:

aravindk wrote:It looks something like this:

I've seen too many times where we've chased our tales because someone typed something in instead of using cut-and-paste and either didn't get it quite right or unconsciously fixed whatever problem there was with it.

So, just to be safe...

Could you cut-and-paste your derivation directly from the job and use the 'code' tags so we can see exactly what it looks like?

Thanks!

aravindk · Post by **aravindk** » Fri Jun 25, 2004 7:25 am

Here you go:

Code: Select all

if isnull(HOP_COV.COVERAGE_KEY)  or NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT))  then KeyMgtGetNextValueConcurrent(1) else NextCovKey

chulett · Post by **chulett** » Fri Jun 25, 2004 7:56 am

Well, that helped.

Glad to see you didn't change the gist of it in your original post.

Still don't see anything wrong with it, however. Might be the kind of problem that needs to be looked at in context - i.e. in your job as a whole.

Unless someone else has some insight here?

ds_developer · Post by **ds_developer** » Fri Jun 25, 2004 8:51 am

I would do a through investigation of the INPUT_STREAM.COVERAGE_EXP_DT field. Is it truly NULL or could it be empty or blanks? It looks like a date to me. Is it a foreign key to the date table? If so, it is likely it cannot be NULL. You might need to change your condition to something like:

if isnull(HOP_COV.COVERAGE_KEY) or LEN(TRIM(INPUT_STREAM.COVERAGE_EXP_DT)) > 0

John

aravindk · Post by **aravindk** » Fri Jun 25, 2004 9:03 am

John,

COVERAGE_EXP_DT is a date field. It is not a fk to the date table.It is always either NULL or a date in that field. No blanks in this field. Atleast not in the bunch of records i am testing with. Also I tried this in debug mode and the INPUT_STREAM.COVERAGE_EXP_DT does contain a date, so i would expect not(isnull(INPUT_STREAM.COVERAGE_EXP_DT)) to return true.

Thanks for trying to help me,

chucksmith · Post by **chucksmith** » Fri Jun 25, 2004 9:17 am

Looking at KeyMgtGetNextValueConcurrent, perhaps a row lock exists on row 1 in the SDKSequences hash file. This would cause a key value of 1 to be returned. Try changing your code to:

Code: Select all

if isnull(HOP_COV.COVERAGE_KEY)  or NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT))  then KeyMgtGetNextValueConcurrent("not 1") else NextCovKey

This might avoid the locked record. If it works, then we can look at unlocking it.

When it does not increment, what value is being returned.

chulett · Post by **chulett** » Fri Jun 25, 2004 9:31 am

chucksmith wrote:When it does not increment, what value is being returned.

That's the question. I wondered about the routine as well, but as you noted problems with it would return a 1 - which would reset the sequence rather than cause it to not increment.

aravindk · Post by **aravindk** » Fri Jun 25, 2004 9:51 am

It returns the previous value. I have five records in my test case. the first four have nulls in the coverage_exp_dt but they satisfy the first condition (hop_cov.coverage_key is null) so it returns the following four values one per record:

Natural_key Seq_key
1 578
2 579
3 580
4 581

The fifth one has a natural key of 2, so it reads the table and finds the record. Thus the first condition is false. This fifth record has a value in coverage_exp_dt. Now the NextKey has the previous value which is 581.
If I continue to write, it will write the following to the table:

2 581.

chucksmith · Post by **chucksmith** » Fri Jun 25, 2004 1:27 pm

Something must not be as it seems. I would add a link to a debug file so I could verify each condition individually.

If your lookup is to a hash file (I hope it is), how is cache set. If the file is preloaded into memory, then the copy being used for the lookup is not being refreshed with your updates.

What exact version of 6 are you using? More details will still help us help you.

aravindk · Post by **aravindk** » Fri Jun 25, 2004 5:29 pm

The version I use is 6.0.1.5.

The lookup stage is not an hash file. It is a table on sql server.

aravindk · Post by **aravindk** » Mon Jun 28, 2004 7:38 am

Greetings to all,

Here is an update. I reversed the condition as follows:

Instead of:

Code: Select all

if isnull(HOP_COV.COVERAGE_KEY)  or NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT))  then KeyMgtGetNextValueConcurrent(1) else NextCovKey

I changed it as:

Code: Select all

if   NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT))  or isnull(HOP_COV.COVERAGE_KEY) then KeyMgtGetNextValueConcurrent(1) else NextCovKey

Guess what, it works like magic. This is crazy.

Thanks for all those pitched in to help me;