Select Not in records from two tables

[Niki_S]

I have a cursor as below.

_DelDtl
nStylecode    cLotName      cLotColor
1234          A1            BLACK
12            B2            WHITE
12            B2            YELLOW
654           C5            BLACK
654           C5            WHITE
654           C5            PINK

Now I want to get records from sql using _DelDtl. For that I used my code as below. But I can't get records.
In MIS.dbo.vInvFinal there have records as below.

nStyleCD     cLotName      cLotColor
1234          A1            BLACK
12            B2            WHITE
654           C5            BLACK

To get records from MIS.dbo.vInvFinal I used this, but it is not working,

stra="select nStyleCD,cLotName,cLotColor from MIS.dbo.vInvFinal where nStyleCD=?_DelDtl.nStylecode and cLotName=?_DelDtl.cLotName group by nStyleCD,cLotName,cLotColor "
SQLEXEC(hndOps,stra,'_Colors')

After that I want to get the records which are not in MIS.dbo.vInvFinal as below.

nStyleCD     cLotName     cLotColor
12            B2            YELLOW
654           C5            WHITE
654           C5            PINK

Can someone please help me to do this?

Thank you

 

[TamarGranor]

It sounds like _DelDtl is a VFP cursor and vInvFinal is in SQL Server (or MySQL or whatever). You can't do a query that includes both of them. You need to get all the data into VFP or into SQL Server to combine them.

Tamar

 

[Niki_S]

To get data from sql I used this. But it comes only first record.

stra="select cLotName,nStyleCD,cLotColor from MIS.dbo.vInvFinal where cLotName=?_Mismatch.cLotName and nStyleCD=?_Mismatch.nStylecode group by cLotName,nStyleCD,cLotColor "
SQLEXEC(hndOps,stra,'_Color')

What is the mistake in my code?

 

[Chris Miller]

I now think, you don't understand the concept of parameters. Do you think ?_Mismatch.cLotName passes in all cLotname values in the _Mismatch cursor?

In VFP there are the concepts of workareas and in each workarea you have a current record. ?_Mismatch.cLotName only takes one single cLotName of the current recno. The same is true for the nStyleCD aka nStylecode. The only thing that could vary in your resiult is the cLotColor.

If you need all pairings (or triples of the SQL server table, then you would need to query without any parameterization and not limit the result to just on lot name and style code.

stra="select cLotName,nStyleCD,cLotColor from MIS.dbo.vInvFinal group by cLotName,nStyleCD,cLotColor "
SQLEXEC(hndOps,stra,'_Color')

This would give you all combinations of lotname, style code and lot color that exist in vInvFinal, if that's what you want. But I would recommend to do data operations where they belong, in the SQL Server,

If you want to join with just the _Mismatch data, then you can either only do so after getting all the groups or you have the same data as in _Mismatch on SQL Server and join that there. There doesn't need to be a table _Mismatch in SQL Server, the "source" of that data to join with MIS.dbo.vInvFinal could be the exact query you previously did to get the _Mismatch cursor.

And the other way would be to create a temp table, and insert _Mismatch data into it to be able to do the join in SQL Server. Creating a temp table is no magic, the tablename just has to start with a #, #Mismatch, for example. You also donÄt need to worry that parallel use would cause name conflicts, each temp table is only seen in the client that created it, unless you would create a global temp table with two #, so ##Mismatch would be seen by all connections and then this table would be like a datbase table that's permanent, but it would disappear with a server restart. As the name says, this is only for temp data, temporarily.


Chriss

 

[Niki_S]

Thank you. For that I did it like this.

stra="select cLotName,nStyleCD,cLotColor from MIS.dbo.vInvFinal group by cLotName,nStyleCD,cLotColor "
SQLEXEC(hndOps,stra,'_Color')


SELECT nStylecode ,cLotName,cColorName FROM _DelDtl WHERE cColorName NOT in (SELECT cLotColor FROM _Color WHERE cLotName=_DelDtl.cLotName and nStyleCD=_DelDtl.nStylecode  and cLotColor=_DelDtl.cColorName ) INTO CURSOR _Final READWRITE

After this I need to update my Delvr_Pcs in my _DelDtl. But the Delvr_Pcs are not updating.

stra="SELECT clot,nsty_cd,nciqty,csty_no,sum(nDelQty) as nDelQty FROM OPS_MASTER_StarDeliverySchedule WHERE ddate=?_MxDeldt.MxDate GROUP BY  csty_no,nsty_cd,clot,nciqty "
SQLEXEC(hndOps,stra,'_DelMissing')


SELECT _Final.nStylecode ,_Final.cLotName,_Final.cColorName ,_DelMissing.nciqty,_DelMissing.nDelQty  FROM _Final LEFT JOIN _DelMissing ON _Final.nStylecode=_DelMissing.nsty_cd  AND _Final.cLotName=_DelMissing.clot ;
GROUP BY  _Final.nStylecode ,_Final.cLotName,_Final.cColorName ,_DelMissing.nciqty,_DelMissing.nDelQty INTO CURSOR _Update

SELECT _Update
SCAN
	IF _DelDtl.nStylecode=_Update.nStylecode AND _DelDtl.cLotName=_Update.cLotName
		SELECT _DelDtl 
		replace Delvr_Pcs WITH ROUND(_Update.nDelQty/_Update.nciqty*_DelDtl.nCiQty,1)
	ENDIF 
ENDSCAN

What is the issue in this?


Thankyou

 

[Chris Miller]

You're only scanning _Update, you stay on one single _DelDtl record, and only when one _Update record matches it, that _DelDtl record could be updated.

Besides that, you can only change records in cursors you select with READWRITE, that should be clear by now with all the solutions given to you.

Use an Update for this kind of updates, not a SCAN/REPLACE loop.

Chriss