locked
sql query with while loop RRS feed

  • Question

  • Select TM.Order_code , TI.Order_NUMBER From OrderCheck TM 
    Inner join OrderTable TI
    ON TM.OrderCode = TI.OrderCode 
    Where OrderDate = @L_INV_DATE 
    PRINT 'NULL VALUE'
    ELSE 
    PRINT 'NOT NULL'

    In the above code i have write the query as per below steps but not giving the proper result , pls sort me out from this problem as the steps to create the above query as mentioned  below 

    1. Select all the Order codes(order_code ) from OrderCode table
    2. For each Order code, select the Order Number and Date from Order Master table  ( OrderTable ) for all the Orders   where date is same as parameter date
    3. If there are no order   for a particular Order_code, select the next Order_code from the OrderCode   table and start again from step 1. Otherwise, insert the Date and Order code in the New table OrderDetail  table  

    Here the OrderDetail table have some column common in the above mentioned table 


    C

    Sunday, October 28, 2012 3:01 PM

Answers

  • Hi, you don't need to perform this action in a while loop procedurally, but instead focus on writing the correct join statement.  What results are returned from your JOIN statement if you remove the PRINT/ELSE statements?

    Select TM.Order_code , TI.Order_NUMBER From OrderCheck TM 
     Inner join OrderTable TI
     ON TM.OrderCode = TI.OrderCode 
     Where OrderDate = @L_INV_DATE 

    If these are not the results you are expecting, what is it returning that is incorrect?

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Sunday, October 28, 2012 3:46 PM
  • Hi Lalit,

    From your codes, it indicates OrderCode is a column of OrderCheck and OrderTable tables. If there is another talbe named OrderCode exists, please let us know. Before going further, I want to confirm what do you mean by

    >>If there are no order   for a particular Order_code

    If you mean that the value of Order_code is null, we can try the following codes and check whether they can achieve your function.

    Select TM.Order_code , TI.Order_NUMBER From OrderCheck TM 
     Inner join OrderTable TI
     ON TM.OrderCode = TI.OrderCode 
     Where OrderDate = @L_INV_DATE and TM.Order_code <> null
    
    Additionally, in order to narrow down the cause of the issue, please provide more information about your tables and their relationships.

    Allen Li

    TechNet Community Support

    Monday, October 29, 2012 10:04 AM

All replies

  • Hi, you don't need to perform this action in a while loop procedurally, but instead focus on writing the correct join statement.  What results are returned from your JOIN statement if you remove the PRINT/ELSE statements?

    Select TM.Order_code , TI.Order_NUMBER From OrderCheck TM 
     Inner join OrderTable TI
     ON TM.OrderCode = TI.OrderCode 
     Where OrderDate = @L_INV_DATE 

    If these are not the results you are expecting, what is it returning that is incorrect?

    Thanks,
    Sam Lester (MSFT)


    My Blog

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Sunday, October 28, 2012 3:46 PM
  • What about the step 3 statement 

    If there are no order   for a particular Order_code, select the next Order_code from the OrderCode   table and start again from step 1. Otherwise, insert the Date and Order code in the New table OrderDetail  table  

    This query is where in the sql statement 

     

    C

    Sunday, October 28, 2012 4:55 PM
  • Hi Lalit,

    From your codes, it indicates OrderCode is a column of OrderCheck and OrderTable tables. If there is another talbe named OrderCode exists, please let us know. Before going further, I want to confirm what do you mean by

    >>If there are no order   for a particular Order_code

    If you mean that the value of Order_code is null, we can try the following codes and check whether they can achieve your function.

    Select TM.Order_code , TI.Order_NUMBER From OrderCheck TM 
     Inner join OrderTable TI
     ON TM.OrderCode = TI.OrderCode 
     Where OrderDate = @L_INV_DATE and TM.Order_code <> null
    
    Additionally, in order to narrow down the cause of the issue, please provide more information about your tables and their relationships.

    Allen Li

    TechNet Community Support

    Monday, October 29, 2012 10:04 AM