0

I'm given a task to find out which have the smaller or equal MOQ(Minimum order quantity) compare to the fixed MOQ. At first, I used the method which was while read() (using c#) and go for line by line.

But it is slow when there are 60k records.

Now I want to update all at one time instead of update and read it line by line.

This is my previous code :

 String cmdtxt8 = "SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL";
            OracleDataReader odrget = db.read(cmdtxt8);
            while (odrget.Read())
            {
                String cmdtxt9 = "UPDATE IR_MOQANS SET NOTE = 'GET'
                WHERE MANUF_PART  = '" + odrget.GetString(0) + "' AND MATERIAL = '" + odrget.GetString(1) + "' 
                AND BUOM_MOQ  
                <= 
                CAST ( (
                SELECT BUOM_MOQ FROM IR_MOQANS 
                WHERE MANUF_PART  = '" + odrget.GetString(0) + "' AND MATERIAL = '" + odrget.GetString(1) + "' 
                AND FIXD IS NOT NULL AND ROWNUM <= 1) 
                AS DECIMAL)";
                db.execmd(cmdtxt9);
            }

This is the query I had tried but doesn't work (I want something like this) :

UPDATE IR_MOQANS SET DELTA = 'G' 
WHERE SCALE_QTY 
<= 
(
    SELECT BUOM_MOQ  FROM IR_MOQANS WHERE (MANUF_PART, MATERIAL) 
    IN 
    (
        SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
    ) 
    AND FIXD IS NOT NULL AND ROWNUM <= 1
)
AND (MANUF_PART, MATERIAL) 
IN 
(
    SELECT MANUF_PART, MATERIAL FROM IR_MOQANS GROUP BY MANUF_PART, MATERIAL
);

Below is an example of my table:

manuf_part  |material   |fixd    |buom_moq
-------------------------------------------
A           |Pen        |X       |100      
-------------------------------------------
A           |Pen        |        |50        
-------------------------------------------
A           |Pen        |        |110     
-------------------------------------------
A           |Bag        |X       |200     
-------------------------------------------
B           |Cap        |        |20       
-------------------------------------------
B           |Cap        |X       |50       
-------------------------------------------
B           |Cap        |        |70       
-------------------------------------------

The expected result will come out like this:

manuf_part  |material   |fixd    |buom_moq   |note
--------------------------------------------------------
A           |Pen        |X       |100        |GET
--------------------------------------------------------
A           |Pen        |        |50         |GET
--------------------------------------------------------
A           |Pen        |        |110        |
--------------------------------------------------------
A           |Bag        |X       |200        |GET
--------------------------------------------------------
B           |Cap        |        |20         |GET
--------------------------------------------------------
B           |Cap        |X       |50         |GET
--------------------------------------------------------
B           |Cap        |        |70         |
--------------------------------------------------------

Manuf_part and material MUST be grouped to get the same category. For example, (A, Pen) MUST only compare with (A, Pen).

Karrie
  • 25
  • 3
  • I would suggest reading https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection as a matter of urgency. Your code, as is, is **extremely** dangerous. – mjwills Aug 04 '20 at 06:59
  • That's why I want to change my codes. I want it to do this process just using a query. Any advice or solution please? – Karrie Aug 04 '20 at 07:04

0 Answers0