Stage Variable - conditional incrementing
Moderators: chulett, rschirm, roy
Stage Variable - conditional incrementing
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,
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,
Nope. It's a pretty straight-forward check and I don't see any problems with the logic. What worries me is this quote:
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!
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...aravindk wrote:It looks something like this:
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!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
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
if isnull(HOP_COV.COVERAGE_KEY) or LEN(TRIM(INPUT_STREAM.COVERAGE_EXP_DT)) > 0
John
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,
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,
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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:
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.
Code: Select all
if isnull(HOP_COV.COVERAGE_KEY) or NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT)) then KeyMgtGetNextValueConcurrent("not 1") else NextCovKey
When it does not increment, what value is being returned.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
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.chucksmith wrote:When it does not increment, what value is being returned.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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.
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.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
Greetings to all,
Here is an update. I reversed the condition as follows:
Instead of:
I changed it as:
Guess what, it works like magic. This is crazy.
Thanks for all those pitched in to help me;
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
Code: Select all
if NOT(ISNULL(INPUT_STREAM.COVERAGE_EXP_DT)) or isnull(HOP_COV.COVERAGE_KEY) then KeyMgtGetNextValueConcurrent(1) else NextCovKey
Thanks for all those pitched in to help me;