i need to increment a column depending on another column's value in java

Question

I have a table in sql server with columns TP_No, Level_No, TP_No2, Quantity

The sample values may be..

Level_No | TP_No | TP_No2 | Quantity
____________________________________
    1    |   1   |        |    1   
    2    |   1   |   3    |    4   
    3    |  14.1 | 28, 32 |    8

For each row, I need to calculate 2 things Plate value1 and plate value 2.

For row 1, Plate value 1 has to be 1 as the level is 1 and Quantity is 1 and Plate value2 has to be blank, because, TP_No2 is blank (repeat the values for the no. mentioned in Quantity field)

For row 2, Plate value 1 has to be 1.1, 1.2, 1.3, 1.4 because, level is 2, so, we need to add one decimal point and incremet the number after the decimal point until the number in quantity, i.e., here, quantity is 4, so plate value 1 will have 4 values and, plate value 2 has to be 3, 3, 3, 3 (simply repeat and not incremet the value mentioned in TP_No2 to the number mentioned in Quantity)

For row 3, plate value 1 has to be 14.1.1 to 14.1.8 (8 values) because, level is 3. So, the decimal points need to be addedd and the last value has to be incremented to the number mentioned in quantity. and, plate value 2 has to be 28 or 32 printed 8 times (either 28 8 times or 32 8 times or 28 4 times and 32 4 times)

try
            {
                    java.util.List<String> level = new ArrayList<String>();
                    java.util.List<String> qty = new ArrayList<String>();
                    java.util.List<String> tpno = new ArrayList<String>();
                    java.util.List<String> tpcust = new ArrayList<String>();
                    java.util.List<String> np = new ArrayList<String>();
                    java.util.List<String> npcust = new ArrayList<String>();

                    Statement stat = con.createStatement();

                    ResultSet rs = stat.executeQuery("select Level_No, Qty, TP_No, Make2 from PlanningSheet.dbo.SAP");

                    while(rs.next())
                    {
                            int index = rs.getString("Qty").trim().indexOf(".");

                            String qty1 = rs.getString("Qty").trim().substring(0, index);

                            int qnty = Integer.parseInt(qty1);

                            qty.add(Integer.toString(qnty));

                            level.add(rs.getString("level_no").trim());

                            tpno.add(rs.getString("tp_no").trim());

                            tpcust.add(rs.getString("make2").trim());
                    }

                    System.out.println("qty : level : tpno : tpcust");

                    for(int j = 0; j < level.size(); j++)
                    {
                            //System.out.println(qty.get(j).toString().trim() + "   :" + level.get(j).toString().trim() + " :" + tpno.get(j).toString().trim() + "  : " + tpcust.get(j).toString().trim());

                            for(int i = 0; i < Integer.parseInt(qty.get(j).toString().trim()); i++)
                            {
                                    if(!( (("").equals(tpno.get(j).toString().trim())) || ((" ").equals(tpno.get(j).toString().trim())) ))
                                    {
                                            if(tpno.get(j).toString().trim().contains("."))
                                            {
                                                    System.out.println("tp_no with . : " + tpno.get(j).toString().trim());

                                                    int index1 = tpno.get(j).toString().trim().lastIndexOf(".");

                                                    String tpno1 = tpno.get(j).toString().trim().substring(0, index1);

                                                    String tpno2 = tpno.get(j).toString().trim().substring(index1 + 1);

                                                    int tpno3 = Integer.parseInt(tpno2);

                                                    np.add(tpno1 + "." + (tpno3 + 1));

                                                    System.out.println("tpno with . : " + tpno1 + "." + (tpno3 + 1));
                                            }

                                            else if(!(tpno.get(j).toString().trim().contains(".")))
                                            {
                                                    String tpno1 = tpno.get(j).toString().trim();

                                                    System.out.println("tp_no without . : " + tpno.get(j).toString().trim());

                                                    String tpno2 = Integer.toString(Integer.parseInt(tpno.get(j).toString().trim()) + 1);

                                                    np.add(tpno1 + "." + tpno2);

                                                    System.out.println("tpno1 without . : " + tpno1 + "." + tpno2);
                                            }
                                    }
                            }

                            for(int i = 0; i < Integer.parseInt(qty.get(j).toString().trim()); i++)
                            {
                                    if(!( (("").equals(tpcust.get(j).toString().trim())) || ((" ").equals(tpcust.get(j).toString().trim())) ))
                                    {
                                            if(tpcust.get(j).toString().trim().contains(","))
                                            {
                                                    int index2 = tpcust.get(j).toString().trim().indexOf(",");

                                                    String tpcust1 = tpcust.get(j).toString().trim().substring(0, index2);

                                                    String tpcust2 = tpcust.get(j).toString().trim().substring(index2 + 1);

                                                    npcust.add(tpcust1);
                                                    npcust.add(tpcust2);

                                                    System.out.println("tpcust1 : " + tpcust1);
                                                    System.out.println("tpcust2 : " + tpcust2);
                                            }

                                            else if(!(tpcust.get(j).toString().trim().contains(",")))
                                            {
                                                    npcust.add(tpcust.get(j).toString().trim());

                                                    System.out.println("tpcust : " + tpcust.get(j).toString().trim());
                                            }
                                    }
                            }
                    }

                    System.out.println("np size : " + np.size());
                    System.out.println("npcust size : " + npcust.size());

                    System.out.println("np : " + np);
                    System.out.println("npcust : " + npcust);
            }

            catch(Exception e3)
            {
                    e3.printStackTrace();
            }

I have edited my post and added the code

np - expected output for plate value 1

np - result got for plate value 1

np cust - expected output for plate value 2

np cust - result got for plate value 2

As it is clearly visible from the expected result and the result i have got from above code, observe hoe the value has to increment number of times or to the number mentioned in teh quantity column (ex., if quantity is 4, tp number is 2, the result for plate value 1 has to be 2.1, 2.2, 2.3, 2.4 and if tp cust is 100, the result for plate value 2 has to be 100, 100, 100, 100. Now, if the quantity is 5, tp number is 3.4, tp cust is 4, result for plate value 1 has to be 3.4.1, 3.4.2, 3.4.3, 3.4.4, 3.4.5 and result for plate value 2 is 4, 4, 4, 4, 4. Now, if tp cust is not mentioned, the plate value 2 will be blank. and if tp cust is something like 2.1 - 2.6 then, plate value 2 has to be 2.1, 2.2, 2.3, 2.4, 2.5, 2.6. if tp cust is 4, 5 and quantity is 3, both 4 and 5 has to appear 3 times.

i hope my problem and results are clear

What have you tried so far? Although I can't quite fully extract the meaning, you have a fully realized algorithm as text, you should first take a stab at solving this yourself. Or, post how far you've been able to get, and folks here can help you with your current progress. As stated, however, your question is not clear. — ProgrammerDan, Mar 28 '14 at 06:08
i don't know how to put 2 decimal points and the increment it. — user1416631, Mar 28 '14 at 06:40
depending on the level, the values have to be incremented (for TP_No). If the level is 2, the one decimal point. I have managed to do this. (float / decimal type). but, how about 2 decimal points?. have not seen in java or SQL server. do they support it? — user1416631, Mar 28 '14 at 06:43
Perhaps this [Stack Overflow post relates to your question?](http://stackoverflow.com/questions/441600/write-a-number-with-two-decimal-places-sql-server) — ProgrammerDan, Apr 03 '14 at 01:04
Thanks for the reply. I dont want 2 decimal places. I want **2 decimal Points (like 1.1.1)** — user1416631, Apr 03 '14 at 05:21
I'd say, look into string concatenation, but the question as it stands is a bit hard to understand. It's not clear what the output needs to be, what format it should take .. Can you take some time to describe your problem more, and what you're tried so far to solve? — ProgrammerDan, Apr 09 '14 at 03:21
Okay, great. Now, in that code, can you highlight or indicate where you begin having problems? I'm guessing you could just use some kind of counter to increment for each "level" and just use a `StringBuilder` to construct a string with as many decimal points as you desired. — ProgrammerDan, Apr 09 '14 at 13:51
hi, thanks for the reply. I have problems in number of times plate value1 has to be incremented and number of times plate value2 has to appear which depends on the quantity mentioned. my results are wrong for the for loop that i have written — user1416631, Apr 10 '14 at 04:37
Could you add the results that you currently get, and using those results, show how they differ from expected? — ProgrammerDan, Apr 10 '14 at 14:17
hi, thanks for the reply. i have edited my post to add the expected result and the result got from the above code and explanations on the results.. please let me know in case they are confusing — user1416631, Apr 11 '14 at 04:19

i need to increment a column depending on another column's value in java

0 Answers0