Need help in a logic

[muralisankarr]

I have an interesting problem to solve using datastage. I'm not able to derive a solution. Please help if you can

The table in interest has two attribute Item and Part. I need to form a group records based on deep-derive part key combination. For example

Item Part
I1 P1
I1 P2
I1 P3
I2 P4
I3 P3
I3 P5
I4 P5
I5 P3
I6 P2
I7 P6

I need to group all part based on items and I need to find out the other items parts that can be related through the part of the original item. It is a recursive finding. The group will be

Group Item Parts
G1 I1,I3,I4,I5,I6 P1,P2,P3,P3,P5,P5,P3,P2
G2 I2 P4
G3 I7 P6

Group 1 was formed as below

The Item I1 has three parts P1,P2,P3. Using these parts we find out the P3 was exists in other items I3 and I5 and P2 in I6. After these the other parts in item I3 ie., P5 was used to find out the other items ie., I4.

Please help and many thanks
MSR

[ArndW]

This type of recursive programming is not done easily directly in DataStage PX. I'd use a server job or stage as the BASIC language supports recursion or, if the data comes from a database, use SQL to solve the lookup recursion.

[dr.murthy]

you can handle these kind of things by wrinting user defined sequel in your source stage if you are using oracle is source database.
oracle 10g supports to write a sequels in order to get data in hierarchical order by using sys_connect_by_path, level key words.
which might helps you

[muralisankarr]

This type of recursive programming is not done easily directly in DataStage PX. I'd use a server job or stage as the BASIC language supports recursion or, if the data comes from a database, use SQL to solve the lookup recursion.

Thanks Arndw. Can you please give me some idea on how to solve it with hash file or server jobs? I tried using PL/SQL to solve the issue. But end up in crossing the maximum open cusrsor limits on recurssion

Many Thanks
MSR

[ArndW]

I'm surprised the PL/SQL reached cursor limits - is the source data that big and has that many levels?

Using stage variables and/or routines in server would necessitate that the data fit into virtual memory, is that the case?

[muralisankarr]

I'm surprised the PL/SQL reached cursor limits - is the source data that big and has that many levels?

Using stage variables and/or routines in server would necessitate that the data fit into virtual memory, is that the case?

The source data is 0.5 million nad it has more than 2000 levels. (or may be we are new to PL/SQL and we haven't handled the cursor efficiently. Please have a look at the code below

Code: Select all

CREATE OR REPLACE FUNCTION Get_Group(pITEM IN VARCHAR2) 
RETURN VARCHAR2
IS
	CURSOR item_cur (v_ITEM VARCHAR2)
	IS
		SELECT ITEM,PART
		FROM TEST666
		WHERE ITEM = v_ITEM;
	v_Output VARCHAR2(255);
BEGIN
	FOR item_rec IN item_cur(pITEM) LOOP
		v_Output := v_Output || '|' || item_rec.PART ;
    FOR part_rec IN (SELECT ITEM FROM TEST666 WHERE ITEM <> pITEM AND PART = item_rec.PART) LOOP
        v_Output := v_Output || '^' ||Get_Group(part_rec.ITEM);
    END LOOP;
	END LOOP;
	RETURN v_Output;
END;

[ArndW]

I've thought a bit about how I'd do this in server and with a function and it is a bit more involved than I originally thought and, unfortunately, I don't have the time right now to write a routine to do this.

I'm not an expert on SQL as what you posted looks correct, but could imagine that a good DBA might be able to figure out a solution to the cursor problem.

[chulett]

Use two cursors and ensure the inner one gets properly released each time.

[muralisankarr]

I got solution in SQL from oracle forum. Please find the link below

.http://forums.oracle.com/forums/thread. ... 1&tstart=0

But the recurssion goes up to 54000 records! The SQL was taking infinite time to produce the output. We don't have a SQL DBA.So I handled it using PL/SQL.

Code: Select all

CREATE OR REPLACE PROCEDURE PRTGRP_737_XXX_E01H IS
  CURSOR itmprt_grp IS
    SELECT PARTNUMBER, ITEM FROM ITEM_PART_GROUP_737_XXX_E01H WHERE GROUP_CODE IS NULL;
  ONSid     NUMERIC(8, 0) := 0;
  ItmGrpCde NUMERIC(8, 0) := 0;
  PrtGrpCde NUMERIC(8, 0) := 0;
  SmlGrp    NUMERIC(8, 0) := 0;
  GtrGrp    NUMERIC(8, 0) := 0;
BEGIN
  FOR prt_itmprt IN itmprt_grp LOOP
    SELECT min(GROUP_CODE)
      INTO ItmGrpCde
      FROM ITEM_PART_GROUP_737_XXX_E01H
     WHERE ITEM = prt_itmprt.ITEM;
    SELECT min(GROUP_CODE)
      INTO PrtGrpCde
      FROM ITEM_PART_GROUP_737_XXX_E01H
     WHERE PARTNUMBER = prt_itmprt.PARTNUMBER;
    IF ItmGrpCde IS NULL AND PrtGrpCde IS NULL THEN
      ONSid := ONSid + 1;
      UPDATE ITEM_PART_GROUP_737_XXX_E01H
         SET GROUP_CODE = ONSid
       WHERE ITEM = prt_itmprt.ITEM
         AND PARTNUMBER = prt_itmprt.PARTNUMBER;
    ELSIF ItmGrpCde IS NOT NULL AND PrtGrpCde IS NULL THEN
      UPDATE ITEM_PART_GROUP_737_XXX_E01H
         SET GROUP_CODE = ItmGrpCde
       WHERE ITEM = prt_itmprt.ITEM
         AND PARTNUMBER = prt_itmprt.PARTNUMBER;
    ELSIF ItmGrpCde IS NULL AND PrtGrpCde IS NOT NULL THEN
      UPDATE ITEM_PART_GROUP_737_XXX_E01H
         SET GROUP_CODE = PrtGrpCde
       WHERE ITEM = prt_itmprt.ITEM
         AND PARTNUMBER = prt_itmprt.PARTNUMBER;
    ELSE
      IF ItmGrpCde < PrtGrpCde THEN
        SmlGrp := ItmGrpCde;
        GtrGrp := PrtGrpCde;
      ELSIF ItmGrpCde > PrtGrpCde THEN
        SmlGrp := PrtGrpCde;
        GtrGrp := ItmGrpCde;
      ELSE
        SmlGrp := ItmGrpCde;
        GtrGrp := ItmGrpCde;
      END IF;
      UPDATE ITEM_PART_GROUP_737_XXX_E01H
         SET GROUP_CODE = SmlGrp
       WHERE PARTNUMBER = prt_itmprt.PARTNUMBER;
      UPDATE ITEM_PART_GROUP_737_XXX_E01H
         SET GROUP_CODE = SmlGrp
       WHERE GROUP_CODE = GtrGrp;
    END IF;
    IF Mod(itmprt_grp%ROWCOUNT, 10000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

Many thanks for all your valuble inputs