Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    53

    Unanswered: Help w/syntax select in a while loop

    I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:

    'AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
    and @start between sh_istart and sh_istop'

    Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:

    Also I'm trying really hard not to use temp tables in this example

    Result from select statement below

    sh_serial
    -----------
    53565
    53597

    sh_serial
    -----------
    53565
    53597

    sh_serial
    -----------

    sh_serial
    -----------
    53588
    53597

    Desired results:

    sh_serial
    -----------
    53588
    53597
    53565

    Syntax:

    declare @start int
    select @start = 580
    declare @stop int
    select @stop = 900

    while @start <= @stop
    begin
    select sh_serial,
    from casemas, schilin
    WHERE (schi_shser = sh_serial)
    and (schi_itemno = '004852')
    and (sh_serial <> 600000)
    and sh_serial in (53565,53588,53597)
    and sh_serial in

    (select distinct sh_serial
    from casemas, schilin
    WHERE (schi_shser = sh_serial)
    and (schi_itemno = '004852')
    and sh_serial in (53565,53588,53597)
    AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
    and @start between sh_istart and sh_istop
    group by sh_serial
    having (sum(schi_qty) + 1 < 4 ))


    select @start = @start + 1
    end



    I'd appreciate any help. Thanks!
    Last edited by awfdml; 08-31-04 at 14:38.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Insert into a table variable and select distinct from that when you leave the loop?
    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.

  3. #3
    Join Date
    Jul 2004
    Posts
    53
    cause of business logic I cannot use temp tables or tables...

    is there a way that I can put this into some sort of a derived table and then do the select distinct?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I was gonna rewrite it...but I got scared....

    SQL 2000?

    Code:
    DECLARE @start int, @stop int
    SELECT @start = 580, @stop = 900
    
    DECLARE @x TABLE(shSerial int)
    
    WHILE @start <= @stop
     BEGIN
    
    INSERT INTO @x(shSerial)
    SELECT sh_serial
      FROM casemas, schilin
     WHERE schi_shser = sh_serial
       AND schi_itemno = '004852'
       AND sh_serial <> 600000
       AND sh_serial in (53565,53588,53597) 
       AND sh_serial in ( SELECT DISTINCT sh_serial
    			FROM casemas, schilin
    		        WHERE (schi_shser = sh_serial)
    			  AND (schi_itemno = '004852')
    			  AND sh_serial in (53565,53588,53597)
    			  AND @start NOT IN ( SELECT sh_istart 
    						FROM casemas 
    					       WHERE sh_istart in (SELECT sh_istop 
    								     FROM casemas 
    								    WHERE sh_serial in (53565,53588,53597)))
      AND @start between sh_istart and sh_istop
    GROUP BY sh_serial
    HAVING (sum(schi_qty) + 1 < 4 )) 
    
    
      SELECT @start = @start + 1
      END
    
    SELECT DISTINCT shSerial  FROM @x
    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
    Jul 2004
    Posts
    53
    Thanks Brett,

    and by all means feel free to change anything...I do hate the fact that I have to use this statement all the time

    'AND sh_serial in (53565,53588,53597)'

    and if anyone else has any ideas on how i can tweak this horrid statement...I'd appreciate it! Thanks!

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Store those values in a temp table (@ or #) and do WHERE EXISTS (...).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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