is there any wrong in routine

[harithay]

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

[zam62]

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.

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

[kduke]

I would use () to isolate the OR in the IF statement.

[harithay]

Hi

thanks for ur reply

if i use () to isolate the OR in the IF statement , i am getting syntax error.


my requirement is to caliculate change in amount for the same fund having differn filing dates if differnc between filing dates is >=365 days.

how to get this one .

[chulett]

Show us your syntax with the parens added.

[harithay]

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

[chulett]

Since we don't know for sure what you need, it either needs to look like this:

Code: Select all

if (Fundname2 <> Fundname1 or Fundname1='') and DateGenericDateDiff(Date2, Date1) < 365 then 0 else TNAamount2-TNAamount1

or:

Code: Select all

if Fundname2 <> Fundname1 or (Fundname1='' and DateGenericDateDiff(Date2, Date1) < 365) then 0 else TNAamount2-TNAamount1

One of the two should be what you had in mind.

[ray.wurlod]

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.

[kduke]

Code: Select all

if (Fundname2 <> Fundname1) or (Fundname1='' ) and DateGenericDateDiff(Date2, Date1) < 365 then 0 else 1

Is almost always true because the () are in the wrong place. The () should always protect the or like:

Code: Select all

if (a<>b or b=1) and c<b then 0 else 1

or

Code: Select all

if a<>b or (b=1 and c<b) then 0 else 1

but you did:

Code: Select all

if (a<>b or b=1 and c) < b then 0 else 1

So everything between () will be treated as a logical and return 0 for false and 1 for true. Therefore either is less than 365.

[/code]

[chulett]

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.

[kduke]

What?

[chulett]

Which part did I lose you on, Kim?

[chulett]

The first schtick was just in reference to this statement of yours:

Is almost always true because the () are in the wrong place. The () should always protect the or like:

Emphasis added by yours truly.

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?

[kduke]

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.

[chulett]

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.