Results 1 to 3 of 3

Thread: sql while loop

  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: sql while loop

    I'm working on this query where I will have various qty's like 2 3 4 ect. If the qty is 4 I need an output column to have '-01', '-02', -'03', '-04' and for 3 only start at 3 ect. I have this set up already, but i'm trying to avoid the use of a temp table. Any thoughts?

    set NoCount ON
    declare @qty int
    set @qty = 0
    SELECT CUST_ORDER_LINE.LINE_NO, CASE WHEN CUST_ORDER_LINE.DESIRED_SHIP_DATE IS NOT NULL
    THEN CUST_ORDER_LINE.DESIRED_SHIP_DATE ELSE CUSTOMER_ORDER.DESIRED_SHIP_DATE END AS SHIP_DATE, CUSTOMER.NAME AS CUSTOMER,
    CUST_ORDER_LINE.CUST_ORDER_ID AS CUSTOMER_ORDER, CUST_ORDER_LINE.PART_ID AS PART, CUST_ORDER_LINE.MISC_REFERENCE AS DESCRIPTION,
    DEMAND_SUPPLY_LINK.SUPPLY_BASE_ID, DEMAND_SUPPLY_LINK.ALLOCATED_QTY, @qty as CAT, @qty + demand_supply_link.ALLOCATED_QTY as CAT, WHILE CAT <> 0
    BEGIN
    SET CAT = '01'
    CAT = CAT - 1
    END
    FROM CUSTOMER INNER JOIN
    CUSTOMER_ORDER ON CUSTOMER.ID = CUSTOMER_ORDER.CUSTOMER_ID INNER JOIN
    CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID INNER JOIN
    DEMAND_SUPPLY_LINK ON CUST_ORDER_LINE.CUST_ORDER_ID = DEMAND_SUPPLY_LINK.DEMAND_BASE_ID AND
    CUST_ORDER_LINE.LINE_NO = DEMAND_SUPPLY_LINK.DEMAND_SEQ_NO
    WHERE (CUST_ORDER_LINE.CUST_ORDER_ID = 'A051021GS1')

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could write a function that created the values as required
    must this be in SQL or could you just do this in the presentation layer (the for or report that consumes the data).
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    I was hoping to make it a pass through in access.

Tags for this Thread

Posting Permissions

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