is there any wrong in routine
Moderators: chulett, rschirm, roy
is there any wrong in routine
Hi all,
i am using follwing routine but i am not getting what i required in the output. i am using stage variables for that , i have written follwing expression in stage variable "change"
if Fundname2 <> Fundname1 or Fundname1='' and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1 .
sample :
input
fundname date TNA
adamsINC 6/30/2004 1248430
adamsINC 12/31/2003 1218862
adamsINC 12/31/2004 1295549
output should be like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 0
adams INC 12/31/2004 1295549 76687
i am getting like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 -29568
adams INC 12/31/2004 1295549 76687
what is wrong with my expression,
thanks in adavnce
i am using follwing routine but i am not getting what i required in the output. i am using stage variables for that , i have written follwing expression in stage variable "change"
if Fundname2 <> Fundname1 or Fundname1='' and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1 .
sample :
input
fundname date TNA
adamsINC 6/30/2004 1248430
adamsINC 12/31/2003 1218862
adamsINC 12/31/2004 1295549
output should be like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 0
adams INC 12/31/2004 1295549 76687
i am getting like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 -29568
adams INC 12/31/2004 1295549 76687
what is wrong with my expression,
thanks in adavnce
Re: is there any wrong in routine
In the second set of input, 1218862+29568 = 1248430, which is the amount in the first line of input. My guess is that you have either your variables declared in the wrong order or you need to zero them out with each row. The TNAamout1 is still holding the value frome the previous row.
harithay wrote:Hi all,
i am using follwing routine but i am not getting what i required in the output. i am using stage variables for that , i have written follwing expression in stage variable "change"
if Fundname2 <> Fundname1 or Fundname1='' and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1 .
sample :
input
fundname date TNA
adamsINC 6/30/2004 1248430
adamsINC 12/31/2003 1218862
adamsINC 12/31/2004 1295549
output should be like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 0
adams INC 12/31/2004 1295549 76687
i am getting like this
fundname date TNA changeTNA
adams INC 6/30/2004 1248430 0
adams INC 12/31/2003 1218862 -29568
adams INC 12/31/2004 1295549 76687
what is wrong with my expression,
thanks in adavnce
tahnks craig ;
i am sorry before i did not use parens properly so i got syntax error,
now i am using follwing expresion but i am not getting proper output
if (Fundname2 <> Fundname1) or (Fundname1='' ) and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1
if it is wrong please let me know
tahnks
i am sorry before i did not use parens properly so i got syntax error,
now i am using follwing expresion but i am not getting proper output
if (Fundname2 <> Fundname1) or (Fundname1='' ) and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1
if it is wrong please let me know
tahnks
Since we don't know for sure what you need, it either needs to look like this:
or:
One of the two should be what you had in mind.
Code: Select all
if (Fundname2 <> Fundname1 or Fundname1='') and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1
Code: Select all
if Fundname2 <> Fundname1 or (Fundname1='' and DateGenericDateDiff(Date2, Date1) < 365) then 0 else TNAamount2-TNAamount1
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Derivations of Fundname1 and Fundname2 would be useful in providing a more exact diagnosis, as well as any other stage variables upon which they in turn depend.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Code: Select all
if (Fundname2 <> Fundname1) or (Fundname1='' ) and DateGenericDateDiff(Date2, Date1) < 365 then 0 else 1
Code: Select all
if (a<>b or b=1) and c<b then 0 else 1
Code: Select all
if a<>b or (b=1 and c<b) then 0 else 1
Code: Select all
if (a<>b or b=1 and c) < b then 0 else 1
[/code]
Mamu Kim
Remember the battlecry of the parenthesis: Protect the OR!
Next thing you know Ray will come along and remind us all how the OR in DataStage isn't weaker than the AND and doesn't really need protecting after all. Instead, we need to use them to enforce the desired evaluation order in the expressions. Something like that, anyways.![Wink :wink:](./images/smilies/icon_wink.gif)
![Laughing :lol:](./images/smilies/icon_lol.gif)
Next thing you know Ray will come along and remind us all how the OR in DataStage isn't weaker than the AND and doesn't really need protecting after all. Instead, we need to use them to enforce the desired evaluation order in the expressions. Something like that, anyways.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The first schtick was just in reference to this statement of yours:
The rest is in reference to this (somewhat) recent post where Ray explained that DataStage BASIC treats ANDs and ORs equally, and precedence is evaluated in a strictly left-to-right order - unless parens come into the game, of course.
Make more sense now?![Wink :wink:](./images/smilies/icon_wink.gif)
Emphasis added by yours truly.kduke wrote:Is almost always true because the () are in the wrong place. The () should always protect the or like:
The rest is in reference to this (somewhat) recent post where Ray explained that DataStage BASIC treats ANDs and ORs equally, and precedence is evaluated in a strictly left-to-right order - unless parens come into the game, of course.
Make more sense now?
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Well he was just wrong. PROTECT THE OR's. Some have sense and then some have common sense and then some ain't got none at all.
If you protect the OR's with () then who cares what langauge you use or what precedence is involved. I try to keep it simple by not having to worry about these things. Ray has too much detail stored in his brain. Life is too short to carry all this detail around with you.
Geez, Craig I didn't think you listened to the dark side.
Up all night baby sitting some fragile process left behind by some great PLSQL developer. Took months to write and still needs people to watch it. Well this is going to end soon. We may have to teach DataStage or PLSQL to baby sit. I am too old for this sith.
If you protect the OR's with () then who cares what langauge you use or what precedence is involved. I try to keep it simple by not having to worry about these things. Ray has too much detail stored in his brain. Life is too short to carry all this detail around with you.
Geez, Craig I didn't think you listened to the dark side.
Up all night baby sitting some fragile process left behind by some great PLSQL developer. Took months to write and still needs people to watch it. Well this is going to end soon. We may have to teach DataStage or PLSQL to baby sit. I am too old for this sith.
Mamu Kim
Listen to? Sure, you betcha. Give myself completely over to the dark side? Nah.
I think Ray was just getting deep in the weeds on that one, pointing out the facts as to how expressions are resolved and not advocating non-use unless required. I agree with you - and I'm sure Ray does too - use the parens to both enforce how you want it resolved, or just to make it easier to understand what is going on, and it won't matter the language or whatever natural precedence is involved.
Hopefully, somewhere in all of this, I hope the OP got their problem figured out.
![Wink :wink:](./images/smilies/icon_wink.gif)
I think Ray was just getting deep in the weeds on that one, pointing out the facts as to how expressions are resolved and not advocating non-use unless required. I agree with you - and I'm sure Ray does too - use the parens to both enforce how you want it resolved, or just to make it easier to understand what is going on, and it won't matter the language or whatever natural precedence is involved.
Hopefully, somewhere in all of this, I hope the OP got their problem figured out.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers