Assigning sequential numbers with some conditions

[vbeeram]

A configuration table SHOWING THE LIMITS WAS given ...

ITEM_TYPE- Start - End

A - 1000 - 1299-
B - 1300 - 1399-
C - 1400- 1799-
D - 1400- 1799-
E - 1800- 1999-
F - 1800- 1999-
G - 1800- 1999-
H - 1800- 1999--
1 - 2000- 1999-
2 - 2500- 1999-
3 - 2500- 1999-

BASED ON THE (ITEM_TYPE), A SEQUENTIAL NUMBER IS TO BE GENERATED IN A NEW COLUMN (ITEM_ID) STARTING FROM THE VALUE IN COLUMN (START)......

FOR EXAMPLE.....SEE THE TABLE BELOW
IF THE DATA I GET IS ONLY THE FIRST COLUMN( ITEM_TYPE)
THE RESULT MUST BE LIKE IN THE SECOND COLUMN...
THE ITEM_ID MUST BE INCREMENTED BASED ON THE (ITEM_TYPE)....





ITEM_TYPE - ITEM_ID

A -1000
B -1300
C -1400
A - 1001
F -1800
D -1401
F - 1801
1 - 2000
1 - 2001
2 - 2500
3 - 2501



I TRIED USING DIFFERENT STAGE VARIABLES, ROUTINE..FOR INCREMENTING VALUES AND CONNECTED THEM BY "IF THEN ELSE " ETC....I GET WIERD VALUES.....I SEE THAT THE VALUES ARE NOT FOLLOWING THE PREVIOUS VALUE BUT THEY GO TO THE STARTING VALUE....

CAN ANY ONE SUGGEST ME AN IDEA....
I TRIED SEARCHING THE FORUM BUT WAS NOT ABLE TO GET ANY RELATED TOPIC....

[rasi]

Hi

You need to explain about your question so that we can help you.

The result you typed for D must be 1400 since it listed first and it does make sence for Item_type A starts at 1000 and the next occurence increases to 1001 but why D and 3 is different.


ITEM_TYPE - ITEM_ID

A -1000
B -1300
C -1400
A - 1001
F -1800
D -1401
F - 1801
1 - 2000
1 - 2001
2 - 2500
3 - 2501

How many item_types you have in total is it fixed or variable?

What happenes when the increment reaches the End number?

Just to keep the balls rolling you can think something like this.

Create the following hash file in whatever way you feel it is convenient.

Item_Type, Seq_No, Item_Number
A, 1, 1000
A, 2, 1001
.............
A,100,1100
B,1,2000
B,2,2001
B,3,2002
..............
B,100,2100

Item_Type and Seq_No is the Key columns.

Use this file as lookup to join your source. And do a tranformation to increase the sequence number 1,2,3 for a Item_Type using a stage variable. Check the same item_type is coming from the source if so increase the sequence number and pass it to the stream. And based on your source item_type and generated sequence number you can join to the hash file to get the Item_Number.



Thanks
Siva

[ray.wurlod]

You've made the statement of the problem too complex.

What you need are separate sequences. You can initialize them how you will.

You can create your sequences in stage variables, or use a routine such as KeyMgtGetNextValueConcurrent from the SDK, using a separate sequence name for each ITEM_TYPE. Initializing the SDK sequences can be performed with simple SQL against the SDKSequences table.

Siva's point about hitting the end of a range is pertinent. This is best handled by prevention; creating sequences that will never overlap (perhaps with a uniqueness identifier).

[vbeeram]

Hello Friends....

Regarding Sivas's question.....I mean that if the ITEM_TYPE is either C 0r D then the sequence starts from 1400...similarly with ITEM_TYPE is either 2 or 3 then the sequence starts from 2500....

I tried using the routine KeyMgmtNextconcurrentValue()...it initialized correctly but it kept on increasing for every iteration.....

I GOT THE SOLUTION FINALLY....
I BASICALLY MISSED THAT THE STAGE VARIABLES ARE CALCULATED IN A SEQUENTIAL ORDER AND NOT CONCURRENTLY...

MY SOLUTION IS
I INITIALIZED THE STAGE VARIABLE CORESPONDING TO THE CONFIGURATIN TABLE GIVEN...

DERIVATION ------ STAGE VARIABLE

IF ITEM_TYPE = "A" THEN XA+1 ELSE XA - XA
IF ITEM_TYPE = "B" THEN XB+1 ELSE XB - XB
IF ITEM_TYPE = "C" OR ITEM_TYPE = "D" THEN XCD+1 ELSE XCD - XCD
.....SIMILARLY FOR E,F,G,H,,I,J,K,L,1,2,3...

FINALLY A STAGE VARIABLE XID
If ITEM_TYPE = "A" Then xA Else
if ITEM_TYPE = "B" then xB ELSE
if ITEM_TYPE = "C" Or ITEM_TYPE = "D" then xCD Else
if ITEM_TYPE = "E" or ITEM_TYPE = "F" or
ITEM_TYPE = "G" or ITEM_TYPE = "H" or
ITEM_TYPE = "I" or ITEM_TYPE = "J" or
ITEM_TYPE = "K" or ITEM_TYPE = "L" then xE2L else
if ITEM_TYPE = 1 then x1 else
if ITEM_TYPE = 2 Or ITEM_TYPE = 3 then x23 else 0

I ASSIGNED XID IN THE COLUMN WHICH RETURNED WITH THE CORRECT SEQUENCE......

THANX FOR UR ADVICE....

--------------------------------------------------------------------------------

A configuration table SHOWING THE LIMITS WAS given ...

ITEM_TYPE- Start - End

A - 1000 - 1299-
B - 1300 - 1399-
C - 1400- 1799-
D - 1400- 1799-
E - 1800- 1999-
F - 1800- 1999-
G - 1800- 1999-
H - 1800- 1999--
1 - 2000- 1999-
2 - 2500- 1999-
3 - 2500- 1999-

BASED ON THE (ITEM_TYPE), A SEQUENTIAL NUMBER IS TO BE GENERATED IN A NEW COLUMN (ITEM_ID) STARTING FROM THE VALUE IN COLUMN (START)......

FOR EXAMPLE.....SEE THE TABLE BELOW
IF THE DATA I GET IS ONLY THE FIRST COLUMN( ITEM_TYPE)
THE RESULT MUST BE LIKE IN THE SECOND COLUMN...
THE ITEM_ID MUST BE INCREMENTED BASED ON THE (ITEM_TYPE)....





ITEM_TYPE - ITEM_ID

A -1000
B -1300
C -1400
A - 1001
F -1800
D -1401
F - 1801
1 - 2000
1 - 2001
2 - 2500
3 - 2501



I TRIED USING DIFFERENT STAGE VARIABLES, ROUTINE..FOR INCREMENTING VALUES AND CONNECTED THEM BY "IF THEN ELSE " ETC....I GET WIERD VALUES.....I SEE THAT THE VALUES ARE NOT FOLLOWING THE PREVIOUS VALUE BUT THEY GO TO THE STARTING VALUE....

CAN ANY ONE SUGGEST ME AN IDEA....
I TRIED SEARCHING THE FORUM BUT WAS NOT ABLE TO GET ANY RELATED TOPIC....

[ray.wurlod]

But what happens when you process the 101st Type B ?