Query needs to check all the records, now only the last record is checked.

[DanHD]

hello

I've written a query (base of a more complex one) that I used to test all the records of a document in a stored procedure.
In this simple example it's checks the quantity on row level in a document is = 1, the an error message will follow.
My problem is that the check only works on the last record. So when I've 2 order lines, whereby row 1 quantity is 1 and quantity row 2 is <> 1, then I don't receive an error.
How do this come and what do I've to change to check all the records.


declare @docentry as int
set @docentry = 5122

declare @ERROR as nvarchar (10)
declare @Error_Message as nvarchar (300)
declare @VISORDER as int
declare @ITEMCODE as nvarchar (20)
declare @QTY as numeric (19,6)

select @VISORDER = t1.VisOrder +1
, @ITEMCODE = t1.ItemCode
, @QTY = t1.Quantity
from ORDR t0
inner join RDR1 t1 on t0.DocEntry = t1.DocEntry
where t0.DocEntry = @DocEntry
order by t1.VisOrder

--select @VISORDER as visorder , @ITEMCODE as ITEMCODE , @QTY as QTY /*I use this line for testing*/

if ( @QTY =1 )
begin
set @ERROR = 08
set @Error_Message= 'Test foutmelding'
end


select @ERROR, @Error_Message


regards
Dan


Dan

 

[bborissov]

...
IF EXISTS(select * 
          from ORDR t0
          inner join RDR1 t1 on t0.DocEntry = t1.DocEntry AND  t1.Quantity = 1)
   begin
      set @ERROR = 08
      set @Error_Message= 'Test foutmelding'
    end

Borislav Borissov
VFP9 SP2, SQL Server