How to find the length for decimal field

[somu_june]

Hi,

I have a server job. I am having a field "Price" which has decimal (15,2) length and I want to send the records which has decimal(12,2) length to one file and remaining records which is not having length decimal (12,2) to the other file or link.

Can some one help in solving my problem.



Thanks,
Somaraju

[DSguru2B]

Use the Matches operator. Something like

Code: Select all

if in.Col Matches "12N.2N" then "A" else if in.Col Matches "15N.2N" then "B" else "C"

Do the above in a stage variable. For Decimal(12,2) the stage variable will evalute to A. Similarly for Decimal(15,2) it will evalute to "B", for everything else it will be "C".
Use A, B and C in the constraint.

[somu_june]

Hi Dsguru,

I tried with the option matches that you have mentioned. But Iam not able to filter any records with decimal length(12,2) .

In Stage variable I mentioned Stage 1 as variable and in Derivation I mentioned

if Frm_Sort.Price Matches "12N.2N" then "A" else "B"


And in Constraint I mentioned Trim(inColumn.Price) ='A' and the other link Constraint as Trim(inColumn.Price) ='B'

But no records are going to these links


Thanks,
SomaRaju

[ray.wurlod]

Try the IsValid() function I posted here some time back.

[DSguru2B]

Is the name of your stage variable inColumn.Price ? I doubt it. Say your stage variable is called StgVar, then the constraint for Decimal(12,2) should be

Code: Select all

StgVar = "A"

[satya1]

We Need to load TERADATA table from a stack of files having the same File Layout in a folder (EBCDIC FORMAT) in WIndows Env. Would anyone give us any suggestions ??

Thanks

[somu_june]

Hi Ray,


If I use a parallel job and IsValid() function . Can you please tell me what arguments do I need to pass in IsValid() function. I tried like this IsValid("decimal", Incolumn.price) but every record is going to that link . I want to filter records whose length is (12,2) to a file .



Thanks,
somaRaju

[somu_june]

Hi DSguru,


I tried with Stage Variable as you mentioned. I gave stage Variable name as

StagVar and in derivation I mentioned as

If Frm_Sort.Price Matches "12N.2N" then "A" else "B"

and in Constraints I mentioned as you said

StgVar = "A" and StgVar = "B"

But every record is going to the StgVar = "B" and not to to StagVar = "A" link


Thanks for helping me ,

SomaRaju

[DSguru2B]

Ok tell me this. For a Decimal value 12.33 coming in, how does it show in the database/dataset? Does it show 12.33 or 000000000012.33 for (12,2)?

You can also try with lenth function.
Len(in.Link) = 14 for (12,2) and
Len(in.Link) = 17 for (15,2).

[somu_june]

Hi Dsguru,

In sequential file Iam seeing the decimal value as 1234567891.00 for
decimal (12,2) and for Decimal (15,2) Iam seeing as 1234567891234.00 and I tried with Length function but it is giving length to all the fields as 15 whether it is(12,2) or (15,2). I mentioned in input Sequential File a decimal (15,2) and in target Sequential file I created field Length and defined as integer (10) to find the length of each field but all fields are having the same length 15 .


Thanks,
Somaraju.

[DSguru2B]

My bad. The matches argument is wrong. Please try again with the following.
Try both:

Code: Select all

If Frm_Sort.Price Matches "10N.2N" then "A" else "B" 

OR

Code: Select all

If Frm_Sort.Price Matches " 10N.2N" then "A" else "B" 

[somu_june]

Hi DSguru,


I tried with the code you have mentioned. But no luck, all records are going to StagVar = "B" link and no records or 0 records are going through the StagVar = "A" constraint link




Thanks,
SomaRaju.

[ray.wurlod]

There is, on this forum, an IsValid() function for server jobs. It is well documented.
Please post parallel job questions on the parallel forum.

[chulett]

And for goodness sakes, if you are going to keep going back and forth on this, post some actual data samples you are seeing. Made up stuff like "I am seeing 1234567891234.00" doesn't help anyone help properly.

[DSguru2B]

Also, specify the decimal as varchar and then take the length. I forgot to mention this when I suggested to go with the Len() function.