Regarding the IF....ENDIF

[Niki_S]

I have a dbf as _Del and its like this.

SELECT nStylecode, Style,cLotName,cColorName,Delvr_Pcs FROM _DelClr INTO Dbf C:\Tempfiles\_Del

And I have some a cursor as ABC. Now I want to update records in my _Del using ABC. And also I want to update the records which are not in ABC. For the first update I used this and it's already working.

UPDATE _Del SET Delvr_Pcs=ABC.Delvr_Pcs FROM ABC ;
WHERE _Del.Style=ABC.Style AND _Del.nStylecode=ABC.nStylecode AND _Del.cLotName=ABC.cLotName

And for the second update I need to add this.

IF Colr_Desp.Tot_Desp>0 
 REPLACE _Del.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
ELSE
 IF Lot_CI.Tot_CI=0 
  REPLACE _Del.Delvr_Pcs WITH 0
 ELSE 
  REPLACE _Del.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)
 ENDIF 
ENDIF

For this I need this kind of structure.

IF && want to see the _Del records are in ABC [if there are in ABC need to do below update]
 UPDATE _Del SET Delvr_Pcs=ABC.Delvr_Pcs FROM ABC ;
 WHERE _Del.Style=ABC.Style AND _Del.nStylecode=ABC.nStylecode AND _Del.cLotName=ABC.cLotName 
ELSE 
 IF Colr_Desp.Tot_Desp>0 
  REPLACE _Del.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_Desp.Tot_Desp*Colr_Desp.Tot_Desp,0)
 ELSE
  IF Lot_CI.Tot_CI=0 
   REPLACE _Del.Delvr_Pcs WITH 0
  ELSE 
   REPLACE _Del.Delvr_Pcs WITH ROUND(_DelQty.DelQty/Lot_CI.Tot_CI*_DelDtl.nCiQty,0)
  ENDIF 
 ENDIF
ENDIF

So how can I do this?

Thank You

 

[Chris Miller]

First, your cretion of _Del now includes ALL records of _DlClr, also those which have more than one record for a group of style, style code and lotname. That's what you didn't want in your earlier thread.

I think this is important, because only because of that you have a resto f records that need another update. Well, your construct would only work for a record by record processing, but UPDATE and all of SQL is a set thing, so you need to rethink things set based.

What will never work is an IF having a SL-Select, IF acts on boolean values, in the simplest case they come from a comparison of two values, or variable and value, in the most general case any function or expression that results in a boolean value. That's not what any SQL returns, neither SELECT, UPDATE nor DELETE. SELECT is even the only statement that returns something, a set, the nature of SL is a statement, a command. And that does have an effect on something. The part of a SELECT that evaluates booleans is the WHERE clause, but you can't use the WHERE clause in an IF, so you cant do things like IF value EXISTS IN (result of) a query.

Well, at first you had records where COUN(*) is 1, the rest of records are with COUNT(*)>1, a COUNT(*) of 0 will not happen in a single table query, as groups are only vcreted from the data of it, so all groups have at least 1 record.

Your ret set could be retrieved from:

SELECT nStylecode, Style,cLotName,cColorName,Delvr_Pcs FROM _DelClr GROUP BY Style, nStylecode, cLotName HAVING COUNT(*)>1 INTO Dbf C:\Tempfiles\_Del2

I can't help you with the other part of the else branch, because I don't see where Colr_Desp and Lot_CI come from, whether tey are tables or objects. It smells like the need for - guess what? Another SL updte query that involves all these tables, not your ELSE branch construct. The conditions you have should flow into join clauses or where clause of that update.

And finally the question arises, whether the special case of COUNT(*)=1 needs a separete update or in other words,whether you can't combine the two updates into one.

Chriss

 

[Chris Miller]

One more thing about the general idea (I have) of what you do:

I think you're processing a delivery, either what you send to customers or receibve from vendors and update the inventory. In that case there should be data, maybe in the form of a CSV file of the delivery and you want to process this to adjust your inventory data. Well, of course you sit at a client and not at a server to do that, but that should not need retrieval of data on a client, to update it there and put it back to the server, that's really one of the jobs where the central database SERVER has the advantage of being a server side proces that acts AT THE server and doesn't need to let data travel a roundtrip.

OF course you need a way to put the data about the devlivery into SQL Server. It could be read in from CSV or whatever you get or have, maybe that's also already SQL Server data. But after that all you should need is execution of some SQL UPDATEs, DELETEs, perhaps also INSERTs of items you get for the first time. But this should all just flow from CSV into the database within the database server.

The only reason (albeit not a good one) to pull that data to a client and act on it with VFP in the frontend is, if that processing code already existed. But you're writing this as new code. Then concentrate on the general needs. Ask yourself the question how you can do this with the least need to transfer data through the network, because that is your bottleneck.

Als don't be the manager who thinks in terms of all the things at his PC, even if the data is coming from an Email attachment the source of that isn't the Mail client software on the client, the source is an attachment of a mail that's on a mail server. In a bigger company you would usually have an Exchange server that's central. Even when the mail is recieved on any client PC it's still on the mail server (usally at least for a time period) or it could even be received to a special mail addres that only SQL Server or a separate process on the server runs to receive that specific data and make it available to SQL Server in tables foreseen for such an import task.

If you read this, thank you, I know you actually just want to get the answer to your question. Well, that's already at the begin of this. You would need to tell more about the outset, I think, We don't know anything about the meaning of several names in your current code and don't know what it's about, where it came from or what it is. There's always the possibility to just assume you know yourself and we only need to care about that IF and assume the rest will follow.

Indeed that's also true, but I am quite reluctant to only give what is asked for if I sense a big coneptual error. And I see two possible such grand design mistakes in the flow of data that could be unnecessary and in the way of separating two taks that seem to need separate processing but can be generalized. If I think of inventory the first thing that springs to me is T-SL MERGE, which covers all upgrade needs, update, insert and deletes things.

And for better identification of data I would also expect there to be better identification of what goes where than by characteristics like color and style, a lot number is at lest something I realize belonging in that topic of production.


Chriss

 

[Niki_S]

Really thanks for your ideas.
The only thing that I want is how to use IF condition to see the records are in _Del is in the ABC cursor. I want to use IF condition because I have to use some replace statements in my code. So I think I need to use IF condition. Can you please tell me how to use IF condition like this situation.

 

[Chris Miller]

Well, as said you can't use SQL within an IF, so you need to do SQL before. Or an IF SEEK().

Anyway, all records of ABC originated in _Del, so your UPDATE will process all records of ABC to their originating record in _Del.

If you'd do this UPDATE within a scan through all _Del records, that part isn't necessary at all, you'd update say 500 of 1000 records with the one UPDATE, but execute it for any _del record you find in ABC, that means you would do 500*1000 record updates instead of just updating them once.

What you need for the else branch is the rest set of records. And I gave you the condition for that. And then you only need to execute the else branch for that set.

When you create _Del what you could do is add a logical field "processed" to it, .f. by default.
If you change the UPDATE to not only set Delvr_Pcs but also Set precessed=.t., then all records with processed=.f. are the ones you look for.

Chriss