Converting code to SQL

[Bluejay07]

Hello,

I have been asked to convert a piece of code to SQL. It's a simple piece of code that clears back orders. Since this code involces Accpac, I thought I'd try to post here before going to the SQL forum.

I have made an attempt although I am unsure if this is entire correct. Here is the original code:

   OEORDH.RecordClear
   OEORDH.RecordGenerate False
   OEORDH.Order = 1
   
   OEORDH.Browse "COMPLETE < 3 AND LASTINVNUM > """"", True
   OEORDH.GoTop
   
   Do    
      l_bolClear = False
      OEORDD.RecordClear
      OEORDD.RecordGenerate False
      OEORDD.Browse "ORDUNIQ = " & OEORDH.Fields("ORDUNIQ").Value & " AND QTYBACKORD > 0", True
      OEORDD.SystemAccess = VIEW_SYSACCS_POSTING
         
      OEORDD.GoTop
      Do
         If OEORDD.Fields("QTYBACKORD").Value > 0 Then
            OEORDD.Fields("QTYORDERED").Value = 0
            OEORDD.Fields("QTYBACKORD").Value = 0
               
            OEORDD.Fields("TBASE1").Value = 0
            OEORDD.Fields("TBASE2").Value = 0
            OEORDD.Fields("TBASE3").Value = 0
            OEORDD.Fields("TBASE4").Value = 0
            OEORDD.Fields("TBASE5").Value = 0
               
            OEORDD.Fields("TAMOUNT1").Value = 0
            OEORDD.Fields("TAMOUNT2").Value = 0
            OEORDD.Fields("TAMOUNT3").Value = 0
            OEORDD.Fields("TAMOUNT4").Value = 0
            OEORDD.Fields("TAMOUNT5").Value = 0
               
            OEORDD.Fields("TRATE1").Value = 0
            OEORDD.Fields("TRATE2").Value = 0
            OEORDD.Fields("TRATE3").Value = 0
            OEORDD.Fields("TRATE4").Value = 0
            OEORDD.Fields("TRATE5").Value = 0
               
            OEORDD.Fields("COMPLETE").Value = 2    '// 2 = Complete.
            OEORDD.Update
            OEORDD.Process
            l_bolClear = True
         End If
      Loop While OEORDD.GoNext
         
      If l_bolClear Then
         OEORDH.Fields("COMPLETE").Value = 3    '// 3 = Complete/Not Included.
         OEORDH.Update
         OEORDH.Post
      End If
           
   Loop While OEORDH.GoNext
   OEORDD.Close
   OEORDH.Close

Here is my SQL code:

UPDATE OEORDD
SET 
   QTYORDERED = 0, 
   QTYBACKORD = 0,
               
   TBASE1 = 0,
   TBASE2 = 0,
   TBASE3 = 0,
   TBASE4 = 0,
   TBASE5 = 0,
               
   TAMOUNT1 = 0,
   TAMOUNT2 = 0,
   TAMOUNT3 = 0,
   TAMOUNT4 = 0,
   TAMOUNT5 = 0,
               
   TRATE1 = 0,
   TRATE2 = 0,
   TRATE3 = 0,
   TRATE4 = 0,
   TRATE5 = 0,
               
   COMPLETE = 2
WHERE EXISTS
   (SELECT OEORDD.*
    FROM OEORDH INNER JOIN
         OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
    WHERE OEORDH.COMPLETE > 3 AND OEORDH.LASTINVNUM > '' AND OEORDD.QTYBACKORD > 0)
    
UPDATE OEORDH
SET COMPLETE = 3
WHERE EXISTS
   (SELECT OEORDH.*
    FROM OEORDH INNER JOIN
         OEORDD ON OEORDH.ORDUNIQ = OEORDD.ORDUNIQ
    WHERE OEORDH.COMPLETE > 3 AND OEORDH.LASTINVNUM > '' AND OEORDD.QTYBACKORD = 0  AND OEORDD.COMPLETE = 2)

Can anyone confirm or change the SQL code as required.
Thank you

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

SQL code tested and working with the exception that a '>' sign should have been '<'.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[DjangMan]

Run an integrity check on OE and IC to make sure Accpac is happy.

 

[Bluejay07]

That's a great idea. Thanks.

If at first you don't succeed, then sky diving wasn't meant for you!