problem with oracle db sequencer

[karteek]

Hi Everyone,
How to generate Negative values in Datastage using oracle DB sequence.
My sequencer:

CREATE SEQUENCE FAKE_ID_SEQ_1
START WITH -1 INCREMENT BY -1

Error: APT_CombinedOperatorController,2: Sequence FAKE_ID_SEQ_1 returned invalid next value: -1.

i can write insert statement and i can give this db sequence
i.e., insert into table_name(column) values(FAKE_ID_SEQ_1.nextval)

but i need to use only surrogate key stage to achieve this.

Please advice on this.

[ray.wurlod]

Surrogate Key Generator stage wants an unsigned long integer. This is probably why it's complaining about -1 being invalid.

[karteek]

Hi ,

Thanks for replay, i used datatype : BIGINT,Unsigned still same error.

does Surrogate Key Generator stage will generate Negative values.

i tried with other datatypes same error.

Thanks,
Karteek M

[chulett]

Generate it 'like normal' and then multiply it by -1.

[karteek]

Thanks craig ,

as per my understanding Surrogate Key Generator stage will Not generate Negative values,

Hence i have to change oracle sequencer it will start with 1 increment by 1, before inserting i will multiply with -1 and also i cannot alter this sequencer.
But if any application uses this sequencer it will generate positive values this is a drawback,
if Surrogate Key Generator stage generates Negative values it will be more helpful.

Thanks,
Karteek M

[ray.wurlod]

It simply cannot generate signed numbers. Its output is defined to be an unsigned long integer (which most of the rest of the world uses for surrogate key values).

You can, of course, always access the Oracle sequence in custom SQL rather than via a Surrogate Key Generator stage.

[qt_ky]

My "database 101" understanding is that surrogate keys are supposed to be system generated, not carry any meaning, and not be visible to the user. It sounds like perhaps someone may be trying to attach meaning to particular SK values, which does not make much sense. You should be free to start the SK value at 1000, for instance, and increment every new value by 10 if your heart so desired.

[chulett]

But if any application uses this sequencer it will generate positive values this is a drawback

I was assuming this would be a dedicated sequence, something only used by the process in question. That's the only thing that makes sense to me.

As to the attaching meaning part, I agree but I mentioned a solution I've used in the past when I've needed to generate surrogate keys for use amongst keys from another system. They would supply data with keys assigned and on occasion we would need to insert new records into the mix. Using a "negative sequence" allowed ours to both stand out and not interfere with the existing records. Luckily these keys never went anywhere as only a keyless subset of the data moved on to the next step, so just helped enforce RI while they visited.

I have no idea what the OP's intentions are but just thought I'd mention where I've needed them most recently.