IsNull( Trim( ....) ) in Constraint not able to compile

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
smeliot
Participant
Posts: 19
Joined: Sun Mar 18, 2007 7:31 pm

IsNull( Trim( ....) ) in Constraint not able to compile

Post by smeliot »

Very simple job. Compiles and runs with IsNull( ...) in constraint (although doesn't give expected results).

When we add the Trim inside the IsNull function, we get the followng error when compiling:
##W IIS-DSEE-TOSH-00049 12:26:13(006) <main_program> Parameter specified but not used in flow: DSPXWorkingDir (DY005_MRG_ECC_CIS.Transformer_50)


Have changed to "IsNull( ...) Or ...=' '" but am wondering why IsNull( Trim( ...) ) isn't working. And whether or not we should go through the hassle of reporting the problem.

Any ideas?

Thanks.
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Hi Welcome Aboard :D

What is the exact derivation given with Trim and isNull function. Is it just a field name? Doese the derivation gives the blue color after the edit or will it still give you red color for wrong derivation?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
smeliot
Participant
Posts: 19
Joined: Sun Mar 18, 2007 7:31 pm

Post by smeliot »

kumar_s wrote:What is the exact derivation given with Trim and isNull function. Is it just a field name? Doese the derivation gives the blue color after the edit or will it still give you red color for wrong derivation?
IsNull( Trim( ...) ) where ... = link.column

No red - which is why we were compiling.
kumar_s
Charter Member
Charter Member
Posts: 5245
Joined: Thu Jun 16, 2005 11:00 pm

Post by kumar_s »

Other than the given warning, what other useful warning you get along with this?
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
smeliot
Participant
Posts: 19
Joined: Sun Mar 18, 2007 7:31 pm

Post by smeliot »

kumar_s wrote:Other than the given warning, what other useful warning you get along with this? ...
Not a thing, sorry.
wesd
Participant
Posts: 22
Joined: Mon Aug 16, 2004 8:56 pm

Post by wesd »

Maybe because you can't Trim a null character? Did you try using TRIM(IsNull(in.ABC,0))?
Wes Dumey
Senior Consultant
Data Warehouse Projects
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

IsNull() returns a TinyInt, and Trim() can only be applied to a string. This is why it won't compile.

Trim() won't like NULL so you have to convert this null before the Trim() function is applied. IsNull() does not convert; it only reports. You need something such as

Code: Select all

Trim(If IsNull(InLink.TheValue) Then "0" Else InLink.TheValue)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply