FILTERED QUERY (2 TABLES)

Hi Experts,

I have a table called "sp_orders" which holds sales information and is set up like this:

column1 - name = "sp_orderNo", value = "numberic (with identinty)"
column2 - name = "salesInfo", value = "varchar (50)"

when a sale is made the sp_orders table is updated with the sales data and an order number is automatically assisgned.

If the sales data needs to be altered at a later date then the order is copied from the sp_orders table, adjusted, and then written to another table called "sp_adjustments" which looks like this:

column1 - name = "sp_orderNo", value = "varchar (50)"
column2 - name = "adjustedOrderNoSuffix", value = "varchar (20)"
column3 - name = "adjustedSalesInfo", value = "varchar (50)"

the sp_orderNo" column holds the order number and can be used to join the table to the "orders" table. The "adjustedOrderNoSuffix" table holds the order number suffix - i.e. "1" for the first amendment, "2" if it's amended a second time etc.

My question is this:

I need to create a recordset to return all the bookings from the "orders" table with a join to the "adjustments" table on "orderNo = adjustedOrderNo".

If an order from the orders table has been adjusted then I want the LATEST adjustment from the adjustments table to be returned and not the original order from the orders table.




The SQL code I have so far for this is as follows:



set nocount on

create table sp_ordersTemp (tempOrderNo varchar(500), tempAdjustedOrderNo Varchar(50), tempSalesInfo varchar(50))

Insert into  sp_ordersTemp (tempOrderNo) select distinct sp_orderNo from sp_orders



declare @orderID varchar(500)
declare @salesInfo varchar(500)
declare @adjNo varchar(500)
declare recCnt integer


DECLARE Htree_cursor CURSOR FOR select tempOrderNo from sp_ordersTemp

  OPEN Htree_cursor
     FETCH NEXT FROM Htree_cursor INTO @orderId
          WHILE @@FETCH_STATUS = 0
          BEGIN
               
          select count(*) into recCnt from sp_adjustments where sp_orderNo = @orderId
          if recCnt>0
          begin
           select @adjno = Max(adjustedOrderNoSuffix), @Salesinfo= adjustedSalesInfo from sp_adjustments where orderNo = @orderId
          end
          else
          begin
           set @adjno=0
           select @Salesinfo= SalesInfo from sp_orders where sp_orderNo = @orderId
          end

      UPDATE sp_ordersTemp  SET tempAdjustedOrderNo = @adjno, tempSalesInfo =@salesinfo  where tempOrderNo = @OrderId

     
        FETCH NEXT FROM Htree_cursor INTO @orderId

      END

CLOSE Htree_cursor

DEALLOCATE Htree_cursor




Select * from  sp_ordersTemp order by  tempOrderNo


drop table sp_ordersTemp




This has come from EE and been modified by myself.


I am getting an error on the line "select count(*) into recCnt from sp_adjustments where sp_orderNo = @orderId "


"'integer' is not a recognized CURSOR option."


I don't understand enough about SQL to debug this effectively. Can any of you help? Also are there any additional bugs in this code?




Thanks,



PJORDANNA