DSXchange

Hi,
I have one quick question, I want to genarate sequence of numbers for example PART1,PART2.... USING key genarate next value.

How can I add the the string PART , to the numaric number .

Please suggest me, Thanks in advance...

we are using 7.1 server

Regards
Deva

Just concatenate it using colon (:) in the derivation.

concatenate them as below in the transformer .

"PART":KeyMgtGetNextValue('ID').

To Add or To Concatenate .... That is the Question!!! :D

"PART" is non-numeric. Attempting to add will generate "non-numeric where numeric required, zero used" error. Concatenate it is.

paddu wrote:concatenate them as below in the transformer .

"PART":KeyMgtGetNextValue('ID').

Hi,
Thanks for your reply. I need some other information on this. If I want query on this key column, how can I do that?

let say If I need rage of values between PART10 AND PART30, how to get those rage of values, because of the concadination result is stored as string.

can you advise on this please. Thanks in advance..

In your sql, do a substring of everything after the first four characters, type cast to integer and then viola, you have a number now.

Deva,

I truly did not follow your requirement.

Where did you store the result of the concatenation at first place(PART1,PART2....) ???


Is this result set source to another logic ? Where are you trying to achieve range of values between PART10 and PART30.

Please give some details of how and what should your result set look like.

If you want certain range of values only to be loaded in your target or fetch from the source , you can use tranformer constraint to restrict the data or simple use oracle between function in the oracle stage.


As Dsguru2B suggested you can substring the value and give compare condition to get specific range of values.

Hi,
Thanks for your reply, My question is, I need to genarate sequence of numbers. The formate of the number is 'PART1'.
The columbn datatype is varchar.
I can concadinate the "PART:KEY GENARATE NEXT VALUE"
If I did as above , it is loaded as string. If I want query on this column I can't.

My requirement is I want query this column result as like numaric.

Thanks In advance...

paddu wrote:Deva,

I truly did not follow your requirement.

Where did you store the result of the concatenation at first place(PART1,PART2....) ???


Is this result set source to another logic ? Where are you trying to achieve range of values between PART10 and PART30.

Please give some details of how and what should your result set look like.

If you want certain range of values only to be loaded in your target or fetch from the source , you can use tranformer constraint to restrict the data or simple use oracle between function in the oracle stage.


As Dsguru2B suggested you can substring the value and give compare condition to get specific range of values.

How? 'PART1' is neither numeric nor a number but rather alphanumeric and thus a string it must be.

Not possible. You have to choose.
If you want it as PART1, PART2 ...., then it needs to be stored as a varchar and you need to substring the numeric part for numeric range queries.
Or create another column in your target, that holds just the numeric part.
You cannot get into water and demand not to get wet.

Deva,

I agree with DSguru2B and chulett.