Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: 2nd Column values keep getting repeated

    Hello experts,

    Iím trying to write a code though which I need to add some data in one of my table. At the moment Iíve defined logic and code works fine and insert data in table. However what Iíve observed is when I try to set more then one variable value (after making decision making with if conditions Iím setting values for @project_no and @job variables) the only first variable value gets set and otherís the only last value gets populated in all the rows. For instance if Iím setting @project_no value first in code then it works absolutely fine but @job value doesnít get fill in rows accordingly and only last value gets fill in all the columns. To understand it better here is the output

    1 ME90 Manager 2009-11-03 10:05:17.167
    2 ME91 Manager 2009-11-03 10:05:17.167
    3 ME92 Manager 2009-11-03 10:05:17.167
    4 ME93 Manager 2009-11-03 10:05:17.167
    5 ME94 Manager 2009-11-03 10:05:17.167
    1 ME90 Manager 2009-11-03 10:05:17.167
    2 ME91 Manager 2009-11-03 10:05:17.167
    3 ME92 Manager 2009-11-03 10:05:17.167
    4 ME93 Manager 2009-11-03 10:05:17.167
    5 ME94 Manager 2009-11-03 10:05:17.167

    And to reference here is code.

    declare @i integer, @j integer
    declare @project_no char(4)
    declare @job char(15)
    declare @enter_date datetime
    set @i = 1
    set @j = 1
    set @job = 'Analyst'
    set @enter_date = getdate()
    while @i <= 2
    begin
    while @j <= 5
    begin
    if (@j = 1)
    set @project_no = 'ME90'
    set @job = 'Analyst'
    if (@j = 2)
    set @project_no = 'ME91'
    set @job = 'Junior Cons'
    if (@j = 3)
    set @project_no = 'ME92'
    set @job = 'Technical Cons'
    if (@j = 4)
    set @project_no = 'ME93'
    set @job = 'Sr.Tech Cons'
    if (@j = 5)
    set @project_no = 'ME94'
    set @job = 'Manager'

    insert into works_on
    values(@j, @project_no, @job, @enter_date )
    set @j = @j+1
    end
    set @i = @i+1
    set @j = 1
    end
    GO

    Does anybody know what can be the reason for it? What is my mistake?

    Thank

  2. #2
    Join Date
    Oct 2009
    Posts
    28
    Nevermind folk. I get it. I should add put my code after if in begin and end to make every line get executed like this.

    If (@j =1)
    beging
    set @project_no = 'ME90'
    set @job = 'Analyst'
    end

    Not like as follows

    If (@j =1)
    set @project_no = 'ME90'
    set @job = 'Analyst'

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    jus guessing. try this..

    Quote Originally Posted by asyed01 View Post


    declare @i integer, @j integer
    declare @project_no char(4)
    declare @job char(15)
    declare @enter_date datetime
    set @i = 1
    set @j = 1
    set @job = 'Analyst'
    --set @enter_date = getdate()
    while @i <= 2
    begin
    while @j <= 5
    begin
    if (@j = 1)
    set @project_no = 'ME90'
    set @job = 'Analyst'
    if (@j = 2)
    set @project_no = 'ME91'
    set @job = 'Junior Cons'
    if (@j = 3)
    set @project_no = 'ME92'
    set @job = 'Technical Cons'
    if (@j = 4)
    set @project_no = 'ME93'
    set @job = 'Sr.Tech Cons'
    if (@j = 5)
    set @project_no = 'ME94'
    set @job = 'Manager'

    insert into works_on
    values(@j, @project_no, @job, getdate())
    set @j = @j+1
    end
    set @i = @i+1
    set @j = 1
    end
    GO
    ďIf one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.Ē Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by asyed01 View Post
    And to reference here is code.
    SOME people might call it that

    What are you actually trying to do? I poked my eyes out so I can't see your code right now
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    btw

    THIS is what your code really looks like...notice the SET's outside your nested IF's

    Code:
    while @i <= 2
      begin
    	while @j <= 5
    	  begin
    		if (@j = 1)
    			set @project_no = 'ME90'
    		set @job = 'Analyst'
    		if (@j = 2)
    			set @project_no = 'ME91'
    		set @job = 'Junior Cons'
    		if (@j = 3)
    			set @project_no = 'ME92'
    		set @job = 'Technical Cons'
    		if (@j = 4)
    			set @project_no = 'ME93'
    		set @job = 'Sr.Tech Cons'
    		if (@j = 5)
    			set @project_no = 'ME94'
    		set @job = 'Manager'
    
    		insert into works_on
    		values(@j, @project_no, @job, @enter_date )
    
    		set @j = @j+1
    	  end
    	set @i = @i+1
    	set @j = 1
      end
    GO
    Can that be right? Notice how @job will always equal 'Mangler'....which is kinda of what I work with here...everyone thinks they are a mangler

    Here's another idea...you have sample data...which is good (I think)

    What should the final Resultset look like?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    AHHHHHHHHHHHHH

    I see said the Blind Dude

    Try this

    Code:
    WHILE @i <= 2
      BEGIN
    	WHILE @j <= 5
    	  BEGIN
    		INSERT INTO works_on  -- (Supply the column list ALWAYS)
    		SELECT @j 
    			 , CASE 
    					WHEN @j = 1 THEN THEN 'ME90'
    					WHEN @j = 2 THEN THEN 'ME91'
    					WHEN @j = 3 THEN THEN 'ME92'
    					WHEN @j = 4 THEN THEN 'ME93'
    					WHEN @j = 5 THEN THEN 'ME94'
    									 ELSE 'XXXX'
    				END			 
    			, CASE 
    					WHEN @j = 1 THEN THEN 'Analyst'
    					WHEN @j = 2 THEN THEN 'Junior Cons'
    					WHEN @j = 3 THEN THEN 'Technical Cons'
    					WHEN @j = 4 THEN THEN 'Sr.Tech Cons'
    					WHEN @j = 5 THEN THEN 'Mangler'
    									 ELSE 'SQLTeam Scrub'
    				END
    			, GetDate()
    
    		SET @j = @j+1
    	  END
    	SELECT @i = @i + 1, @j = 1
      END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I like this even better

    Code:
    DECLARE @n table (j int)
    DECLARE @works_on table (j int, project_no varchar(20), job varchar(20), enter_date datetime)
    
    		INSERT INTO @n(j) 
    		SELECT 1 UNION ALL
    		SELECT 2 UNION ALL
    		SELECT 3 UNION ALL
    		SELECT 4 UNION ALL
    		SELECT 5
    
    		INSERT INTO @works_on (j, project_no, job, enter_date)
    		SELECT j 
    			 , CASE 
    					WHEN j = 1 THEN 'ME90'
    					WHEN j = 2 THEN 'ME91'
    					WHEN j = 3 THEN 'ME92'
    					WHEN j = 4 THEN 'ME93'
    					WHEN j = 5 THEN 'ME94'
    							   ELSE 'XXXX'
    				END			 
    			, CASE 
    					WHEN j = 1 THEN 'Analyst'
    					WHEN j = 2 THEN 'Junior Cons'
    					WHEN j = 3 THEN 'Technical Cons'
    					WHEN j = 4 THEN 'Sr.Tech Cons'
    					WHEN j = 5 THEN 'Mangler'
    							   ELSE 'SQLTeam Scrub'
    				END
    			, GetDate()
    		FROM (SELECT j FROM @n UNION ALL SELECT j FROM @n)AS XXX
    
    SELECT * FROM @works_on
    GO
    Basically 2 inserts and no looping
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •