Doubt in Constraint

somu_june · Post by **somu_june** » Tue Mar 07, 2006 5:55 pm

Hi,

I have an requirement like this. I have a columm in transformer called EconomicDevalution with DEC(7,4).The data is comming to transformer from xml and Iam loading to a DB2 table.I have to check if that Economic Devalution contains DEC(7,4) length. If it doesnot I have to load in a file. When I give in constraint I can give at as Link.EconomicDevalution but not as Link.EconomicDevalution DEC(7,4) can I give like this to achieve my target . Please correct me if Iam wrong.And also help me how to achieve this.

Thanks,
Somaraju

rasi · Post by **rasi** » Tue Mar 07, 2006 8:00 pm

Somu

You can use IsValid function to find out whether it is decimal or not.

somu_june · Post by **somu_june** » Tue Mar 07, 2006 9:18 pm

Hi Rasi,

Can you brief me how to use isvalid function .Thanks for reply,

Thanks,
Somaraju

rasi · Post by **rasi** » Tue Mar 07, 2006 10:26 pm

Somu

Refer Parallel Job Developer Guide under Function(Type Conversion Functions)

Thanks

somu_june · Post by **somu_june** » Thu Mar 09, 2006 3:57 pm

Hi Rasi,

I have seen isvalid function .It was given isvalid(%typestring%,%Valuestring%). please correct me if iam wrong isvalid(decimal,7,4).but before inserting in to a table I have to check ex : contract type is decimal (7,4) if it doesnot have decimal(7,4) I have to send to a reject link . How to check with isvalid function in transformer please help me

Thanks
Somaraju

ameyvaidya · Post by **ameyvaidya** » Thu Mar 09, 2006 10:33 pm

What we did to perform this check was to read it as a varchar; use the num() function to see if it is a valid number and then convert it to the required decimal format.
If not valid, the varchar can be written to reject link.

Just one thing.. Watch out for RCP issues!

rasi · Post by **rasi** » Thu Mar 09, 2006 11:04 pm

Somu

The IsValid function will verify whether it is of valid datatype decimal or integer but it won't check to see whether you got decimal(7,4) or not. If you want to do that then convert the decimal in String and separate the scale and verify.

somu_june · Post by **somu_june** » Thu Mar 09, 2006 11:27 pm

Hi ameyvaidya,

Please can you tell me with an example how you people achieved.

Thanks,
Somaraju

ameyvaidya · Post by **ameyvaidya** » Thu Mar 09, 2006 11:47 pm

We have worked this with Sequential Files.

Actual Input Field: Field1[decimal(7,4) Nullable]

Read it in as : Field1[varchar(9) Nullable] (Check the XML File in a text editor to get the actual length of this field. Usually add 2(1 for a leading space and 1 for the decimal point)).

In a transformer Stage Variable:

SvCheckNumField1<-If Num(Trim(NullToEmpty(In.Field1))) then @TRUE else @FALSE

For the Output:
RCP Off for this Link
Constraint SvCheckNumField1
Derivations:
Field1[Decimal(7,4) Nullable]<-StringToDecimal(IN.Field1)

For the Reject:
RCP Off for this Link
Constraint not(SvCheckNumField1)
Derivations:
Field1[Varchar(9) Nullable]<-IN.Field1

Thats it.
Along with num, if you want to do additional validations about the number of decimals, etc, it can be done using additional validations either in the Stage Variable or in additional stage variables (depending on the level of detail of your error reporting requirements).

kumar_s · Post by **kumar_s** » Sat Mar 11, 2006 7:56 am

HI Somu,
May I know what is that you want to check?
Is that you want to check whether it is a decimal or whether it compatable to the given layout or whether it matches to 7,4 (filled with all character?)
If you want to check whether it is decimal you can use IsValid in the constataint as mentioned.
No need to bother about layout compatability, input stage will take of it.
If you want to check if it has all 7,4 with numbers, check whether the length of the value after converting to string and after striping the leading 0's is equal to 7.

somu_june · Post by **somu_june** » Sat Mar 11, 2006 5:27 pm

Hi kumar,

Nice to see your post. Actually my requirement is if economic devaluation is not compatible with Dec(7,4) I must raise an error. I have a columm with name EC dec(7,4) in db2 . If I am getting from input more than dec(7,4) and when I tried to load to db2 I will get an error in log but I want to send that error to a file with the incomming data that is failed.Is there a way to load the record in a file that is not loaded in db2 to a reject link

Thanks,
Somaraju.

kumar_s · Post by **kumar_s** » Sat Mar 11, 2006 10:53 pm

So its data type compatability.
Say if your file is tab delimited, and if the input is 8,4. The input stage you use to read the file will reject the record with an import warning thrown.
If you need to raise an error based on this, it would be easy to maintain a seperate job to import the input data into datastage (let the output of this job be dataset for performance concern). Check for rejection of records in the job with the warning related to this field and particularlay the compatability. you can use LinkInfo or dsjob -longdetail for that purpose. Based on this you can raise error or load into table.