Implementation of the While Loop logic in the job

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
kirannumb
Participant
Posts: 51
Joined: Fri Jun 19, 2009 2:23 pm

Implementation of the While Loop logic in the job

Post by kirannumb »

Hello Gurus,

How can we implement "while loop" logic in the job ?

I am familiar with implementing looping in transformer but not quite sure how to go about implementing the looping for the below while loop inside the stored procedure:

Code: Select all

DECLARE @pnum int
SELECT @pnum = 0
DECLARE @vendor_id varchar(2000) 
DECLARE @vendor_pnum varchar(2000) 
DECLARE @vendor_list varchar(2000) 
DECLARE @vendor_multi_list varchar(2000) 
DECLARE @vendor_cost varchar(2000) 
DECLARE @vendor_id_temp varchar(2000) 

DECLARE @PART_NUM INT
DECLARE @VENDOR_ID VARCHAR(2000)
DECLARE @VENDOR_PART_NUMBER VARCHAR(2000)
DECLARE @LIST VARCHAR(2000)
DECLARE @COST VARCHAR(2000)

DECLARE cPARTS_MASTER INSENSITIVE CURSOR 
FOR
	SELECT     PART_NUMBER, VENDOR_ID, VENDOR_PART_NUMBER, CAST(CONVERT(NUMERIC (19,0), LIST *100) AS VARCHAR(200)) AS LIST, CAST(CONVERT(NUMERIC(19,0),COST*100) AS VARCHAR(200)) AS COST
	FROM         MASTER_SUPPLIER
	ORDER BY PART_NUMBER, VENDOR_ID
FOR READ ONLY

OPEN cPARTS_MASTER
FETCH FROM cPARTS_MASTER INTO @PART_NUM, @VENDOR_ID, @VENDOR_PART_NUMBER, @LIST, @COST
	SELECT @pnum = @PART_NUM
WHILE 
BEGIN
	IF @pnum = @PART_NUM
		BEGIN
		IF @vendor_id = '' 
		SELECT @vendor_id = @VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = @LIST, @vendor_cost = @COST, @vendor_multi_list = @LIST
		ELSE
			BEGIN
			IF @VENDOR_ID = '500004'
			SELECT @vendor_id = @vendor_id+CHAR(253)+@VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@vendor_pnum))+CHAR(253)+RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = @LIST,      
                                                                       @vendor_cost = @vendor_cost+CHAR(253)+@COST, @vendor_multi_list = @vendor_multi_list+CHAR(253)+@LIST
			ELSE SELECT @vendor_id = @vendor_id+CHAR(253)+@VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@vendor_pnum))+CHAR(253)+RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = 0, 
                                                                                 @vendor_cost = @vendor_cost+CHAR(253)+@COST, @vendor_multi_list = @vendor_multi_list+CHAR(253)+@LIST
			END
                           FETCH NEXT FROM cPARTS_MASTER INTO @PART_NUM, @VENDOR_ID, @VENDOR_PART_NUMBER, @LIST, @COST
		END
	ELSE
		BEGIN
		INSERT INTO dbo.SUPPLIER_TEMP
        	              (PART_NUMBER, VENDOR_ID, VENDOR_PART_NUMBER, LIST, COST, MULTI_LIST)
		VALUES     (@pnum, @vendor_id, @vendor_pnum, @vendor_list, @vendor_cost, @vendor_multi_list)
		SELECT @pnum = @PART_NUM
		SELECT @vendor_id = ''
		END
END


To begin with I have sourced the data from the initial select statement and stored the @pnum in a stage variable and used it in a stage variable to check for the PART_NUM but was not quite sure in how to use a loop variable from here on to implement the rest of the logic.

Any input is appreciated.

Thanks !!
priyadarshikunal
Premium Member
Premium Member
Posts: 1735
Joined: Thu Mar 01, 2007 5:44 am
Location: Troy, MI

Post by priyadarshikunal »

does it really transform in to a while loop in datastage or just If else and assignment based on conditions. In my first look its just doing the same if else logic for all records it fetched in the array.
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. :wink:
Post Reply