How to find records in a cursor using another? 1

[Niki_S]

I have a cursor like this.
crusor name : Clr1

cColorname
Black
White
Blue

And I have another cursor as Clr2,

cColorname
Black
Blue
[\code]

In my second cursor there have only 2 records. I need to check cursor2 using my 1st cursor inside a scan. 
How can I do this?

Thank you

 

[Mike Lewis]

What exactly do you mean by "check cursor2 using my 1st cursor"?

If you mean that you want to find the records in Cursor1 that are not in Cursor2, then you can do this:

SELECT * FROM Cursor1 WHERE cColorName NOT IN (SELECT cColorName FROM Cursor2) ;
INTO CURSOR csrResults

If you mean you want the records in Cursor1 that are in Cursor2, it would be the same code but with the NOT.

If you mean something else, please clarify your question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

I need something like this.

if (here I want to see that, all the records are in cursor1 have in my second cursor)

else

endif

I want to check that records inside a IF condition.

Can I use it like this?

if NOT IN (SELECT cColorName FROM Cursor2) then 

else

endif

Or like this?

IF NOT IN (Cursor2.cLotColor ) THEN 

else

endif

 

[Rick C. Hodgin]

Something like this will work (off the top of my head, untested):

SELECT clr1
INDEX ON cColorName TAG main

SELECT clr2
SET RELATION TO cColorName into clr1

* Show those records that are not in clr1
SCAN FOR EOF("clr1")
    * Do something here
ENDSCAN

* Show those records that are in clr1
SCAN FOR NOT EOF("clr1")
    * Do something here
ENDSCAN

If you want to use SQL, then something like this:

SELECT * FROM clr2 WHERE cColorName 
    IN (SELECT cColorName FROM clr1) INTO CURSOR c_matches
SELECT * FROM clr2 WHERE cColorName NOT IN (SELECT cColorName FROM clr2) INTO CURSOR c_no_match

Something like that will get you the lists.

--
Rick C. Hodgin

 

[Mike Lewis]

Can I use it like this?
if NOT IN (SELECT cColorName FROM Cursor2) the

No. That is not valid syntax. But you can do this:

SELECT * FROM Cursor1 WHERE cColorName NOT IN (SELECT cColorName FROM Cursor2) ;
  INTO CURSOR csrResults
IF RECCOUNT("csrResults") > 0
  * It has found records from cursor 1 that are NOT in cursor 2
ELSE
  * It has not found records from cursor 1 that are NOT in cursor 2
ENDIF

Does that help at all?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

Thank you for the help. But I need to know the follow steps are ok with the conditions.

SQLEXEC(OpsHandle,"Select cColorName from vMER_Master_LotColors where nStylecode=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName","Clr_Name")
  SQLEXEC(OpsHandle,"Select cLotColor as cColorName from mis.dbo.vInvFinal where nStylecd=?_DelDtl.nStylecode and clotname=?_DelDtl.cLotName and cLotColor=?Clr_Name.cColorName","Clr_Des")
  
  
SELECT Clr_Name 
SCAN WHILE cColorName = Clr_Des.cColorName 
	SELECT Clr_Name 
	SEEK Clr_Des.cColorName 
	_Clr="N"
	
	SCAN WHILE cColorName = Clr_Des.cColorName
		REPLACE Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
		_Clr="Y"
	ENDSCAN
	IF _Clr="N"
		IF Lot_CI.Tot_CI=0 
	    	REPLACE Delvr_Pcs WITH 0
		ELSE 
			REPLACE Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)

		ENDIF
	ENDIF 
	 
ENDSCAN