How to modify SQL-Statement?

Question

I have a problem that i can´t solve :(

I execute this sql statement to get the max of a the CCID-String:

                "select MAX(CCID) as test123 from "
                        + tabelleName+ " where CCID like 'W%'");

After that I want to increase the string. I substring the W and convert the string to an int:

                String ccid = rs.getString("test123");
                String test = ccid.substring(1,6);
                int id = Integer.parseInt(test);
                int newid = id+1;

At the moment the max(ccid) looks like W01352. The result of newid is "1353" without the "W0".

I want to get a string like W01353 that I could add to the db2-database for the next W-Number.

Do someone have any idea?

Sorry for my not so perfect english ;)

Thank u.

I think you may be going about this the wrong way. Assuming this is an existing table. There must already be code somewhere to get the next value. Find that and make use of it in your code. — Charles, Mar 05 '14 at 13:29
Yes, but the original code is REXX-CODE. But now it works. Thanks Molske — JonnyBeton, Mar 05 '14 at 14:28

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Maybe you can take a look at the db2 substring function : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_bif_substring.htm

You can try select substring(MAX(CCID), 2, x) as test123 from ..

where x is the type you want: CODEUNITS16, CODEUNITS32, ..

This should return the value without the W0

edit

to add leading zero's to the new integer, do the following:

String old = "W01234";
String sub = old.substring(1); // "01234"
Integer id = Integer.parse(sub); //1234
Integer newI = id + 1; // 1235
String newS = "W" + ("00000" + newI).substring(newI.toString().length()); // "W01235"

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 05 '14 at 09:56

KristofMols

3,487
2
38
48

Thank u. I know the substring function. But I have no idea how I can automatical built a string with "W" and 5 digits after the W. When I have an int with only 4 digitets. I can´t fill the string to the left side with 0-digitels to the W. – JonnyBeton Mar 05 '14 at 10:01
1

Aha, if you just want to add leading zero's to your string, you can easily do that with the following trick: http://stackoverflow.com/a/4051991/401667 – KristofMols Mar 05 '14 at 10:03
I get an Int with the increased number, but now I must built it back to a string with WXXXXX. When i have the number 14 i must bulit W00014. When i have the number 123 i must built W00123 and so on. – JonnyBeton Mar 05 '14 at 10:03
leading zero logic in edit .. did not try this, but you get the idea :-) – KristofMols Mar 05 '14 at 10:09

score 0 · Answer 2 · answered Mar 05 '14 at 09:52

0

Use PreparedStatement rather that Statement, eaiser and efficient way to work the SQL queries.

String sql = "select MAX(CCID) as test123 from <tabelleName> where CCID like '?%'");
PreparedStatement preparedStatement = dbConnection.prepareStatement(sql);
preparedStatement.setString(1, "...");
ResultSet rs = preparedStatement.executeQuery(sql);

answered Mar 05 '14 at 09:52

Subhrajyoti Majumder

40,646
13
77
103

I don`t have a problem to save the new String into the database. The problem is, that I can´t built a increased String like "W01353" – JonnyBeton Mar 05 '14 at 09:55

How to modify SQL-Statement?

2 Answers2

edit