How to select single records from foxpro dbf

[Niki_S]

I have a dbf as below and I need to select some records.

TABLE1
Stylecd         colorname         style         delqty
123             black             R8H/1         12
123             red               R8H/1         70
645             blue              ZS32          400
8354            black             HGD5          523

Now I want to select only records which has only 1 single record. For example

Stylecd         colorname         style         delqty
645             blue              ZS32          400
8354            black             HGD5          523

And I want to do this with using an IF-ENDIF.

IF &&select the records which has only 1 single record

ENDIF

So how can I do this?

Thank you

 

[Mike Lewis]

And I want to do this with using an IF-ENDIF

Why? It is much easier to do this with SQL:

SELECT * FROM Table 1 ;
  WHERE StyleCD IN (SELECT StyleCD FROM Table1 GROUP BY StyleCD HAVING Count(StyleCD) = 1)

Or is this one of your homework assignments that your teacher told you to do?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Niki_S]

SELECT * FROM C:\Tempfiles\_DelDtl ;
  WHERE nStylecode IN (SELECT nStylecode FROM _DelDtl GROUP BY nStylecode HAVING Count(nStylecode) = 1) INTO CURSOR ABC

Okay I did it and now I want to update the records in my _DelDtl only that records are in ABC cursor. For that I did something like this. But it is not correct.

IF _DelDtl.nStylecode in (select nStylecode from ABC ) THEN 
	UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
	INNER JOIN UPDT ON _DelDtl.Style=UPDT.Style AND _DelDtl.nStylecode=UPDT.nStylecode AND _DelDtl.cLotName=UPDT.cLotName
ENDIF

If I do it in this way it takes long time.

IF (_DelDtl.nStylecode = ABC.nStylecode ) THEN 
	UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
	INNER JOIN UPDT ON _DelDtl.Style=UPDT.Style AND _DelDtl.nStylecode=UPDT.nStylecode AND _DelDtl.cLotName=UPDT.cLotName
ENDIF

So how can I do this?

Thank you

 

[Chris Miller]

Your ideas of the IF statements are noth not working, but also they are both unnecessary. Just do the updaet, it wil only update thise record that do have a match of stylecode anyway, there is no need to do an IF, the inner join is taking care of that conditionto be fulfilled and skips anything that has no match.

You really are still a beginner in understanding SQL. One major thing to learn is that an inner join is acting as filter only allowing matches from both sides (the opposite outer join would not care about non-matches and fill the gaps with NULLs). SQL is really strong in that it doesn't need loops and IFs are done by joins and where clauses.

So, all I'm saying is your code can be reduced to just:

UPDATE _DelDtl SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtl ;
	INNER JOIN UPDT ON _DelDtl.Style=UPDT.Style AND _DelDtl.nStylecode=UPDT.nStylecode AND _DelDtl.cLotName=UPDT.cLotName

At least in how far I understand the data strucutre, that a combination of style, stylecode AND lot name is unique. Otherwise you just need one more join condition so the Delcr_Pcs are not updated on two innventory records but just the one about the exact same delivered product. I assume there are no two records with all the smae style, stylecode AND lot number, or are there?


Chriss

 

[Niki_S]

Yes, there have more than one records in same style, stylecode and lotnumber. Because of that I need to use this update statement only for the stylecodes which have only one record. That's why I thought that I need to use IF ENDIF for that.

 

[Chris Miller]

Well, thn you need something, but not an IF. You need to limit the data of UPDT with the help of GROUP BY and HAVING, but more detailed as what you had:

Select * From UPDT  SELECT Style, nStylecode, cLotname, FROM _DelDtl GROUP BY Style, nStylecode, cLotname HAVING Count(*) = 1;
INTO Cursor _DelDtlUnique READWRITE

UPDATE _DelDtlUnique SET Delvr_Pcs=UPDT.Delvr_Pcs FROM _DelDtlUnique ;
	INNER JOIN UPDT ON _DelDtlUnique.Style=UPDT.Style AND _DelDtlUnique.nStylecode=UPDT.nStylecode AND _DelDtlUnique.cLotName=UPDT.cLotName

But I'd bet you now still have a leftover problem, if the UPDT records all have to be processed, also those wheree you don't have a unique record in _DelDtl, then you need to still add the number of delivered pieces DlvrPcs to one of the multiple _DelDtl rows. So you need at least one more criterion to get the unique record that needs to be updated. You're only doing part of the job. If you have too little knowlegde that's reason to ask about what else will distinguish the records in question so you can correctly add in the delivery.

Let me ask this from another perspective: Is it correct that an inventory/stock table has multiple records of items with same style, style code, and lot number? Should there only be one record summing the counts of both records? Or what other property distinguishes these two or more inventory/stock items, so you can match it 1:1 to delivered goods?

You have to have a 1:1 matching, or you'll add the delivery to two inventoriy items, while you just have one product.

Let's talk of a very concrete example that's likely to not be concretely enough categorized: Bllue jeans. There are many blue jens, with different styles, different style codes, lot numbers, perhaps colors, manufactureres, labels etc. One very specific property of goods would be an GTIN number.

I may have a totally wrong picture of what your data is about, but you know, talking about such things on that abstract level as you do is hard. It's hardly possible to give you the best advice, because the conditions you're giving as outset might already be a wrong idea. Here you clearly have a problem of insufficient knowledge about the data structure.

Chriss

 

[Niki_S]

Okay I did it like this and it's working.

SELECT * FROM C:\Tempfiles\_DelDtl ;
  WHERE nStylecode IN (SELECT nStylecode FROM _DelDtl GROUP BY nStylecode HAVING Count(nStylecode) = 1) INTO CURSOR ABC READWRITE 

UPDATE ABC SET Delvr_Pcs=UPDT.Delvr_Pcs FROM ABC ;
INNER JOIN UPDT ON ABC.Style=UPDT.Style AND ABC.nStylecode=UPDT.nStylecode AND ABC.cLotName=UPDT.cLotName
 
SELECT ABC 
INDEX on nStylecode TAG Stylecode 

SELECT _DelDtl 
SCAN
	SELECT ABC 
	SEEK _DelDtl.nStylecode 
	
	SELECT _DelDtl 
	replace Delvr_Pcs WITH ABC.Delvr_Pcs 
ENDSCAN

Now I want to update the rest of my records in _DelDtl. That means the records that didn't updated by using ABC. How can I identify that?

Thank you