Explanation about select statement

[Niki_S]

Can someone please explain me about this below process. Specially USE IN and NOT USED.

Use In Select('tempdetl')

SELECT Batch_no 
SCAN 
  TEXT TO stra NOSHOW
  SELECT nPoDtlID,nBatchInvId FROM MAS.dbo.Acp_Invoice_Dtl WHERE  nPoDtlID=?Batch_no.nPoDtlID  
  ENDTEXT
  
  SQLEXEC(hndOps,stra,'_Tempd') 
  If NOT Used('tempdetl')
     Select * From  _Tempd Into Cursor tempdetl READWRITE 
  Else
     SELECT tempdetl
     APPEND FROM DBF('_Tempd') 
  Endif

ENDSCAN

Thank you

 

[Chris Miller]

I think I gave that to you once, and you made frequent use of it.

You would like to join Batch_no with MAS.dbo.Acp_Invoice_Dtl, but you can't.
Well, you could, if you would do SQL with a join in the first place instead of first querying a list of Batch_nos and then getting row by row.
This does an SLEXEC per Batch_no record.

I can only recommend learning more about SQL, so you don't need such constructs.

Now perhaps the core of your question:

You want one result, don't you? Yor goal is to have the data in tempdet1, so in the first line Use In Select('tempdet1') you ensure if there was a previous result, you close it. Use opens and closes workareas. USE IN workareanumber or USE in aliasname closes the cursor in that workarea or with that name. And Select('tempdetl'), that is the Select() function, not the Select command, returns the workarea number of that alias name. So you could also write Use in tempdetl, for short. But that will error, if there isn't yet a tempdetl alias used. If that's the case, for example in the first run of all of this, then Select('tempdetl') will return 0. And Use In 0 does nothing.

You could also write this:

If Used('tempdet1')
   Use In tempdet1
Endif

After this Used('tempdet1') isn't true anymore, but false. tempdet1 isn't used anymore.

Then you go into the scan loop and because USED('tempdet1') now is false, the first _Tempd result cursor is copied to a cursor named tempdet1. All further SQLEexecs overwrite _Tempd and then you add those records to tempdet1 with APPEND. So in the first time the If branch is executed, after that you always go in the else branch of the if.

It's not a recipe that'll always give you what you want. Especially not, if you have double values in the cursor you scan and use as parameter value. That's ?Batch_no.nPoDtlID in this case. If Batch_no has the same nPoDtlID value more than once, you also fetch that data more than once. And since half your recent posts are about deleting double records, well, do that in the root cause of getting the double records.

Ideally, avoid such constructs and go back to the original data. The only reason I see where you need a FoxPro cursor like Batch_no, if it's data from a picklist, i.e. if a user has picked 2 or more things from a listbox and you want to get related data. If Batch_no is something that has its source in another SQLExec query beforehand, then don't use Batch_no, query the data you want from SQL-Server right away.

It's also not the first time I recommend you better get what you need from SL Serer in one go, that can involve a complexer or even multiple queries, but should better be done with a single SQLExec than row by row. Even back when I first showed you this, I strongly pointed out you better not use this, but now it has become your first solution to everything, it seems.

After all this, I welcome that you ask questions. It's the only way to learn.



Chriss